Migrate data from managed PostgreSQL in Yandex Cloud to Managed Databases PostgreSQL

How the migration works

Data is migrated between two managed PostgreSQL clusters:

source cluster — Managed Service for PostgreSQL in Yandex Cloud;
target cluster — PostgreSQL Managed Database in Selectel.

To migrate data, logical replication is used: a publication is created on the source cluster, to which the target Managed Database cluster subscribes. Existing data is copied first, followed by continuous application of changes. The application switchover is performed once the target is fully synchronized with the source, keeping service downtime to a few minutes.

In Selectel Managed Databases, direct access to the database server and superuser privileges are not provided, so all actions are performed at the level of SQL queries and built-in DBMS mechanisms.

Prerequisites

Infrastructure

administrative access to cloud infrastructure in Yandex Cloud and Selectel;
a DBMS user on the source cluster with the mdb_superuser, mdb_replication, mdb_monitor, mdb_admin roles and access to the databases being migrated;
a stable network link between sites and configured routing — especially important for large databases, for more details see the Network connectivity subsection;
configured network policies (security groups, firewall) so that clusters can exchange traffic with each other;
when connecting sites via an internal channel (VPN or Direct Connect) — a DNS resolver for the internal yandexcloud.net zone to access the Yandex primary by hostname, for more details see the DNS resolver for the yandexcloud.net zone subsection;
free disk space on the primary of the source cluster for the growing WAL volume — at least one-third of the database size;
the PostgreSQL version in Selectel is not lower than the version on the source cluster.

Work environment

a server with network access to both clusters for running SQL queries and monitoring. You can use a workstation with VPN that has access to both clusters;
PostgreSQL CLI clients of the required version: psql, pg_dump, pg_restore;
optional: network diagnostic utilities: ping, telnet, traceroute, mtr, iftop, and a terminal multiplexer (tmux) to prevent sessions from disconnecting if connection to the workstation is lost.

Risks

Insufficient disk space on the source	After creating the publication and subscription, the server reserves WAL for the replication slot. The more inserts, updates, and deletes occur, the faster the WAL volume grows. For a database of 1 TB or more with a ~1 Gbps link, about 300 GB of WAL files can accumulate during the initial copy. When the disk fills up, the cluster will switch to read-only mode, and write operations for the application will fail
Unstable connection	A connection drop during initial copying interrupts replication, and it will have to be restarted from scratch. A brief interruption during the catch-up phase usually recovers automatically
Writing to the target cluster	The cluster in Selectel is writable during replication. Any data modifications in the replicated tables will cause a conflict and stop replication. Do not write anything to the target cluster until the application switchover
Source primary change	If the primary in Yandex Cloud moves to another host, replication will stop. Using special Yandex FQDNs that point to the current primary for replication is not recommended: after a role change, they can continue pointing to the old host for up to 10 minutes
Overlapping subnets	Subnets in Yandex Cloud and Selectel must not overlap; otherwise, routing will not work

Network connectivity

To perform the migration, you need to establish a stable network connection between Yandex Cloud and Selectel.

Connection options between Yandex Cloud and Selectel

Option 1 — Direct Connect. Recommended for large databases. A dedicated connection between Selectel and Yandex Cloud via a telecom carrier point of presence: an L2/L3 connection is created, and further interaction takes place through the Selectel global router. The service is ordered on both sides, with each provider switching their part. Routing is configured on the cloud router in Selectel and in the routing table in Yandex Cloud. Check traffic billing conditions for the migration period with your Selectel account manager;
Option 2 — VPN tunnel. A virtual machine is created on each site (a cloud server in Selectel), and a tunnel over the internet (WireGuard, OpenVPN, IPsec) is established between them. The virtual machines must be located in cloud networks accessible to managed databases. Routing is configured via the cloud router in Selectel and the routing table in Yandex Cloud. We recommend configuring static routes on the cloud router rather than in the subnet settings: they will then apply to all products for which the router is the default gateway, including managed databases. You can do this via the OpenStack CLI or API, including Terraform.

DNS resolver for the yandexcloud.net zone

PostgreSQL hosts in Yandex Cloud are accessed only by FQDN: the internal IP address of the host can change at any time. However, the FQDN belongs to Yandex's internal zone and cannot be resolved externally, so the cluster in Selectel will not be able to resolve it through its DNS.

We recommend creating a temporary Unbound-based DNS resolver on both sites (for example, you can use the virtual machines with VPN):

Unbound-01 in Yandex Cloud forwards queries for the yandexcloud.net zone to Yandex internal DNS and accepts queries on port 53 on its local interface;
Unbound-02 in Selectel forwards queries for the yandexcloud.net zone to Unbound-01 and accepts queries on port 53 on its local interface.

The cluster in Selectel uses Unbound-02 as its DNS server and gets the current internal IP address of the Yandex primary.

If you do not use Unbound-02 and specify the Unbound-01 address in Selectel directly, then if connection between the sites is lost, the hosts will be left without their only DNS server.

In Yandex Cloud, the DNS server is the first address of the subnet. For example, for the 10.2.0.0/24 subnet, it is 10.2.0.1. You can find it in the cloud network settings in the console.

Example configuration of Unbound-01 (Yandex Cloud):

server:
    port: 53
    interface: 127.0.0.1
    interface: 10.1.0.10            # local interface IP
    access-control: 127.0.0.0/8 allow
    access-control: 10.0.0.0/8 allow
    do-ip4: yes
    do-ip6: no
    do-udp: yes
    do-tcp: yes
    num-threads: 2
    num-queries-per-thread: 1024
    hide-identity: yes
    hide-version: yes
    prefetch: yes
    verbosity: 1
forward-zone:
    name: "yandexcloud.net"
    forward-addr: 10.1.0.1          # Yandex internal DNS
Example Unbound-02 configuration (Selectel):
server:
    port: 53
    interface: 127.0.0.1
    interface: 10.200.0.10          # local interface IP
    access-control: 127.0.0.0/8 allow
    access-control: 10.0.0.0/8 allow
    do-ip4: yes
    do-ip6: no
    do-udp: yes
    do-tcp: yes
    num-threads: 2
    num-queries-per-thread: 1024
    hide-identity: yes
    hide-version: yes
    prefetch: yes
    verbosity: 1
forward-zone:
    name: "yandexcloud.net"
    forward-addr: 10.1.0.10         # Unbound-01 address

Here 10.200.0.10 is an example IP address of the DNS server for the cluster in Selectel.

Migration steps

The guide uses example parameter values — replace them with your own.

Parameter	Name in the example
DNS address of the primary in the Selectel cluster	master.destination-srv.c.dbaas.selcloud.ru
FQDN of the primary in the Yandex Cloud cluster	rc1a-source-srv.mdb.yandexcloud.net
Port in the Selectel cluster	5432 — direct connection to PostgreSQL, not via a connection pooler
Port in the Yandex Cloud cluster	6432— direct connection to PostgreSQL, not via a connection pooler
my_database	Database name in Selectel
my_database	Database name in Yandex Cloud
my_user	User — database owner in Selectel
selectel	User for database migration in Yandex Cloud

Set up connectivity between Yandex Cloud and Selectel.
Analyze the database before migration.
Prepare the source cluster in Yandex Cloud.
Prepare the target cluster in Selectel.
Transfer the database schema from the source cluster to the target cluster.
Start replication.
Check replication status.
Switch over the application.

1. Set up connectivity between Yandex Cloud and Selectel

Make sure that you have established a connection between Yandex Cloud and Selectel and configured routing, for more details see the Network connectivity subsection.
Make sure that the source and target clusters are in different subnets; otherwise, routing will not work.
Make sure that configured network policies (security groups, firewall) allow clusters to exchange traffic with each other.

2. Analyze the database before migration

For query examples for analysis, see the Useful queries subsection.

Determine which databases need to be migrated.
Check the LC_COLLATE and LC_CTYPE locales. In the target cluster, they are specified when creating a database. Locales cannot be changed after the database is created.
Collect a list of users and roles.
List cluster parameters that have been modified from their default values.
Determine the schemas from which tables are replicated (public and others, if any).
Make sure all tables are supported by logical replication. Foreign tables, temporary tables, and materialized views are excluded from publication.
Find external sources (foreign servers) and ensure their availability from Selectel.
Stop data modifications for the duration of the migration. Tables created after replication starts, including new partitions and tables from application migrations, will not be included in it.
Check tables without a primary key: for UPDATE and DELETE, a REPLICA IDENTITY must be set; otherwise, changes will not be replicated.
Compile a list of extensions — they must be installed in the target cluster before starting replication.

Useful queries

tables in the public schema:

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

tables supported by logical replication:

SELECT n.nspname, c.relname, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND c.relkind IN ('r', 'p')
  AND c.relpersistence = 'p';

tables without a primary key:

SELECT n.nspname, c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (SELECT 1 FROM pg_constraint
                  WHERE conrelid = c.oid AND contype = 'p');

extensions:
```
\dx
```
foreign servers, tables, wrappers:
```
\des+
\det+
\dew+
```
databases and users:
```
\l+
\du+
```

3. Prepare the source cluster in Yandex Cloud

Back up the databases.
Create a dedicated user for migration, for example, selectel, with the mdb_superuser, mdb_replication, mdb_monitor, mdb_admin roles and access to the databases being migrated.
In security groups, allow traffic from Selectel subnets. If the group is set to default, there are no restrictions.
Check the free space margin on the primary, for more details see the Risks subsection.

4. Prepare the target cluster in Selectel

Create a PostgreSQL cluster. When creating the cluster:
- select a PostgreSQL version not lower than the version of the source database;
- select a node configuration with a disk size sufficient for the volume of data being migrated.
Create a user — the future database owner.
Create a database. Specify the same locales as in the source. Locales cannot be changed after the database is created.
Optional: create the remaining users as in the source.
Add extensions that you identified during database analysis before migration in step 10.
For custom DNS (the Unbound-02 address), create a ticket with Selectel technical support — in the Control panel, this parameter cannot be changed for an already created cluster.

5. Transfer the database schema from the source cluster to the target cluster

Export the database schema from the source cluster:

pg_dump -h rc1a-source-srv.mdb.yandexcloud.net -p 6432 -U selectel \
  -d my_database --schema-only -f /data/my_database.sql

Import the schema into the target cluster:

psql -h master.destination-srv.c.dbaas.selcloud.ru -p 5432 -U my_user \
  -d my_database -f /data/my_database.sql

Check the output for errors. GRANT errors are acceptable if additional users have not yet been created. Compare objects in the source and target:
```
\dt+ *.*
\dv
\des
\dew
```

6. Start replication

In PostgreSQL Managed Database, there are no permissions for the CREATE PUBLICATION ... FOR ALL TABLES command, so the list of tables must be specified manually.

The pause between creating the publication and creating the subscription should be minimal, so we recommend preparing both commands first and then running them one after the other. After the subscription is created, the source starts accumulating WAL, and the faster the initial copy completes, the less it will grow.

Get the list of tables in the source cluster:

psql -h rc1a-source-srv.mdb.yandexcloud.net -p 6432 -U selectel -d my_database

SELECT table_schema || '.' || table_name
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

Prepare the command to create a publication on the source cluster, but do not execute it. In the command, specify all required tables obtained in step 1. The table name must be specified with the schema; otherwise, an error will occur:
```
CREATE PUBLICATION my_pub FOR TABLE
    public.table_1,
    public.table_2,
    public.table_3;
```
Prepare the command to create a subscription on the target cluster, but do not execute it:
```
CREATE SUBSCRIPTION my_sub
    CONNECTION 'host=rc1a-source-srv.mdb.yandexcloud.net port=6432
                dbname=my_database user=selectel password=password'
    PUBLICATION my_pub
    WITH (copy_data = true);
```
Where:
- host and port — address and port of the source primary;
- dbname — source database;
- user and password — username and password of the migration user;
- my_pub — publication name;
- copy_data = true — parameter specifying that existing data must be copied before streaming changes. This is the default value, but we recommend specifying it explicitly.
Execute the prepared CREATE PUBLICATION command on the source cluster.
Execute the prepared CREATE SUBSCRIPTION command on the target cluster.

7. Check replication status

Check cluster metrics:
- source cluster — CPU and memory load on the primary, Disk usage on primary, Total size of WAL files, Free space, network traffic and packets;
- target cluster — cluster vCPUs and memory, WAL file size, database size, network load. For more details on monitoring cluster status, see the Monitoring PostgreSQL clusters, nodes, and databases instructions.
If free space on the source primary is running out and initial copying is not yet completed, stop replication; otherwise, the source cluster will enter read-only mode. For more details on stopping replication, see step 5 of the Switch over the application subsection.
On the target cluster, check the copy status for each table:
```
SELECT sub.subname, c.relname, sr.srsubstate
FROM pg_subscription_rel sr
JOIN pg_subscription sub ON sr.srsubid = sub.oid
JOIN pg_class c ON sr.srrelid = c.oid
ORDER BY sr.srsubstate, c.relname;
```
srsubstate values:
- i - initialization,
- d - data is copying,
- f - table copy is finished,
- s - synchronized,
- r - ready, normal replication in progress.
The migration is ready for switchover when all tables are in the r status.

On the target cluster, check the overall subscription status:

SELECT subname, pid, received_lsn, last_msg_send_time,
      last_msg_receipt_time, latest_end_lsn
FROM pg_stat_subscription;

SELECT * FROM pg_stat_subscription_stats;

On the source cluster, check the replication slot and the retained WAL size:

SELECT slot_name, active,
   pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
                  confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

SELECT * FROM pg_publication;

8. Switch over the application

Stop writes to the source database — stop the application or put it into maintenance mode.

Check replication lag on the source:

SELECT slot_name, active,
   pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
                  confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

SELECT * FROM pg_publication;

Wait until the lag on the source becomes 0.

Compare the number of rows. This query may take a long time for large databases:

DO $$
DECLARE
    rec RECORD;
    cnt BIGINT;
BEGIN
    FOR rec IN
        SELECT n.nspname, c.relname
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relkind = 'r'
          AND n.nspname NOT IN ('pg_catalog', 'information_schema')
        ORDER BY 1, 2
    LOOP
        EXECUTE format('SELECT count(*) FROM %I.%I', rec.nspname, rec.relname)
            INTO cnt;
        RAISE NOTICE '%.%: %', rec.nspname, rec.relname, cnt;
    END LOOP;
END $$;

The size of tables on the source and target may differ by up to tens of gigabytes. Table size is usually smaller on the target cluster. This occurs because the source cluster stores "dead" tuples before they are vacuumed, while replication only transfers live data.

Reset sequence values on the target cluster — logical replication does not transfer them, and sequence values on the target cluster remain initial. If you do not set them, the application will encounter an error on insert:

4.1. Run the script on the target cluster in the database being migrated. The script finds sequences linked to columns (serial and identity) and sets each to MAX(<column>) + 1:

DO $$
DECLARE
    seq RECORD;
    max_id BIGINT;
BEGIN
    FOR seq IN
        SELECT c.oid::regclass AS seqname,
              n.nspname AS schemaname,
              t.relname AS tablename,
              a.attname AS columnname
        FROM pg_class c
        JOIN pg_depend d ON d.objid = c.oid AND d.deptype IN ('a', 'i')
        JOIN pg_class t ON t.oid = d.refobjid
        JOIN pg_namespace n ON n.oid = t.relnamespace
        JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = d.refobjsubid
        WHERE c.relkind = 'S'
    LOOP
        EXECUTE format('SELECT max(%I) FROM %I.%I',
                      seq.columnname, seq.schemaname, seq.tablename)
            INTO max_id;
        IF max_id IS NOT NULL THEN
            PERFORM setval(seq.seqname, max_id + 1, false);
        END IF;
    END LOOP;
END $$;

4.2. Get current values of sequences on the source cluster that are not linked to columns:

SELECT last_value FROM <sequences>;

Specify <sequences> — the sequence name.

4.3. Set the sequence values that you obtained in step 4.2 in the target cluster using setval.

Stop replication:

5.1. Drop the subscription on the target cluster:
```
DROP SUBSCRIPTION my_sub;
```
5.2. Drop the publication on the source cluster:
```
DROP PUBLICATION my_pub;
```
DROP SUBSCRIPTION also removes the replication slot on the source cluster, after which the accumulated WAL is freed.

5.3. If the source cluster is unavailable at that moment, the slot will remain — delete it manually on the source cluster, otherwise the WAL will continue to grow:
```
SELECT pg_drop_replication_slot('my_sub');
```
Switch the application connection string to the target cluster in Selectel and start the application.
Optional: delete temporary resources that you created for data migration.