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 a cloud databases Selectel with the help of logic dump.

For your information

Before migration create a host database cluster The versions of the receiving and source clusters must coincide.

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 SQL dump all databases (name, tables, indexes and foreign keys will be preserved) or dump in custom format (e.g., only the schema or data of a specific table can be recovered).

SQL dump⁠

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

    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> — username;
    • <database_name> — database name.

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

    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> — 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 in the receiving cluster using the utility pg_restore:

    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> — database user name;
    • <database_name> — database name.