July 1, 2019 at 12:27

Continuous Replication from Old to New PostgreSQL with Slony

Original author: Nickolay Ihalainen
  • Transfer


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 only suitable for supported versions of PostgreSQL, for example, PostgreSQL 9.4 and PostgreSQL 11. What to do with versions prior 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 Upgrading or migrating older versions of PostgreSQL to new ones , where we learn 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 purpose of replication is to transfer changes from one database server to another. To better understand the architecture, let's look at the terms: Slon, events, and slonik.


By the way, Slony, if you haven’t guessed, these are “elephants”. And they really have a great memory. It is no coincidence that a strict but cute elephant flaunts on the PostgreSQL logo .


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 host. 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 set of replication. Subscription works for a specific set. Changes to replicated tables are combined into SYNC, a group of transactions that are applied together on subscribers.


Events


Changes are reported from the publisher as events. When an event is processed by the Slon daemon on the remote host, an acknowledgment is generated. And events notify the nodes of configuration changes, such as adding or removing new nodes, new subscriptions, or DDL changes.


Each event has its own unique source identifier, serial number, transaction identifier for the snapshot on the event node, several arguments and a time stamp with a time zone.
Triggers written to PL / pgSQL log all changes to replicated tables. Unfortunately, there is no reliable way to handle changes to blobs, DDLs, 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. With the help of slonik commands, you can configure or modify replication in Slony, and they can be embedded in shell scripts. It accepts commands from standard input or from files. The example below shows how the slonik script is passed to slonik 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 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 a serial field to all tables without a primary key.
  • Changes to the OID blob are 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 complicated, apply the blob changes in the last step of the migration.
  • ALTER TABLE and other DDL operations. Slony does not detect table structure changes. Use the slonik EXECUTE SCRIPT command to apply an 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 configure the rights in detail, but it is much more complicated.
  2. Create a database at the destination with TCP / IP access.
  3. Copy the table definitions from the master to the slaves.
  4. Install Slony-I. On servers with an old version of the OS, it will be easier to install Slony-I from the source code.
  5. Define the cluster, set of tables, and node connection information 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 the slonik subscription commands to start the synchronization.
  8. Test read-only requests in the new version of Postgres.
  9. When all the data is replicated and synchronized, stop the applications and direct them to the new Postgres server.
  10. Use the uninstall node in the new version of PostgreSQL to remove all traces of Slony replication.

Transition to previous versions


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


Summary


We saw in general terms how you can upgrade to the new version with minimal downtime using Slony. Find out more at our webinar .

Tags:

Also popular now: