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
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
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.
Events
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
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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | #!/bin/sh slonik <<_EOF_ cluster name = percona_pg; node 1 admin conninfo = 'dbname=pg93 host=pg93_host user=percona_pg93_user'; node 2 admin conninfo = 'dbname=pg11 host=pg11_host user=percona_pg11_user'; #-- # Creates a _$(clustername), this example, _percona_pg schema #-- init cluster ( id=1, comment = 'Legacy PG Node'); #-- # Add a list of tables being replicated to a set. #-- create set (id=1, origin=1, comment='pgbench'); set add table (set id=1, origin=1, id=1, fully qualified name = 'public.pgbench_accounts', comment='accounts'); set add table (set id=1, origin=1, id=2, fully qualified name = 'public.pgbench_branches', comment='branches'); set add table (set id=1, origin=1, id=3, fully qualified name = 'public.pgbench_tellers', comment='tellers'); set add table (set id=1, origin=1, id=4, fully qualified name = 'public.pgbench_history', comment='history'); #-- # Create the second node (the slave) tell the 2 nodes how to connect to # each other and how they should listen for events. #-- store node (id=2, comment = 'Target node', event node=1); store path (server = 1, client = 2, conninfo='dbname=pg93 host=pg93_host user=percona_pg93_user'); store path (server = 2, client = 1, conninfo='dbname=pg11 host=pg11_host user=percona_pg11_user'); _EOF_ |
Why Slony is useful for migrations?
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.
How to adapt your database for usage with Slony?
- Your tables have to have primary keys. Add a serial field to all tables without primary key
- OID blobs will not have their changes replicated. If you have columns with small-length values, you could convert these to BYTEA. For a really large objects like images, it’s better to store data externally e.g. use S3 in Amazon cloud. If it’s too hard to change your application, you could apply blob changes at the last stage of migration.
- ALTER TABLE and other DDL operations. Slony can’t detect table structure changes. Instead you should use an EXECUTE SCRIPT slonik command to apply a SQL file with DDL or SQL strings to the whole replication cluster.
Online migration from legacy PostgreSQL
- Create replication user with superuser privileges. It’s possible to use fine-grained privileges, but they are significantly harder to setup.
- Create a database on the destination, setup access by TCP/IP
- Copy table definitions from master to slaves
- Install Slony-I. On servers with an old OS distribution you might find it simpler to install Slony-I from the source code.
- Define cluster, set of tables, and connection information to nodes as a list of slonik commands
- Start the slon daemon on each postgresql server. Check standard output or log files for any potential communication errors.
- Execute subscription slonik commands to start sync
- Test your read-only queries with a newer version of postgres
- Once all the data has been replicated and is in sync, stop your applications and repoint them to the new postgres server.
- Use “uninstall node” on the newer version of PostgreSQL to remove all traces of Slony replication
Downgrade steps
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.
Summary
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.
