[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for PostgreSQL](../index.md) > [Tutorials](index.md) > Replication and migration > Migrating a database from Managed Service for PostgreSQL

# Migrating a database from Managed Service for PostgreSQL

A Managed Service for PostgreSQL cluster supports [logical replication](https://www.postgresql.org/docs/current/logical-replication.html). This allows you to use built-in PostgreSQL tools for migrating databases between different PostgreSQL clusters of version 10 and later. You can also migrate your databases from one PostgreSQL version to another, e.g., from PostgreSQL 15 to 17.

{% note info %}

If you use older clusters, you can migrate your database by [making a dump](https://www.postgresql.org/docs/current/app-pgdump.html) and then [restoring](https://www.postgresql.org/docs/current/app-pgrestore.html) from it.

{% endnote %}

This use case describes how to migrate a database from Managed Service for PostgreSQL to a different PostgreSQL cluster using logical replication.

To migrate a database from the Managed Service for PostgreSQL *source cluster* to the PostgreSQL *target cluster*:
1. [Migrate the database schema](#migrate-schema).
1. [Configure the user to manage replication on the source cluster](#configure-user).
1. [Create a publication on the source cluster](#create-publication).
1. [Create a subscription on the target cluster](#create-subscription).
1. [Monitor the migration process](#monitor-migration) until it is complete.
1. [Complete your migration](#finish-migration).

If you no longer need the resources you created, [delete them](#clear-out).


## Required paid resources {#paid-resources}

* Managed Service for PostgreSQL cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for PostgreSQL pricing](../pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).


## Getting started {#before-you-begin}

1. Check that all source cluster hosts are accessible via public IP addresses to make sure the target cluster can connect to the source cluster. For more information, see [Creating a cluster](../operations/cluster-create.md).
1. [Install the Managed Service for PostgreSQL](../operations/connect/index.md#get-ssl-cert) client SSL certificates on the hosts of the target cluster to successfully connect to the source cluster that is publicly available.


1. If you need to, set up a firewall and [security groups](../operations/connect/index.md#configuring-security-groups) so you can connect to the source cluster from the target cluster, as well as to each cluster separately, e.g., using the [psql](https://www.postgresql.org/docs/current/app-psql.html) utility.


1. Make sure the target cluster hosts can connect to the source cluster hosts.
1. Make sure you can [connect to the source cluster](../operations/connect/index.md) and the target cluster via SSL.
1. Check that an empty database is created on the target cluster to migrate your data to. 
1. Check if there is a user with full access rights to this database in the target cluster.

## Migrate the database schema {#migrate-schema}

For logical replication to work properly, both the source and the target must have the same database schema. To migrate the database schema:
1. Create a dump of the source cluster's database schema using the [pg_dump](https://www.postgresql.org/docs/current/app-pgdump.html) utility:

   ```bash
   pg_dump "host=<source_cluster_host_FQDN> port=6432 sslmode=verify-full dbname=<DB_name> user=<DB_owner_username>" --schema-only --no-privileges --no-subscriptions --no-publications -Fd -f <dump_directory>
   ```
   
   You can get the host FQDN with the [list of hosts in the cluster](../operations/hosts.md#list).
   
1. If necessary, create users with the appropriate access rights to the target cluster's database objects.

1. Restore the database schema from the dump on the target cluster using the [pg_restore](https://www.postgresql.org/docs/current/app-pgrestore.html) utility:

   ```bash
   pg_restore -Fd -v --single-transaction -s --no-privileges -h <master_host_FQDN_of_target_cluster> -U <DB_owner_username> -p 5432 -d <DB_name> <dump_directory>
   ```

## Configure the user to manage replication on the source cluster {#configure-user}

PostgreSQL uses the publish-subscribe model for logical replication: the target cluster *subscribes* to the source cluster's *publication* to transfer data. To successfully subscribe to a publication, make sure the Managed Service for PostgreSQL source cluster is accessed on behalf of the user who is assigned the logical replication management role. To configure this user:
1. [Create a user](../operations/cluster-users.md#adduser).
1. [Assign](../operations/grant.md#grant-role) the `mdb_replication` role to this user.
1. [Connect to the database](../operations/connect/index.md) that you want to migrate as the **database owner**.
1. [Grant the new user a privilege](../operations/grant.md#grant-privilege) to perform `SELECT` operations on all the DB tables.

After [creating a subscription](#create-subscription), a connection to the source cluster on the target side will be made on behalf of this user.

## Create a publication on the source cluster {#create-publication}

1. [Connect](../operations/connect/index.md) to the **master host** and the database to migrate as the **database owner**.
1. Create a publication that the target cluster will subscribe to:

   ```sql
   CREATE PUBLICATION <publication_name>;
   ```
   
1.  Include all database tables in the created publication:

    ```
    ALTER PUBLICATION <publication_name> ADD TABLE <table_1_name>;
    ...
    ALTER PUBLICATION <publication_name> ADD TABLE <table_N_name>;
    ``` 
    
    {% note info %}
    
    Managed Service for PostgreSQL clusters do not allow creating a publication for all tables at once (`CREATE PUBLICATION ... FOR ALL TABLES;`), as this operation requires superuser privileges.
    
    {% endnote %}
    
## Create a subscription on the target cluster {#create-subscription}

1. Connect to the **master host** and the target database as a **superuser** (e.g., `postgres`).
1. Create a subscription to the source cluster's publication:

   ```sql
   CREATE SUBSCRIPTION <subscription_name> CONNECTION 'host=<source_cluster_host_FQDN> port=6432 sslmode=verify-full dbname=<source_database_name> user=<user_for_replication_management> password=<user_password>' PUBLICATION <publication_name>;
   ```
   
This initiates data migration from the source cluster database to the target cluster database.

## Monitoring the migration process {#monitor-migration}

Monitor the migration process using the [pg_subscription_rel](https://www.postgresql.org/docs/current/catalog-pg-subscription-rel.html) directory that shows the *replication status*:

```sql
SELECT * FROM pg_subscription_rel;

 srsubid | srrelid | srsubstate | srsublsn
---------+---------+------------+----------
...
```

We recommend that you follow the replication status by this directory's `srsubstate` field on the target cluster.

You can get the overall replication status using the [pg_stat_subscription](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-SUBSCRIPTION) view on the target cluster and the [pg_stat_replication](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW) view on the source cluster.

## Complete your migration {#finish-migration}

After the replication is complete:
1. Disable writing data to the migrated database on the source cluster.

1. Transfer sequences, if any, from the source cluster to the target cluster using the pg_dump and psql utilities:

   ```bash
   pg_dump "host=<source_cluster_master_host_FQDN> port=6432 sslmode=verify-full dbname=<DB_name> user=<DB_owner_username>" --data-only -t '*.*_seq' > <sequences_file_name>
   ```
   
   ```bash
   psql -h <master_host_FQDN_of_target_cluster> -U <DB_owner_username> -p 5432 -d <DB_name> < <sequences_file_name>
   ``` 

1. Delete the subscription on the target cluster:

   ```sql
   DROP SUBSCRIPTION <subscription_name>;
   ```
   
1. Delete the publication on the source cluster:

   ```sql
   DROP PUBLICATION <publication_name>;
   ```
   
1. [Remove the user](../operations/cluster-users.md#removeuser) managing replication on the source cluster.

## Delete the resources you created {#clear-out}

Delete the resources you no longer need to avoid paying for them:

* [Delete the VM](../../compute/operations/vm-control/vm-delete.md).
* If you reserved a public static IP address for your virtual machine, [delete it](../../vpc/operations/address-delete.md).
* [Delete the Yandex Managed Service for PostgreSQL cluster](../operations/cluster-delete.md).