---
title: "Migrate PostgreSQL databases to Managed PostgreSQL PGVector"
sidebar_label: "Migrate to managed databases"
sidebar_position: 22
description: "How to migrate a PostgreSQL database to Managed PostgreSQL PGVector"
---

import Formbricks from '@theme/MDXComponents/Formbricks'

# Migrate PostgreSQL databases to Managed PostgreSQL PGVector

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 a [logical dump](#logical-dump).

Before migrating, [create a target database cluster](/managed-databases/pgvector/create-cluster.mdx) with a PostgreSQL version not lower than that of the source cluster. If you choose the migration method using a logical dump, the cluster versions must match.

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

## Logical replication \{#logical-replication}

[Logical replication](https://www.postgresql.org/docs/current/logical-replication.html) uses a publish and subscribe model with one or more subscribers. They subscribe to one or more publications on a publisher node. A publication is created on an external source PostgreSQL cluster, and the target managed database cluster subscribes to it.

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 target 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>` — the username.

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 master host of the target cluster.

4. Restart PostgreSQL to apply the changes:

   ```bash
   systemctl restart postgresql
   ```

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

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

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

   ```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>` — the port;
   * `<database_name>` — the database name;
   * `<user_name>` — the database username;
   * `<dump_directory>` — the path to the dump.

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

   ```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 target cluster host;
   * `<user_name>` — the database username;
   * `<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 migrate:

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

Specify `<publication_name>` — the publication name.

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

In the target managed 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:

   ```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 master host;
   * `<port>` — the port;
   * `<user_name>` — the database username;
   * `<password>` — the user password;
   * `<database_name>` — the database name;
   * `<publication_name>` — the publication name.

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

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

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

3. Sequences are not replicated, so before switching workload to the target cluster, restore a dump with sequences on it if they are used. Also, before switching workload, delete the subscription in the target 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 target cluster.

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

:::info

If you use PgBouncer port 5433, [change the PgBouncer pooling mode](/managed-databases/pgvector/connection-pooler.mdx) to session. If another pooling mode is enabled for PgBouncer, search_path may change for some connections, and tables will not be accessible by an unqualified name.

:::

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

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

   ```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 master host;
   * `<user_name>` — the username;
   * `<database_name>` — the database name.
2. Restore the dump on the target cluster using [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 target cluster master host;
   * `<port>` — the port;
   * `<user_name>` — the database username;
   * `<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 master host;
   * `<user_name>` — the database username;
   * `<database_name>` — the database name.
2. Restore the dump on the target cluster using [pg_restore](https://www.postgresql.org/docs/13/app-pgrestore.html):

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

   Specify:

   * `<host>` — the IP address or DNS name of the target cluster master host;
   * `<user_name>` — the database username;
   * `<database_name>` — the database name.

<Formbricks />