With the help of our open source extension, Aiven users can perform logical replication without superuser roles. Read this post to learn more.
Hannu Valtonen
|RSS FeedCo-founder at Aiven
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
There have been a multitude of ways to perform logical replication, such as trigger-based replication. But, they all have come with significant shortcomings either in performance or operational complexity.
The release of PostgreSQL 10's logical replication fundamentally changed this by allowing easy, performant logical replication to be performed out-of-box.
PostgreSQL 10’s support for logical replication using publish/subscribe made it possible for a number of use cases, such as replicating a subset of tables in a database or performing no-downtime upgrades between major versions.
You might be asking why this hasn't been supported on Aiven if PostgreSQL 10 supported it out-of-box. The short answer?
It requires superuser privileges to perform it; and, we can’t grant them to our main database user, avnadmin because we need to ensure our level of service. But, it got us thinking,
If we allow avnadmin to create databases and roles at will, would it be possible to support publish/subscribe-style logical replication?
After all, some DBaaS vendors patched their PostgreSQL services with proprietary changes to allow them to grant these privileges to non-superusers roles, but we wanted to do something better: we tried to do it as an Open Source PostgreSQL extension.
The result? Aiven-extras. Simply put, this extension creates functions that allow users to perform publish/subscribe-style logical replication without a superuser account as the functions themselves are run with superuser privileges.
Now that we know why it’s important and what we did to make it possible for Aiven PostgreSQL users, let’s look at a simple example of replicating a single table with a single column.
As stated previously, we’ll replicate a single table called foo that has a single column called id in it:
Loading code...
PostgreSQL 10 defined PUBLICATIONs as entries that define the tables that are to be replicated which are in turn SUBSCRIBED to by the receiving database.
When creating a publication you can also define which operations should be transferred. For example, you can define that only INSERTs should be replicated but not UPDATEs and DELETEs. In this example we'll replicate all three types of operations:
Loading code...
Next, we’ll log into the destination database that will be receiving the changes from the source database and begin by creating the new aiven_extras extension:
Loading code...
As PostgreSQL's logical replication doesn't copy table definitions, we need to copy them with something like pg_dump:
Loading code...
or, if there are only a few tables to be defined, we can simply define them with the appropriate CREATE TABLE statements:
Loading code...
Once the table definitions are present in the destination PostgreSQL, we can create a SUBSCRIPTION to start replicating changes from the source database:
Loading code...
As the pg_subscription catalog is superuser-only we need to use the aiven_extras.pg_list_all_subscriptions()function from aiven-extras to see that the subscription was indeed created successfully.
Loading code...
Once the subscription has been created you can execute:
Loading code...
And we can see that the data was copied successfully from the source database.
It is also important not to leave unused replication setups, because the underlying replication slots in PostgreSQL make sure that the server keeps all the data needed to replicate from that time forward; and, if no-one is reading the data stream it will keep ever growing amounts of data on disk until it eventually gets full.
Once we know we want to get rid of a subscription, we can run:
Loading code...
To see that everything got cleaned up correctly, you can list all the subscriptions again to see that the one you deleted is really gone:
Loading code...
And finally here's an example for publishing all the tables in the source side database with the help of the aiven-extras extension.
While CREATE PUBLICATION doesn't require superuser privileges if you publish just an enumerated list of tables, it does require it if you want all the tables to be published.
Loading code...
Publish/subscribe based logical replication greatly increases the utility of Aiven PostgreSQL. And with the open source aiven_extras extension, you will now be able to use logical replication within Aiven.
Although it can be used for replicating a subset of tables in a database or performing no-downtime upgrades between major versions, one of its most interesting use cases is migrating your PostgreSQL database from one cloud to another.
Soon, we’ll cover migrating your database in another post. In the meantime, join our blog and changelog RSS feeds; or, follow us on Linkedin and Twitter.
source $ CREATE TABLE foo (id INT);
source $ INSERT INTO foo VALUES (1), (2);source $ CREATE PUBLICATION pub1 FOR TABLE foo WITH (publish='insert,update,delete');destination $ CREATE EXTENSION aiven_extras CASCADE;$ pg_dump --schema-only postgres://avnadmin:lab0h4c84r38heui@source-demoprj.aivencloud.com:26882/defaultdb?sslmode=require | psql postgres://avnadmin:o42w0sjr80esj967@destination-demoprj.aivencloud.com:26882/defaultdb?sslmode=require
destination $ CREATE TABLE foo (id INT);destination $ SELECT * FROM aiven_extras.pg_create_subscription('subscription', 'host=source-demoprj.aivencloud.com password=lab0h4c84r38heui port=26882 dbname=defaultdb user=avnadmin sslmode=require', 'pub1', 'slot', TRUE, TRUE);destination=> SELECT * FROM aiven_extras.pg_list_all_subscriptions();
subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
---------+--------------+----------+------------+------------------------------------------------------------------------------------------------------------------------+-------------+---------------+-----------------
16394 | subscription | 10 | t | host=source-demoprj.aivencloud.com password=lab0h4c84r38heui port=26882 dbname=defaultdb user=avnadmin sslmode=require | slot | off | {pub1}
(1 row)destination $ SELECT * FROM foo;
id
----
1
2
(2 rows)destination $ SELECT * FROM aiven_extras.pg_drop_subscription('subscription');destination $SELECT * FROM aiven_extras.pg_list_all_subscriptions();
subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
---------+---------+----------+------------+-------------+-------------+---------------+-----------------
(0 rows)source $ CREATE EXTENSION aiven_extras CASCADE;
source $ SELECT * FROM aiven_extras.pg_create_publication_for_all_tables('pub2', 'INSERT');
source $ SELECT * FROM pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
pub1 | 16393 | f | t | t | t
pub2 | 16393 | t | t | f | f
(2 rows)