Migrate postgres database between servers

Prerequisites

1. Source database server with shell access.

2. Destination database server with shell access.

3. Basic knowledge of SSH to the servers.

4. Knowledge of docker cli commands and,

5. docker exec commands if you are using docker based postgres database

Prepare destination database

1. Login to the destination postgres instance.

2. Create the destination database if it does not exists.

   psql -h destination_host -U postgres -c "CREATE DATABASE database_name;"
   
   # or
   
   createdb -h hostname -U
   
   

   
   

3. If you need to drop the database first, then use one of the following command.

   # Without confirmation
   dropdb -h hostname -U username database_name
   
   # With confirmation
   dropdb -i -h hostname -U username database_name
   
   # If exists
   dropdb --if-exists -h hostname -U
   
   

Create database backup/dump from the source

1. Login to the shell of the source database instance.

2. Use the following command to backup the data from source database.

   pg_dump -h hostname -p port -U username -d database_name -Fc -f

   
   

3. This will create the backup.dump file in the shell path.

4. Download the backup file and migrate to another database server.

5. In case of database containers, create a relative docker volume on the container and store the file in the path of the mounted docker volume. For postgres, it is normally /var/lib/postgresql/data.

Restore the database backup to the destination

1. Login to the shell of the destination database instance.

2. Make sure you have the database backup/dump file on the same server accessible to the user.

3. Then run the following command.

   pg_restore -h destination_host -p 5432 -U username -d database_name --no-owner

   
   

4. Above command will seed data to your destination database from the backup dump.

Verify the migration

You can verify if the migration is imported using the following command and listing the tables in the database.

psql -h destination_host -U username -d database_name -c "\dt"