I will show how to create a new database (DB) with some user credentials and then a migration will be done. The migration consists of copying an entire database (DB) from one to another computer.

Creating a new DB:

The “openproject” database and a user of the same name are created. The user privileges are given to the new database and finally a password is set for the user.

$ sudo -u postgres psql
$ postgres= create database openproject;
$ postgres= create user openproject;
$ postgres= grant all privileges on database "openproject" to openproject;
$ postgres= alter user openproject with password 'new_password' ;

Then wen can make changes to the newly created database.

Create the backup with pg_dump command:

We make a backup of our “openproject” database with the following command. It include blobs, if any in our database.

$ pg_dump -h localhost -p 5432 -U postgres -F c -b -v -f  <backup path> <database name>

-F c is custom format (compressed, and able to do in parallel with -j N)
-b is including blobs,
-v is verbose
-f is the backup file name

Restore the backup with

We can restore a previously backup database with the following command.

$ pg_restore -h localhost -p 5432 -U postgres -d  -v <backup file name>

Just make sure the DB name is the same in both commands and that the destination DB is created before using pg_restore. And create the DB with the same user credentials as the original DB.