Migrating PostgreSQL databases to PostgreSQL Managed Databases

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

For your information

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

In logical replication, 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.

Prepare the source cluster.
Transfer the database schema.
Create a publication on the source cluster.
Create a subscription on the receiving 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> as the user name.
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> as the IP address or DNS name of the receiving cluster's master host.

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

2. Transfer the database schema

The source and receiving 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> — 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.
Restore the schema from the dump on the receiving 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> — 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

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

Create a publication for the tables you want to transfer:

CREATE PUBLICATION <publication_name> FOR ALL TABLES;

Specify <publication_name> — the publication name.

4. Create a subscription on the receiving cluster

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

Create a subscription as 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'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.
You can monitor the replication status using the pg_subscription_rel catalog:
```
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.
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:
```
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 receiving cluster.

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

For your information

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

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> — the IP address or DNS name of the source cluster's master host;
- <user_name> — the user name;
- <database_name> — the database name.
Restore the dump on the receiving cluster using the PostgreSQL 9.3 utility 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 receiving cluster's master host;
- <port> — the port;
- <user_name> — the database user name;
- <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's master host;
- <user_name> — the database user name;
- <database_name> — the database name.
Restore the dump on the receiving cluster using the pg_restore utility:
```
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.