Set up logical replication to Aiven for PostgreSQL®#
Aiven for PostgreSQL® represents an ideal managed solution for a variety of use cases; remote production systems can be completely migrated to Aiven using different methods including using Aiven-db-migrate or the standard dump and restore method.
Whether you are migrating or have another use case to keep an existing system in sync with an Aiven for PostgreSQL service, setting up a logical replica is a good way to achieve that. This article goes through the steps of replicating some tables from a self-managed PostgreSQL cluster to Aiven.
These instructions work also with AWS RDS PostgreSQL 10+ and Google CloudSQL PostgreSQL.
These are the placeholders you will need to replace in the code sample:
Hostname of the source PostgreSQL database
Port of the source PostgreSQL database
Database Name of the source PostgreSQL database
Username of the source PostgreSQL database
Password of the source PostgreSQL database
Connection URI of the source PostgreSQL database
You will need:
PostgreSQL version 10 or newer.
Connection between the source cluster’s PostgreSQL port and Aiven for PostgreSQL cluster.
Access to an superuser role on the source cluster.
logicalon the source cluster. To verify and change the
wal_levelsetting check the instructions on setting this configuration.
If you are using an AWS RDS PostgreSQL cluster as source, the
rds.logical_replication parameter must be set to
1 (true) in the parameter group.
Set up the replication#
To create a logical replication, there is no need to install any extensions on the source cluster, but a superuser account is required.
aiven_extras extension enables the creation of a publish/subscribe-style logical replication without a superuser account, and it is preinstalled on Aiven for PostgreSQL servers. For more info on
aiven_extras check the dedicated GitHub repository. The following example will assume
aiven_extras extension is not available in the source PostgreSQL database.
This example assumes a source database called
origin_database on a self-managed PostgreSQL cluster. The replication will mirror three tables, named
test_table_3, to the
defaultdb database on Aiven for PostgreSQL. The process to setup the logical replication is the following:
On the source cluster, connect to the
pub_source_tables, for the test tables:
CREATE PUBLICATION pub_source_tables FOR TABLE test_table,test_table_2,test_table_3 WITH (publish='insert,update,delete');
In PostgreSQL 10 and above,
PUBLICATIONentries define the tables to be replicated, which are in turn
SUBSCRIBEDto by the receiving database.
When creating a publication entry, the
publishparameter defines the operations to transfer. In the above example, all the
DELETEoperations will be transferred.
PostgreSQL’s logical replication doesn’t copy table definitions, that can be extracted from the
pg_dumpand included in a
pg_dump --schema-only --no-publications \ SRC_CONN_URI \ -t test_table -t test_table_2 -t test_table_3 > origin-database-schema.sql
psqlto the destination Aiven for PostgreSQL database and create the new
CREATE EXTENSION aiven_extras CASCADE;
Create the table definitions in the Aiven for PostgreSQL destination database within
dest_subscription, in the Aiven for PostgreSQL destination database to start replicating changes from the source
SELECT * FROM aiven_extras.pg_create_subscription( 'dest_subscription', 'host=SRC_HOST password=SRC_PASSWORD port=SRC_PORT dbname=SRC_DATABASE user=SRC_USER', 'pub_source_tables', 'dest_slot', TRUE, TRUE);
Verify that the subscription has been created successfully. As the
pg_subscriptioncatalog is superuser-only, you can use the
SELECT subdbid, subname, subowner, subenabled, subslotname FROM aiven_extras.pg_list_all_subscriptions(); subdbid | subname | subowner | subenabled | subslotname ---------+-------------------+----------+------------+------------- 16401 | dest_subscription | 10 | t | dest_slot (1 row)
Verify the subscription status:
SELECT * FROM pg_stat_subscription; subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time -------+-------------------+-----+-------+--------------+-------------------------------+-------------------------------+----------------+------------------------------- 16444 | dest_subscription | 869 | | 0/C002360 | 2021-06-25 12:06:59.570865+00 | 2021-06-25 12:06:59.571295+00 | 0/C002360 | 2021-06-25 12:06:59.570865+00 (1 row)
Verify the data is correctly copied over the Aiven for PostgreSQL target tables
Remove unused replication setup#
It is important to remove unused replication setups, since the underlying replication slots in PostgreSQL forces the server to keep all the data needed to replicate since the publication creation time. If the data stream has no readers, there will be an ever-growing amount of data on disk until it becomes full.
To remove an unused subscription, essentially stopping the replication, run the following command in the Aiven for PostgreSQL target database:
SELECT * FROM aiven_extras.pg_drop_subscription('dest_subscription');
Verify the replication removal with:
SELECT * FROM aiven_extras.pg_list_all_subscriptions(); subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications ---------+---------+----------+------------+-------------+-------------+---------------+----------------- (0 rows)
Manage inactive or lagging replication slots#
Inactive or lagging replication could cause problems in a database, like an ever-increasing disk usage not associated to any growth of the amount of data in the database. Filling the disk causes the database instance to stop serving clients and thus a loss of service.
Assess the replication slots status via
SELECT slot_name,restart_lsn FROM pg_replication_slots;
The command output is like:
slot_name │ restart_lsn ───────────────┼───────────── pghoard_local │ 6E/16000000 dest_slot | 5B/8B0 (2 rows)
restart_lsnvalues between the replication slot in analysis (
dest_slotin the above example) and
pghoard_local: the hexadecimal difference between the them states how many write-ahead-logging (WAL) entries are waiting for the target
dest_slotconnector to catch up.
In the above example the difference is 0x6E - 0x5B = 19 entries
If, after assessing the lag, the
dest_slotconnector results lagging or inactive:
dest_slotconnector is still in use, a recommended approach is to restart the process and verify if it solves the problem. You can disable and enable the associated subscription using
SELECT * FROM aiven_extras.pg_alter_subscription_disable('dest_subscription'); SELECT * FROM aiven_extras.pg_alter_subscription_enable('dest_subscription');
dest_slotconnector is no longer needed, run the following command to remove it:
In both cases, after the next PostgreSQL checkpoint, the disk space that the WAL logs have reserved for the
dest_subscriptionconnector should be freed up.
- The checkpoint occurs only when
an hour has elapsed (we use a
checkpoint_timeoutvalue of 3600 seconds), or
5% of disk write operations is reached (the
max_wal_sizevalue is set to 5% of the instance storage).
For further information about WAL and checkpoints, read the PostgreSQL documentation.
The recreation of replication slots gets enabled automatically for services created or updated as of January 2023. Additional details are outlined in our blog post.
Replication slots are recreated when a maintenance update is applied or a failover occurs (for multi-node clusters), but they are not recovered after major version upgrades.