Prerequisites
- DB type should be the same between the publication and subscription servers. Either from PostgreSQL to PostgreSQL or EPAS to EPAS.
- Superuser privilege is required for the subscription server.
-
To add a table to a publication, the invoking user must have ownership rights on the table. The FOR ALL TABLES and FOR TABLES IN SCHEMA clauses require the invoking user to be a superuser. PostgreSQL: Documentation: 15: CREATE PUBLICATION
- Publication server has access to the subscription server
-
EDB Postgres AI Database WAL archiving compresses the WAL files and uploads them to Cloud storage. The number of CPU cores affects the parallel WAL archiving jobs. The best practice
1. You can scale up more CPUs during the migration by modifying the cluster instance type; the CPUs can be scaled down after migration.
2. max_worker_processes is at least 16 -
In this article, we will discuss the migration between
- Source(Publication): On-Premise Database
- Target(Subscription): EDB Postgres AI Database Cluster
Step 1 Create Publication from on-prem EPAS
1.1 Change the WAL level(wal_level) to logical (replica by default).
1.2 Restart the on-prem EPAS instance for the new configuration to take effect. You can verify the setting below:
postgres=# show wal_level;
wal_level
-----------
logical
(1 row)
1.3 Dump schemas and objects from on-prem EPAS
Copy the table(s) definition from the source instance:
pg_dump -s -t <table_name> -h <on-prem EPAS instance> -p <on-prem EPAS pg_port> -U <on-prem EPAS DB_user> <DB_name> > schema_table_name.dump
or, for all tables:
pg_dump -s -h <on-prem EPAS instance> -p <on-prem EPAS pg_port> -U <on-prem EPAS DB_user> <DB_name> > schema.dump
As well as users and other global objects:
pg_dumpall -g -h <on-prem EPAS instance> > globals.dump
NOTE: This dumps all objects, including any previously created publication that needs thus to be dropped.
1.4 Create the publication(s) for one/all table(s)
CREATE PUBLICATION <publication_name> FOR TABLE <table_name>;
Or e.g.:
CREATE PUBLICATION <publication_name> FOR ALL TABLES;
If the database is large, the best practice is to add tables to the logical replication one by one in order to control the flow rather than having one massive uninterrupted sync operation.
1.5 Check if the desired table(s) were added to the related publication(s):
SELECT * FROM pg_publication_tables;
sample output:
pubname | schemaname | tablename
-----------+----------------+----------------
migration | public | table1
migration | public | table2
migration | public | table3
(3 rows)
Step 2 Create Subscription from EDB Postgres AI Database
2.1 Creating your database. Default database edb_admin is not recommended to load data.
CREATE DATABASE <DB_name>;
2.2 Import the tables' and global objects' definitions
psql -h <BA host_addr> -p 5432 -U edb_admin <DB_name> -f schema_table_name.dump
psql -h <BA host_addr> -p 5432 -U edb_admin <DB_name> -f globals.dump
Any previously created publication that was included in the dump, needs to be dropped:
TABLE pg_publication;
DROP PUBLICATION <publication_name>;
2.3 Create the subscription(s) to the publication(s)
CREATE SUBSCRIPTION <subscription_name> CONNECTION '<on-prem epas_connection_string>' PUBLICATION <publication_name>;
E.g.:
CREATE SUBSCRIPTION <subscription_name> CONNECTION 'host=epas_host port=5444 dbname=<epas_db> user=<epas_user> password=<epas_password>' PUBLICATION <publication_name>;
2.4 Check the status of subscription(s):
SELECT * FROM pg_stat_subscription_rel;
sample output:
srsubid | srrelid | substate | srsublsn
-----------+-------------+---------------+-------------
nnnnn | nnnnn | r | FFFF/XXXXXXXX
(1 row)
Step 3 Observability and Monitoring
Query these views during the copy process to monitor the initial sync.
From the target (EDB Postgres AI Database)
pg_stat_subscription provides statistics and status information about logical replication subscriptions.
SELECT * FROM pg_stat_subscription;
pg_stat_progress_copy provides information on the progress of COPY operations. In the context of logical replication, this view can be used to monitor the progress of table synchronization between a publisher and subscriber.
SELECT * FROM pg_stat_progress_copy;
From the source (on-prem EPAS)
pg_stat_replication provides information on the current state of streaming replication connections.
SELECT * FROM pg_stat_replication;
pg_replication_slots provides a listing of all replication slots that currently exist on the database cluster, along with their current state.
SELECT * FROM pg_replication_slots;
Step 4 Cutover
Once the data sync has been completed, reconnect the application from the on-prem EPAS instance to the target instance to complete the migration procedure.
At this point, update any sequences on the target database to their current value based on the contents of the migrated tables.
In case you have multiple tables with lots of sequences, it would be nice to include in the guidance step how to backup and restore sequences before cutover
pg_dump -h Source_HOST -U DB_User -n SCHEMA_NAME -t SCHEMA_NAME.*_seq' --data-only -d DB_NAME -F p > seq_schema.dump
psql -h BA_HOST -U DB_User -d DB_NAME -n SCHEMA_NAME -f seq_schema.dump
About terminate replication
If you don’t need the replication, you can remove the subscription from the target database.
DROP SUBSCRIPTION <subscription_name>;
Verify the replication slot is removed from on-prem EPAS:
TABLE pg_replication_slots;
Restrictions for Logical Replication
Logical replication currently has restrictions or missing functionality -> PostgreSQL: Documentation: 15: 31.6. Restrictions
max_replication_slots
In case you have multiple databases with multiple schemas, please pay attention to the below settings:
Subscriber BA:
max_replication_slots
max_logical_replication_workers
Source DB:
max_replication_slots