Skip to main content
Migration of PostgreSQL databases to cloud databases
Last update:

Migration of PostgreSQL databases to cloud databases

You can migrate data from your PostgreSQL database to Selectel's cloud databases using logical-dump.

For your information

Before migrating create a receiving database cluster, the versions of the receiving and source clusters must match.

Logic dump⁠

Create a dump (a file with commands to restore) of the database in the source cluster and restore the dump in the destination cluster.

You can create a SQL-dump of all databases (name, tables, indexes and foreign keys are preserved) or a custom-dump (for example, you can recover only the schema or data of a specific table).

SQL dump⁠

  1. Create a database dump of the database in the source cluster using the pg_dump utility:

    pg_dump \
       -h <host> \
       -U <user_name> \
       -d <database_name> \
       -f dump.sql

    Specify:

    • <host> — IP address or DNS name of the source cluster master host;
    • <user_name> is the username;
    • <database_name> is the name of the database.

  2. Reconstruct the dump in the receiving cluster using the psql utility:

    psql \
       -f dump.sql \
       -h <host> \
       -p 5432 \
       -U <user_name> \
       -d <database_name>

    Specify:

    • <host> — The IP address or DNS name of the master host of the receiving cluster;
    • <user_name> is the database username;
    • <database_name> is the name of the database.

Custom dump⁠

The custom database copy is compressed by default.

  1. Create a database dump of the database in the source cluster using the pg_dump utility:

    pg_dump \
       -Fc -v \
       -h <host> \
       -U <user_name> \
       <database_name> > archive.dump

    Specify:

    • <host> — IP address or DNS name of the source cluster master host;
    • <user_name> is the database username;
    • <database_name> is the name of the database.

  2. Restore the dump in the receiving cluster using the pg_restore utility:

    pg_restore \
       -v
       -h <host> \
       -U <user_name>
       -d <database_name> archive.dump

    Specify:

    • <host> — The IP address or DNS name of the master host of the receiving cluster;
    • <user_name> is the database username;
    • <database_name> is the name of the database.