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

import Formbricks from '@theme/MDXComponents/Formbricks'

# Migrating PostgreSQL databases to PostgreSQL TimescaleDB Managed Databases

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

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

The TimescaleDB versions must match in the destination and source clusters. You can [check the TimescaleDB version](/managed-databases/timescaledb/configurations.mdx#find-out-timescaledb-version) in the created destination cluster.

## Logical replication \{#logical-replication}

In [logical replication](https://www.postgresql.org/docs/current/logical-replication.html), a publish and subscribe model with one or more subscribers is used. They subscribe to one or more publications on the publishing node. A publication is created on the external source PostgreSQL cluster and subscribed to by the receiving Managed Database cluster.

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. Add the replication privilege to the user with access to the replicated data:

   ```sql
   ALTER ROLE <user_name> WITH REPLICATION;
   ```

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

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

Creating a publication for all tables at once requires [superuser privileges](https://www.postgresql.org/docs/current/sql-createpublication.html).

Create a publication for the tables you want to migrate:

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

   The general replication status can be viewed in the pg\_stat\_subscription and pg\_stat\_replication views for subscriptions and publications, respectively.

3. Sequences (sequences) are not replicated, so before transferring the load to the receiving cluster, restore the sequences dump to it if they are used. 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 containing commands for restoration) on the source cluster and restore it on the receiving cluster.

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

:::info

If you are using port 5433 for PgBouncer, [change the PgBouncer pooling mode](/managed-databases/timescaledb/connection-pooler.mdx) to session. If a different pooling mode is enabled for PgBouncer, the search\_path for some connections may change, and those tables will not be accessible by their 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>` — IP address or DNS name of the source cluster master host;
   * `<user_name>` — user name;
   * `<database_name>` — the database name.
2. Restore the dump on the receiving cluster using the [psql](https://www.postgresql.org/docs/9.3/app-psql.html) utility:

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

   Specify:

   * `<host>` — IP address or DNS name of the receiving cluster master host;
   * `<port>` — port;
   * `<user_name>` — 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>` — IP address or DNS name of the source cluster master host;
   * `<user_name>` — 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>` — IP address or DNS name of the receiving cluster master host;
   * `<user_name>` — database user name;
   * `<database_name>` — the database name.

<Formbricks />