Migrating PostgreSQL from Other Providers
This article guides you how to migrate PostgreSQL database from other providers (called old instance for short) to cloud PostgreSQL Clusters (called cloud instance for short).
1. Back up database on old instance.
Let's assume the database name is db_test and the user is user_test.
We recommend backing up by pg_dump. With different parameters for pg_dump, the backup file and restoration method are different. Here are the examples:
- Backup file in the .sql format.
pg_dump -h old_instance_address -p 5432 -U user_test db_test > db_test.sql
- Backup file in the .dmp format.
pg_dump -h old_instance_address -p 5432 -U user_test -Fc db_test > db_test.dmp
Note:
How you back up your database determines how you restore it in Step 3.
The step has to be complete on the old instance. If you do not know how to operate, please contact your previous provider.
2. Upload backup to cloud platform via FTPs.
Please refer to the guide here to upload backup files.
3. Restore database on cloud instance.
3.1 Create database and user on the Client Panel
Please refer to the guide here to create database db_test and user user_test on the Client Panel. Do not forget to set proper permission for the user.
3.2 Access CLI Console from the Shell/SSH page on the Client Panel
Note: Shell/SSH is available for verified customer account only. If the feature is greyed out, please complete your Billing information first.
3.3 Restore database
The restore operation should be performed with a user account that has sufficient permissions.
- To restore .sql backup files
psql -U postgres db_test < /path/of/db_test.sql
- To restore .dmp backup files
pg_restore -U postgres -d db_test /path/of/db_test.dmp
Reminder
Please make sure that you have enough free space on your cloud instance for the databases before restoration.
Please repeat the process above if you will migrate multiple databases.
Backup and restoration can be performed via pgAdmin, too. Please refer to steps here.