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.
