Migrate a PostgreSQL Database

This article will show you how to migrate a PostgreSQL database onto PostgreSQL Clusters platform using pgAdmin and SQL Shell(psql). To use pgAdmin and psql, you need to install the same version of PostgreSQL in your local PC or server.

Migrate a PostgreSQL database using pgAdmin

1. Take a backup of your database

Choose the target database to be backed up.

Enter the Filename, Format, Compression ratio, Encoding, and Role name, then click Backup.

A successful notification will show after it.

And you will get the backup file.

2. Connect to the target database

Connect to the database to which you want to migrate your data.

PS: keep the new database name the same as your previous database.

3. Restore the database

Right click the target database, and select Restore.

Choose the backup file, then click Restore.

Migrate a PostgreSQL database using pg_dump and psql

1. Take a backup of your database

Open CMD in Windows or open Shell in Linux to take a backup using pg_dump.

Use the command below to back up your database.

pg_dump -h <hostname> -p <port> -U <dbuser> -d <database> -f <backup file path>

Example in Windows

pg_dump -h postgresql-5428-0.cloudclusters.net -p 10018 -U pgs -d pgs -f D:/backup/pgs.bak

Example in Linux

pg_dump -h postgresql-5428-0.cloudclusters.net -p 10018 -U pgs -d pgs -f /tmp/pgs.bak

Then enter the password for the PostgreSQL database and wait for the backup process to be completed. And you will find the backup file.

2. Restore the database

Restore the backup to the target database using pg_restore command.

PS: Before restoring the database, please create a new database in the new server and keep the database name the same as your previous database.

this is the command to restore database:

`psql  -h <hostname> -p <port> -U <dbuser> -d <database> -f <desired file path>`

Example in Windows

psql -h postgresql-5428-0.cloudclusters.net -p 10018 -U pgs -d pgs -f D:/backup/pgs.bak

Example in Linux

psql -h postgresql-5428-0.cloudclusters.net -p 10018 -U pgs -d pgs -f /tmp/pgs.bak

Then enter the password for the postgresql database and wait for the restoration process to be completed.