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 replication or using logical-dump.

For your information

Before migration, create a receiving database cluster of PostgreSQL with a version no lower than the source cluster. If you chose the logical dump migration method, the cluster versions must match.

Logical replication

The logical replication uses a publish and subscribe model with one or more subscribers. They subscribe to one or more publications on the publishing node. A publication is created on the external source PostgreSQL cluster to which the receiving cloud database cluster subscribes.

  1. Prepare source cluster.
  2. Move database schema.
  3. Create publication on source cluster.
  4. Create a subscription on the receiving cluster.

Prepare source cluster

  1. Add the replication privilege to a user with access to replicated data:

    ALTER ROLE <user_name> WITH REPLICATION;

    Specify <user_name> as the user name.

  2. In the postgresql.conf file, set the logging level (Write Ahead Log) to logical:

    wal_level = logical
  3. In the pg_hba.conf file, configure authentication:

    host all all             <host>      md5
    host replication all <host> md5

    Specify <host> — the IP address or DNS name of the host master host of the receiving cluster.

  4. Restart PostgreSQL to apply the changes:

    systemctl restart postgresql

Transfer database schema

The source and destination cluster must have the same database schema.

  1. Create a circuit dump on the source cluster using the pg_dump utility:

    pg_dump \
    -h <host> \
    -p <port> \
    -d <database_name> \
    -U <user_name> \
    --schema-only \
    --no-privileges \
    --no-subscriptions \
    --no-publications \
    -Fd -f <dump_directory>

    Specify:

    • <host> — IP address or DNS name of the source cluster master host;
    • <port> — port;
    • <database_name> is the name of the database;
    • <user_name> is the database username;
    • <dump_directory> is the path to the dump.
  2. Restore the circuitry from the dump on the receiving cluster using the pg_restore utility:

    pg_restore \
    -Fd -v \
    --single-transaction -s \
    --no-privileges \
    -O \
    -h <host> \
    -U <user_name> \
    -p <port> \
    -d <database_name> \
    <dump_directory>

    Specify:

    • <host> — IP address or DNS hostname of the receiving cluster;
    • <user_name> is the database username;
    • <port> — port;
    • <database_name> is the name of the database;
    • <dump_directory> is the path to the dump.

Create a publication on the source cluster

To create a publication for all tables at once, you need superuser rights.

Create a publication for the tables you want to migrate:

CREATE PUBLICATION PUBLICATION <publication_name> FOR ALL TABLES;

Specify <publication_name> as the name of the publication.

Create a subscription on the receiving cluster

In the receiving cloud database cluster, subscriptions can only be used by a user with the dbaas_admin role.

  1. Create a subscription on behalf of a user with the dbaas_admin role:

    CREATE SUBSCRIPTION <subscription_name> CONNECTION
    'host=<host>
    port=<port>
    dbname=<database_name>
    user=<user_name>
    password=<password>
    sslmode=verify-ca'
    PUBLICATION <publication_name>;

    Specify:

    • <subscription_name> is the name of the subscription;
    • <host> — IP address or DNS name of the source cluster master host;
    • <port> — port;
    • <user_name> is the database username;
    • <password> is the user's password;
    • <database_name> is the name of the database;
    • <publication_name> is the name of the publication.
  2. You can monitor replication status using the pg_subscription_rel directory:

    SELECT * FROM pg_subscription_rel;

    You can see the overall replication state in the pg_stat_subscription and pg_stat_replication views for subscriptions and publications respectively.

  3. Sequences are not replicated, so before transferring the load to the receiving cluster, restore the sequences dump to the receiving cluster if they are used. Also, remove subscriptions in the receiving cluster before transferring the load:

    DROP SUBSCRIPTION <subscription_name>;

    Specify <subscription_name> as the name of the subscription.

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).

For your information

If you are using PgBouncer port 5433, change the pooling mode of PgBouncer to session. If a different pooling mode is enabled for PgBouncer, the search_path for some of the connections may change, and the tables will not be accessible by a partial name.

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 <port> \
    -U <user_name> \
    -d <database_name>

    Specify:

    • <host> — The IP address or DNS name of the master host of the receiving cluster;
    • <port> — port;
    • <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.