Migrating PostgreSQL databases to PostgreSQL Managed Databases

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

Before migration, create a receiving database cluster 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 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 the external source PostgreSQL cluster, and the receiving Managed Databases cluster subscribes to it.

Prepare the source cluster.
Migrate 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> — 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> — IP address or DNS name of the master host of the target cluster.

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

2. Migrate the database schema

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

Create a schema dump on the source cluster using the pg_dump utility:
```
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> — IP address or DNS name of the master host of the source cluster;
- <port> — port;
- <database_name> — database name;
- <user_name> — database username;
- <dump_directory> — path to the dump.
Restore the schema from the dump on the target cluster using the pg_restore utility:
```
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 host of the target cluster;
- <user_name> — database username;
- <port> — port;
- <database_name> — database name;
- <dump_directory> — path to the dump.

3. Create a publication on the source cluster

To create a publication for all tables at once, superuser privileges are required.

Create a publication for the tables you want to migrate:

CREATE PUBLICATION <publication_name> FOR ALL TABLES;

Specify <publication_name> — publication name.

4. Create a subscription on the target cluster

In the target Managed Databases cluster, only a user with the dbaas_admin role can use subscriptions.

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> — subscription name;
- <host> — IP address or DNS name of the master host of the source cluster;
- <port> — port;
- <user_name> — database username;
- <password> — user password;
- <database_name> — database name;
- <publication_name> — publication name.
You can monitor the replication status using the pg_subscription_rel catalog:
```
SELECT * FROM pg_subscription_rel;
```
You can see the general status of replication in the pg_stat_subscription and pg_stat_replication views for subscriptions and publications, respectively.
Sequences are not replicated, so before transferring the workload to the target cluster, restore the dump with sequences on it if they are used. Also, before transferring the workload, delete the subscription on the target cluster:
```
DROP SUBSCRIPTION <subscription_name>;
```
Specify <subscription_name> — subscription name.

Logical dump

Create a database dump (a file containing recovery commands) on the source cluster and restore the dump on the target cluster.

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

If you use PgBouncer port 5433, change the PgBouncer pooling mode to session. If a different pooling mode is enabled for PgBouncer, search_path may change for some connections, and tables will not be accessible by their unqualified names.

SQL dump

Create a database dump on the source cluster using the pg_dump utility:
```
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> — username;
- <database_name> — database name.
Restore the dump on the target cluster using the psql utility:
```
psql \
   -f dump.sql \
   -h <host> \
   -p <port> \
   -U <user_name> \
   -d <database_name>
```
Specify:
- <host> — IP address or DNS name of the target cluster master host;
- <port> — port;
- <user_name> — database username;
- <database_name> — database name.

Custom dump

A custom-format database copy 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> — IP address or DNS name of the source cluster master host;
- <user_name> — database username;
- <database_name> — database name.
Restore the dump on the target cluster using the pg_restore utility:
```
pg_restore \
   -v \
   -h <host> \
   -U <user_name> \
   -d <database_name> archive.dump
```
Specify:
- <host> — IP address or DNS name of the target cluster master host;
- <user_name> — database username;
- <database_name> — database name.