Continuous replication from old to new PostgreSQL using Slony

Native streaming replication in PostgreSQL only works between servers with the same major version. We talked about logical replication in a previous post . We saw how logical replication helps move data from one version of PostgreSQL to another. But logical replication is suitable only for supported versions of PostgreSQL, for example, for PostgreSQL 9.4 and PostgreSQL 11. What should I do with versions up to 9.4? Use Slony-i .

Use replication with Slony-I to transfer data from old databases to the latest version of PostgreSQL. What is Slony and how does it work?

This is the fourth post in our series Updating or migrating old versions of PostgreSQL to new ones , where we study different methods for updating PostgreSQL databases.

Slony

Slony is an application-level logical replication implementation for PostgreSQL. Or rather, it is a third-party replication tool that requires separate installation and configuration. Slony has been around for a long time. The latest version supports PostgreSQL from 8.4 to 11.

The main goal of replication is to transfer changes from one database server to another. To better understand the architecture, let's understand the terms: Slon, events and slonik.

By the way, Slony, if you didn’t guess, is "elephants." And they really have a great memory. Not by chance on the PostgreSQL logo flaunts a strict, but cute elephant .

Slon

Slon is a daemon that runs on every PostgreSQL node in Slony-I replication. These daemons are used to handle 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 the source of the changes, and the subscriber node receives and applies the changes from the publisher.

To configure replication, you must specify all replicated tables, or a replication set. A subscription works for a specific set. Changes to replicated tables are combined in SYNC, a group of transactions that are applied together at subscribers.

Developments

Changes are transmitted from the publisher in the form of events. When an event is processed by the Slon daemon on the remote host, an acknowledgment is generated. Events also notify nodes of configuration changes, such as the addition or removal of new nodes, new subscriptions, or changes to the DDL.

Each event has its own unique source identifier, sequence number, transaction identifier for a snapshot on the event node, several arguments, and a time stamp with a time zone.
Triggers written to PL / pgSQL capture all changes in replicated tables. Unfortunately, there is no reliable way to handle blob changes, DDL, or changes to users and roles.

slonik

This is a command line utility with an analyzer and interpreter that accepts slonik scripts - a simple declarative language. It is designed to overcome the limitations of a procedural language. Using slonik commands, you can customize or change the replication in Slony, and you can embed them in shell scripts. It accepts commands from standard input or from files. In the example below you can see how the slonik script is passed to the slonik utility and embedded in shell scripts.

The script that creates the initial configuration for a simple master-slave scheme in our pgbench database looks like this:

#!/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 is Slony convenient to use for migrations?

Despite the advantages of internal logical replication, for versions prior to PostgreSQL 9.4, you have to use this third-party solution. The trigger-based approach depends on the database API - both versions must be compatible to use the PL / pgSQL and SQL syntax.

How to adapt the database for use with Slony?

• Tables must have primary keys. Add the serial field to all tables without a primary key.
• Changes in OID blob not replicated. If you have columns with short values, convert them to BYTEA. If the objects are very large, for example, images, it is better to store data in external storage (say, S3 in the Amazon cloud). If changing the application is too difficult, apply the change blob at the last stage of the migration.
• ALTER TABLE and other DDL operations. Slony does not detect changes to the table structure. Use the slonik EXECUTE SCRIPT command to apply a SQL file with SQL or DDL strings to the entire replication cluster.

Online migration from previous versions of PostgreSQL

1. Create a replication user with superuser privileges. You can fine-tune the rights, but it is much more difficult.
2. Create a database at your destination with TCP / IP access.
3. Copy table definitions from master to slave.
4. Install Slony-I. On servers with an old OS version, it will be easier to install Slony-I from source code.
5. Define a cluster, a set of tables, and information about connecting to nodes as a list of slonik commands.
6. Run the slon daemon on each PostgreSQL server. Check the standard output or log files for connection errors.
7. Run slonik subscription commands to start synchronization.
8. Test read-only requests in the new Postgres version.
9. When all data has been replicated and synchronized, stop the applications and send them to the new Postgres server.
10. Use the uninstall node in the new PostgreSQL version to remove all traces of Slony replication.

Switch to previous versions

To upgrade to previous versions, use the same procedure. With Slony, you can replicate from any version to any version of PosgreSQL that Slony version supports. The minimum supported version is 8.4.

Results

We outlined how you can upgrade to the new version with minimal downtime using Slony. Learn more at our webinar .