---
title: "Migrating PostgreSQL databases to PostgreSQL Managed Databases"
sidebar_label: "Migration to Managed Databases"
sidebar_position: 22
description: "How to migrate a PostgreSQL database to Managed Databases"
---

import Formbricks from '@theme/MDXComponents/Formbricks'

# Migrating PostgreSQL databases to PostgreSQL Managed Databases

You can migrate data from your PostgreSQL database to [Selectel Managed Databases](https://selectel.ru/services/cloud/managed-databases/) using [logical replication](#logical-replication) or [logical dump](#logical-dump).

:::info

Before migration, [create a receiving database cluster](/managed-databases/postgresql/create-cluster.mdx) PostgreSQL with a version no lower than that of the source cluster. If you have chosen the migration method using a logical dump, the cluster versions must match.

:::

## Logical replication \{#logical-replication}

In [logical replication](https://www.postgresql.org/docs/current/logical-replication.html), a publication and subscription model is used 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, which the receiving Managed Database cluster subscribes to.

1. [Prepare the source cluster](#prepare-source-cluster).
2. [Transfer the database schema](#move-database-schema).
3. [Create a publication on the source cluster](#create-publication-on-source-cluster).
4. [Create a subscription on the receiving cluster](#create-subscription-on-target-cluster).

### 1. Prepare the source cluster \{#prepare-source-cluster}

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

   ```sql
   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](https://www.postgresql.org/docs/current/static/wal-intro.html)) to logical:

   ```bash
   wal_level = logical
   ```

3. In the pg\_hba.conf file, configure authentication:

   ```bash
   host         all            all             <host>      md5
   host         replication    all             <host>      md5
   ```

   Specify `<host>` as the IP address or DNS name of the receiving cluster's master host.

4. Restart PostgreSQL to apply the changes:

   ```bash
   systemctl restart postgresql
   ```

### 2. Transfer the database schema \{#move-database-schema}

The source and receiving clusters must have the same database schema.

1. Create a schema dump on the source cluster using the [pg\_dump](https://www.postgresql.org/docs/current/app-pgdump.html) utility:

   ```bash
   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>` — the IP address or DNS name of the source cluster's master host;
   * `<port>` — the port;
   * `<database_name>` — the database name;
   * `<user_name>` — the database user name;
   * `<dump_directory>` — the path to the dump.

2. Restore the schema from the dump on the receiving cluster using the [pg\_restore](https://www.postgresql.org/docs/13/app-pgrestore.html) utility:

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

   Specify:

   * `<host>` — the IP address or DNS name of the receiving cluster's host;
   * `<user_name>` — the database user name;
   * `<port>` — the port;
   * `<database_name>` — the database name;
   * `<dump_directory>` — the path to the dump.

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

To create a publication for all tables at once, you need [superuser privileges](https://www.postgresql.org/docs/current/sql-createpublication.html).

Create a publication for the tables you want to transfer:

```sql
CREATE PUBLICATION <publication_name> FOR ALL TABLES;
```

Specify `<publication_name>` — the publication name.

### 4. Create a subscription on the receiving cluster \{#create-subscription-on-target-cluster}

In the receiving Managed Database cluster, subscriptions can only be used by a user with the dbaas\_admin role.

1. Create a subscription as a user with the dbaas\_admin role:

   ```sql
   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>` — the subscription name;
   * `<host>` — the IP address or DNS name of the source cluster's master host;
   * `<port>` — the port;
   * `<user_name>` — the database user name;
   * `<password>` — the user password;
   * `<database_name>` — the database name;
   * `<publication_name>` — the publication name.

2. You can monitor the replication status using the [pg\_subscription\_rel](https://www.postgresql.org/docs/current/catalog-pg-subscription-rel.html) catalog:

   ```sql
   SELECT * FROM pg_subscription_rel;
   ```

   You can view the general replication status 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 dump with sequences (if they are used) to it. Also, before transferring the load, remove the subscription on the receiving cluster:

   ```sql
   DROP SUBSCRIPTION <subscription_name>;
   ```

   Specify `<subscription_name>` — the subscription name.

## Logical dump \{#logical-dump}

Create a database dump (a file with commands for restoration) on the source cluster and restore the dump on the receiving cluster.

You can create a [SQL dump](#sql-dump) of all databases (the name, tables, indexes and foreign keys are preserved) or a [custom format dump](#custom-dump) (for example, you can restore only the schema or the data of a specific table).

:::info

If you are using the PgBouncer port 5433, [change the PgBouncer pooling mode](/managed-databases/postgresql/connection-pooler.mdx) to session. If a different pooling mode is enabled for PgBouncer, the search\_path may change for some connections, and tables will be inaccessible by short names.

:::

### SQL dump \{#sql-dump}

1. Create a database dump on the source cluster using the [pg\_dump](https://www.postgresql.org/docs/current/app-pgdump.html) utility:

   ```bash
   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's master host;
   * `<user_name>` — the user name;
   * `<database_name>` — the database name.
2. Restore the dump on the receiving cluster using the PostgreSQL 9.3 utility [psql](https://www.postgresql.org/docs/9.3/app-psql.html):

   ```bash
   psql \
      -f dump.sql \
      -h <host> \
      -p <port> \
      -U <user_name> \
      -d <database_name>
   ```

   Specify:

   * `<host>` — the IP address or DNS name of the receiving cluster's master host;
   * `<port>` — the port;
   * `<user_name>` — the database user name;
   * `<database_name>` — the database name.

### Custom dump \{#custom-dump}

A database copy in custom format is compressed by default.

1. Create a database dump on the source cluster using the pg\_dump utility:

   ```bash
   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's master host;
   * `<user_name>` — the database user name;
   * `<database_name>` — the database name.
2. Restore the dump on the receiving cluster using the [pg\_restore](https://www.postgresql.org/docs/13/app-pgrestore.html) utility:

   ```bash
   pg_restore \
      -v \
      -h <host> \
      -U <user_name> \
      -d <database_name> archive.dump
   ```

   Specify:

   * `<host>` — the IP address or DNS name of the receiving cluster's master host;
   * `<user_name>` — the database user name;
   * `<database_name>` — the database name.

<Formbricks />