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 cloud databases with a logical dump.

For your information

Before migration , 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 dump in a customized format (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> — The IP address or DNS name of the source cluster master host;
    • <user_name> — user name;
    • <database_name> — database name.

  2. Recover 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 host cluster;
    • <user_name> — database user name;
    • <database_name> — database name.

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> — The IP address or DNS name of the source cluster master host;
    • <user_name> — database user name;
    • <database_name> — database name.

  2. Restore the dump to 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 host cluster;
    • <user_name> — database user name;
    • <database_name> — database name.