Native streaming replication in PostgreSQL works only between servers running the same major version. We discussed about logical replication in our previous blog post. In that post, we saw how logical replication could help us set up migration between two different PostgreSQL versions. However, logical replication works only for the currently supported versions of PostgreSQL, for example between PostgreSQL 9.4 and PostgreSQL 11. So what about the legacy versions that are older than 9.4? Slony-I could help us meet this replication requirement.
Replication between different PostgreSQL versions with Slony-I is useful for migration from legacy database installations to the latest available version. So what is Slony and how does it work?
This post is the fourth of our Upgrading or Migrating Your Legacy PostgreSQL to Newer PostgreSQL Versions series where we’ll be exploring different methods available to upgrade your PostgreSQL databases.
Slony is an application-level logical replication implementation for PostgreSQL. Rather, we could say that it is an external replication tool that requires a separate installation and configuration. Slony has been around for a long time. The latest version supports PostgreSQL versions from 8.4 and up to 11.
The main goal for replication is to ship changes from one database server to another. To better understand the architecture, you should know the terms such as Slon, Events and Slonik in Slony-I.
An aside: Slony means elephants in Russian, and elephants are indeed reputed to have a great memory. A slightly angry, but nevertheless pretty elephant, “Slonik“, looks at you from the PostgreSQL logo image.
Slon is a daemon that runs on each PostgreSQL node in Slony-I replication. These daemons are used for processing configuration and replication events for each PostgreSQL server. Each PostgreSQL server is called a “node”. All nodes together form a Slony “cluster”.
The “publisher node” is a source for replicated changes. While “subscriber” nodes receive and apply changes from the publisher node.
In order to setup replication, we should specify all replicated tables or “set”. Actual subscription works within a specific set. Changes to the tables being replicated are grouped together into SYNCs. These group of transactions are applied together to the subscriber nodes.
Changes are transferred from the publisher in the form of “events”. When an event is processed by the slon daemon on a remote node, it generates a “confirmation”. Events are also used to notify nodes about configuration changes like adding/removing new nodes, new subscriptions or DDL changes.
Each event has a unique origin identifier, sequence number, transaction id for the snapshot on provider node for this event, multiple arguments, and timestamp with timezone.
Triggers written in PL/pgSQL register all changes in replicated tables. Unfortunately, there is no reliable way yet to handle changes to large objects (BLOBS), DDLs, or changes to users and roles.
Slonik means a little elephant. It is a command line utility with parser and interpreter and it accepts “slonik scripts” – a simple declarative scripting language. It is designed to overcome the limitations of procedural language. You use slonik commands to set up or modify slony replication and they can be embedded in shell scripts. It can accept commands from standard input or from files. The following example shows how a slonik script being fed to the slonik utility, and then embedded in shell scripts.
The script to create the initial configuration for the simple master-slave setup of our pgbench database looks like this:
|
1 |
#!/bin/sh<br><br>slonik <<_EOF_<br><br> cluster name = percona_pg;<br> node 1 admin conninfo = 'dbname=pg93 host=pg93_host user=percona_pg93_user';<br> node 2 admin conninfo = 'dbname=pg11 host=pg11_host user=percona_pg11_user';<br> #--<br> # Creates a _$(clustername), this example, _percona_pg schema<br> #-- <br><br> init cluster ( id=1, comment = 'Legacy PG Node');<br> #--<br> # Add a list of tables being replicated to a set. <br> #--<br><br>create set (id=1, origin=1, comment='pgbench');<br> set add table (set id=1, origin=1, id=1, fully qualified name = 'public.pgbench_accounts', comment='accounts');<br> set add table (set id=1, origin=1, id=2, fully qualified name = 'public.pgbench_branches', comment='branches');<br> set add table (set id=1, origin=1, id=3, fully qualified name = 'public.pgbench_tellers', comment='tellers');<br> set add table (set id=1, origin=1, id=4, fully qualified name = 'public.pgbench_history', comment='history');<br><br> #--<br> # Create the second node (the slave) tell the 2 nodes how to connect to<br> # each other and how they should listen for events.<br> #--<br> store node (id=2, comment = 'Target node', event node=1);<br> store path (server = 1, client = 2, conninfo='dbname=pg93 host=pg93_host user=percona_pg93_user');<br> store path (server = 2, client = 1, conninfo='dbname=pg11 host=pg11_host user=percona_pg11_user');<br>_EOF_ |
Despite the benefits of internal logical replication, this external solution is better for migrations between different versions that are older than PostgreSQL 9.4. The trigger-based approach depends on the database API – both older and newer versions should be compatible for PL/pgSQL and SQL syntax.
To downgrade, follow the same procedure as upgrade. Slony allows you to replicate from and to any versions of PosgreSQL supported by your slony version. The minimum supported version is 8.4.
So far, we have seen a high level overview of how Slony can be helpful to perform an upgrade with the least possible downtime. Come and see more of this in action during our Webinar. And don’t forget at Percona Live in Austin, May 28-30 2019, we’ll have two days of PostgreSQL content in a postgres dedicated track.
