Migrate PostgreSQL databases to Managed PostgreSQL PGVector

You can migrate data from your PostgreSQL database to Managed Databases Selectel using logical replication or a logical dump.

Before migrating, create a target database cluster 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 in the created target cluster.

Logical replication

Logical replication 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.

Prepare the source cluster.
Transfer the database schema.
Create a publication on the source cluster.
Create a subscription on the target cluster.

1. Prepare the source cluster

Grant the replication privilege to the user with access to the replicated data:
```
ALTER ROLE <user_name> WITH REPLICATION;
```
Specify <user_name> — the username.
In the postgresql.conf file, set the logging level (Write Ahead Log) to logical:
```
wal_level = logical
```

In the pg_hba.conf file, configure authentication:

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.

Restart PostgreSQL to apply the changes:
```
systemctl restart postgresql
```

2. Transfer the database schema

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

Create a schema dump on the source cluster using pg_dump:
```
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.
Restore the schema from the dump on the target cluster using pg_restore:
```
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

To create a publication for all tables at once, you need superuser privileges.

Create a publication for the tables you want to migrate:

CREATE PUBLICATION <publication_name> FOR ALL TABLES;

Specify <publication_name> — the publication name.

4. Create a subscription on the target cluster

In the target managed database cluster, subscriptions can only be used by a user with the dbaas_admin role.

Create a subscription on behalf of a user with the dbaas_admin role:
```
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.
You can monitor the replication status using the catalog pg_subscription_rel:
```
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.
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:
```
DROP SUBSCRIPTION <subscription_name>;
```
Specify <subscription_name> — the subscription name.

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 of all databases (names, tables, indexes, and foreign keys will be saved) or a custom format dump (for example, you can restore only the schema or data of a specific table).

For your information

If you use PgBouncer port 5433, change the PgBouncer pooling mode 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

Create a database dump on the source cluster using pg_dump:
```
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.
Restore the dump on the target cluster using psql:
```
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

A database copy in custom format is compressed by default.

Create a database dump on the source cluster using the pg_dump utility:
```
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.
Restore the dump on the target cluster using pg_restore:
```
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.