Migrating PostgreSQL databases to PostgreSQL TimescaleDB Managed Databases

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

Before migration, create a target database cluster with a PostgreSQL version not lower than that of the source cluster. If you chose migration using a logical dump, the cluster versions must match.

TimescaleDB versions must match in the target and source clusters. You can find out the TimescaleDB version in the created target cluster.

Logical replication

In logical replication, a publish and subscribe model is used 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, to which the target Cloud Databases cluster subscribes.

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

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 source cluster master host;
- <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 target cluster host;
- <user_name> — database username;
- <port> — port;
- <database_name> — database name;
- <dump_directory> — path to the dump.

3. Create a publication on the source cluster

Creating a publication for all tables at once requires superuser privileges.

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 source cluster master host;
- <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 saved) or a custom-format dump (for example, you can restore only the schema or data of a specific table).

If you are using PgBouncer port 5433, change the pooling mode of PgBouncer to session. If a different pooling mode is enabled for PgBouncer, search_path may change for some connections, and tables will be unavailable by their short name.

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.