Migrating PostgreSQL databases to PostgreSQL TimescaleDB Managed Databases

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

Before migration, create a receiving PostgreSQL database cluster with a version no lower than that of the source cluster. If you have chosen logical dump as your migration method, the cluster versions must match.

The TimescaleDB versions must be the same in the receiving and source clusters. You can find the TimescaleDB version in the created receiving cluster.

Logical replication

In logical replication, a publish and subscribe model with one or more subscribers is used. They subscribe to one or more publications on the publishing node. A publication is created on the external source PostgreSQL cluster and subscribed to by the receiving Managed Database cluster.

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

Add the replication privilege to the user with access to the replicated data:
```
ALTER ROLE <user_name> WITH REPLICATION;
```
Specify <user_name> — 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> — the IP address or DNS name of the receiving cluster 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 master host;
- <port> — port;
- <database_name> — database name;
- <user_name> — 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> — IP address or DNS name of the receiving cluster host;
- <user_name> — database user name;
- <port> — port;
- <database_name> — database name;
- <dump_directory> — the 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> — 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> — subscription name;
- <host> — IP address or DNS name of the source cluster master host;
- <port> — port;
- <user_name> — database user name;
- <password> — user password;
- <database_name> — database name;
- <publication_name> — the publication name.
You can monitor the replication status using the pg_subscription_rel catalog:
```
SELECT * FROM pg_subscription_rel;
```
The general replication status can be viewed in the pg_stat_subscription and pg_stat_replication views for subscriptions and publications, respectively.
Sequences (sequences) are not replicated, so before transferring the load to the receiving cluster, restore the sequences dump to it if they are used. 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 containing commands for restoration) on the source cluster and restore it on the receiving cluster.

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

For your information

If you are using port 5433 for PgBouncer, change the PgBouncer pooling mode to session. If a different pooling mode is enabled for PgBouncer, the search_path for some connections may change, and those tables will not be accessible by their 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> — IP address or DNS name of the source cluster master host;
- <user_name> — user name;
- <database_name> — the database name.
Restore the dump on the receiving 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 receiving cluster master host;
- <port> — port;
- <user_name> — 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> — IP address or DNS name of the source cluster master host;
- <user_name> — 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> — IP address or DNS name of the receiving cluster master host;
- <user_name> — database user name;
- <database_name> — the database name.