Skip to main content

Migration: On-premise EDB Postgres Advanced Server(EPAS) to BigAnimal using Logical Replication

shanshan zhao
  • Updated

Prerequisites 

  • DB type should be the same between the publication server and the subscription server. 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
  • BigAnimal 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.     Scaling up more CPUs during the migration by modifying cluster instance type, the CPUs can be scaled down after migration.
    2. max_worker_processes is at least 16

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 BigAnimal

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 (BigAnimal)

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 BigAnimal instance to complete the migration procedure. At this point, make sure to update any sequences on the BigAnimal target database to their current value based on the contents of the migrated tables.

About terminate replication

If you don’t need the replication anymore, then you can remove the subscription from BigAnimal.

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