Aiven PostgreSQL^® now supports logical replication

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.

Why we didn't support logical replication immediately

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?

Aiven's open source extension for 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.

Example of publish/subscribe-style logical replication

As stated previously, we’ll replicate a single table called foo that has a single column called id in it:

source $ CREATE TABLE foo (id INT);
source $ INSERT INTO foo VALUES (1), (2);
Copy to clipboard

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:

source $ CREATE PUBLICATION pub1 FOR TABLE foo WITH (publish='insert,update,delete');
Copy to clipboard

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:

destination $ CREATE EXTENSION aiven_extras CASCADE;
Copy to clipboard

As PostgreSQL's logical replication doesn't copy table definitions, we need to copy them with something like pg_dump:

$ 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
Copy to clipboard

or, if there are only a few tables to be defined, we can simply define them with the appropriate CREATE TABLE statements:

destination $ CREATE TABLE foo (id INT);
Copy to clipboard

Once the table definitions are present in the destination PostgreSQL, we can create a SUBSCRIPTION to start replicating changes from the source database:

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);
Copy to clipboard

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.

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)
Copy to clipboard

Once the subscription has been created you can execute:

destination $ SELECT * FROM foo;
id
----
 1
 2
(2 rows)
Copy to clipboard

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:

destination $ SELECT * FROM aiven_extras.pg_drop_subscription('subscription');
Copy to clipboard

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:

destination $SELECT * FROM aiven_extras.pg_list_all_subscriptions();
 subdbid | subname | subowner | subenabled | subconninfo | subslotname | subsynccommit | subpublications
---------+---------+----------+------------+-------------+-------------+---------------+-----------------
(0 rows)
Copy to clipboard

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.

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)
Copy to clipboard

Wrapping up

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.