In this article by Chitij Chauhan, author of the book PostgreSQL Cookbook, we will talk about various high availability and replication solutions, including some popular third-party replication tools such as Slony-I and Londiste.
In this article, we will cover the following recipes:
The important components for any production database is to achieve fault tolerance, 24/7 availability, and redundancy. It is for this purpose that we have different high availability and replication solutions available for PostgreSQL.
From a business perspective, it is important to ensure 24/7 data availability in the event of a disaster situation or a database crash due to disk or hardware failure. In such situations, it becomes critical to ensure that a duplicate copy of the data is available on a different server or a different database, so that seamless failover can be achieved even when the primary server/database is unavailable.
In this recipe, we are going to set up a master-slave streaming replication.
For this exercise, you will need two Linux machines, each with the latest version of PostgreSQL installed. We will be using the following IP addresses for the master and slave servers:
Before you start with the master-slave streaming setup, it is important that the SSH connectivity between the master and slave is setup.
Perform the following sequence of steps to set up a master-slave streaming replication:
psql -c "CREATE USER repuser REPLICATION LOGIN ENCRYPTED PASSWORD 'charlie';"
This is done by making the necessary changes as mentioned in the pg_hba.conf file:
Vi pg_hba.conf host replication repuser 192.168.0.5/32 md5
Vi /var/lib/pgsql/9.3/data/postgresql.conf listen_addresses = '*' wal_level = hot_standby max_wal_senders = 3 wal_keep_segments = 8 archive_mode = on archive_command = 'cp %p /var/lib/pgsql/archive/%f && scp %p postgres@192.168.0.5:/var/lib/pgsql/archive/%f' checkpoint_segments = 8
pg_ctl -D /var/lib/pgsql/9.3/data restart
psql -U postgres -h 192.168.0.4 -c "SELECT pg_start_backup('label', true)" rsync -a /var/lib/pgsql/9.3/data/ 192.168.0.5:/var/lib/pgsql/9.3/data/ --exclude postmaster.pid psql -U postgres -h 192.168.0.4 -c "SELECT pg_stop_backup()"
hot_standby = on
cp /usr/pgsql-9.3/share/recovery.conf.sample /var/lib/pgsql/9.3/data/recovery.conf standby_mode = on primary_conninfo = 'host=192.168.0.4 port=5432 user=repuser password=charlie' trigger_file = '/tmp/trigger.replication′ restore_command = 'cp /var/lib/pgsql/archive/%f "%p"'
service postgresql-9.3 start
psql -h 192.168.0.4 -d postgres -U postgres -W postgres=# create database test; postgres=# c test; test=# create table testtable ( testint int, testchar varchar(40) ); CREATE TABLE test=# insert into testtable values ( 1, 'What A Sight.' ); INSERT 0 1
psql -h 192.168.0.5 -d test -U postgres -W test=# select * from testtable; testint | testchar ---------+--------------------------- 1 | What A Sight. (1 row)
The following is the explanation for the steps performed in the preceding section.
In the initial step of the preceding section, we create a user called repuser, which will be used by the slave server to make a connection to the primary server. In the second step of the preceding section, we make the necessary changes in the pg_hba.conf file to allow the master server to be accessed by the slave server using the repuser user ID that was created in step 1. We then make the necessary parameter changes on the master in step 3 of the preceding section to configure a streaming replication. The following is a description of these parameters:
Now, with the data directory available on the slave, the next step is to configure it. We will now make the necessary parameter replication related parameter changes on the slave in the postgresql.conf directory on the slave server. We set the following parameters on the slave:
Here, we are going to set up replication using Slony-I. We will be setting up the replication of table data between two databases on the same server.
The steps performed in this recipe are carried out on a CentOS Version 6 machine. It is also important to remove the directives related to hot streaming replication prior to setting up replication using Slony-I.
We will first need to install Slony-I. The following steps need to be performed in order to install Slony-I:
tar xvfj slony1-2.2.3.tar.bz2 cd slony1-2.2.3
./configure --with-pgconfigdir=/usr/pgsql-9.3/bin/ make make install
You need to perform the following sequence of steps, in order to replicate data between two tables using Slony-I replication:
pg_ctl -D $PGDATA start
createdb test1 createdb test2
In the next step, we will create the t_test table on the source database, test1, and insert some records into it:
psql -d test1 test1=# create table t_test (id numeric primary key, name varchar); test1=# insert into t_test values(1,'A'),(2,'B'), (3,'C');
pg_dump -s -p 5432 -h localhost test1 | psql -h localhost -p 5432 test2
psql -d test2 test2=# select * from t_test;
cd /usr/pgsql-9.3/bin vi init_master.slonik #!/bin/sh cluster name = mycluster; node 1 admin conninfo = 'dbname=test1 host=localhost port=5432 user=postgres password=postgres'; node 2 admin conninfo = 'dbname=test2 host=localhost port=5432 user=postgres password=postgres'; init cluster ( id=1); create set (id=1, origin=1); set add table(set id=1, origin=1, id=1, fully qualified name = 'public.t_test'); store node (id=2, event node = 1); store path (server=1, client=2, conninfo='dbname=test1 host=localhost port=5432 user=postgres password=postgres'); store path (server=2, client=1, conninfo='dbname=test2 host=localhost port=5432 user=postgres password=postgres'); store listen (origin=1, provider = 1, receiver = 2); store listen (origin=2, provider = 2, receiver = 1);
cd /usr/pgsql-9.3/bin vi init_slave.slonik #!/bin/sh cluster name = mycluster; node 1 admin conninfo = 'dbname=test1 host=localhost port=5432 user=postgres password=postgres'; node 2 admin conninfo = 'dbname=test2 host=localhost port=5432 user=postgres password=postgres'; subscribe set ( id = 1, provider = 1, receiver = 2, forward = no);
cd /usr/pgsql-9.3/bin slonik init_master.slonik
cd /usr/pgsql-9.3/bin slonik init_slave.slonik
nohup slon mycluster "dbname=test1 host=localhost port=5432
user=postgres password=postgres" &
nohup slon mycluster "dbname=test2 host=localhost port=5432
user=postgres password=postgres" &
psql -d test1 test1=# insert into t_test values (5,'E');
psql -d test2 test2=# select * from t_test; id | name ----+------ 1 | A 2 | B 3 | C 5 | E (4 rows)
We will now discuss the steps performed in the preceding section:
For more information on Slony-I replication, go to http://slony.info/documentation/tutorial.html.
If you are using Slony-I for replication between two different servers, in addition to the steps mentioned in the How to do it… section, you will also have to enable authentication information in the pg_hba.conf file existing on both the source and target servers. For example, let’s assume that the source server’s IP is 192.168.16.44 and the target server’s IP is 192.168.16.56 and we are using a user named super to replicate the data.
If this is the situation, then in the source server’s pg_hba.conf file, we will have to enter the information, as follows:
host postgres super 192.168.16.44/32 md5
Similarly, in the target server’s pg_hba.conf file, we will have to enter the authentication information, as follows:
host postgres super 192.168.16.56/32 md5
Also, in the shell scripts that were used for Slony-I, wherever the connection information for the host is localhost that entry will need to be replaced by the source and target server’s IP addresses.
In this recipe, we are going to show you how to replicate data using Londiste.
For this setup, we are using the same host CentOS Linux machine to replicate data between two databases. This can also be set up using two separate Linux machines running on VMware, VirtualBox, or any other virtualization software. It is assumed that the latest version of PostgreSQL, version 9.3, is installed. We used CentOS Version 6 as the Linux operating system for this exercise.
To set up Londiste replication on the Linux machine, perform the following steps:
tar -xvzf skytools-3.2.tar.gz
cd skytools-3.2 ./configure --prefix=/var/lib/pgsql/9.3/Sky –with-pgconfig=/usr/pgsql-9.3/bin/pg_config make make install
export PYTHONPATH=/opt/PostgreSQL/9.2/Sky/lib64/python2.6/site-packages/
createdb node1 createdb node2
pgbench -i -s 2 -F 80 node1
Vi /tmp/prepare_pgbenchdb_for_londiste.sql -- add primary key to history table ALTER TABLE pgbench_history ADD COLUMN hid SERIAL PRIMARY KEY; -- add foreign keys ALTER TABLE pgbench_tellers ADD CONSTRAINT pgbench_tellers_branches_fk FOREIGN KEY(bid) REFERENCES pgbench_branches; ALTER TABLE pgbench_accounts ADD CONSTRAINT pgbench_accounts_branches_fk FOREIGN KEY(bid) REFERENCES pgbench_branches; ALTER TABLE pgbench_history ADD CONSTRAINT pgbench_history_branches_fk FOREIGN KEY(bid) REFERENCES pgbench_branches; ALTER TABLE pgbench_history ADD CONSTRAINT pgbench_history_tellers_fk FOREIGN KEY(tid) REFERENCES pgbench_tellers; ALTER TABLE pgbench_history ADD CONSTRAINT pgbench_history_accounts_fk FOREIGN KEY(aid) REFERENCES pgbench_accounts;
psql node1 -f /tmp/prepare_pgbenchdb_for_londiste.sql
pg_dump -s -t 'pgbench*' node1 > /tmp/tables.sql psql -f /tmp/tables.sql node2
Vi londiste.ini [londiste3] job_name = first_table db = dbname=node1 queue_name = replication_queue logfile = /home/postgres/log/londiste.log pidfile = /home/postgres/pid/londiste.pid
[postgres@localhost bin]$ ./londiste3 londiste3.ini create-root node1 dbname=node1 2014-12-09 18:54:34,723 2335 WARNING No host= in public connect string, bad idea 2014-12-09 18:54:35,210 2335 INFO plpgsql is installed 2014-12-09 18:54:35,217 2335 INFO pgq is installed 2014-12-09 18:54:35,225 2335 INFO pgq.get_batch_cursor is installed 2014-12-09 18:54:35,227 2335 INFO pgq_ext is installed 2014-12-09 18:54:35,228 2335 INFO pgq_node is installed 2014-12-09 18:54:35,230 2335 INFO londiste is installed 2014-12-09 18:54:35,232 2335 INFO londiste.global_add_table is installed 2014-12-09 18:54:35,281 2335 INFO Initializing node 2014-12-09 18:54:35,285 2335 INFO Location registered 2014-12-09 18:54:35,447 2335 INFO Node "node1" initialized for queue "replication_queue" with type "root" 2014-12-09 18:54:35,465 2335 INFO Don
[postgres@localhost bin]$ ./londiste3 londiste3.ini worker 2014-12-09 18:55:17,008 2342 INFO Consumer uptodate = 1
Vi slave.ini [londiste3] job_name = first_table_slave db = dbname=node2 queue_name = replication_queue logfile = /home/postgres/log/londiste_slave.log pidfile = /home/postgres/pid/londiste_slave.pid
./londiste3 slave.ini create-leaf node2 dbname=node2 –provider=dbname=node1 2014-12-09 18:57:22,769 2408 WARNING No host= in public connect string, bad idea 2014-12-09 18:57:22,778 2408 INFO plpgsql is installed 2014-12-09 18:57:22,778 2408 INFO Installing pgq 2014-12-09 18:57:22,778 2408 INFO Reading from /var/lib/pgsql/9.3/Sky/share/skytools3/pgq.sql 2014-12-09 18:57:23,211 2408 INFO pgq.get_batch_cursor is installed 2014-12-09 18:57:23,212 2408 INFO Installing pgq_ext 2014-12-09 18:57:23,213 2408 INFO Reading from /var/lib/pgsql/9.3/Sky/share/skytools3/pgq_ext.sql 2014-12-09 18:57:23,454 2408 INFO Installing pgq_node 2014-12-09 18:57:23,455 2408 INFO Reading from /var/lib/pgsql/9.3/Sky/share/skytools3/pgq_node.sql 2014-12-09 18:57:23,729 2408 INFO Installing londiste 2014-12-09 18:57:23,730 2408 INFO Reading from /var/lib/pgsql/9.3/Sky/share/skytools3/londiste.sql 2014-12-09 18:57:24,391 2408 INFO londiste.global_add_table is installed 2014-12-09 18:57:24,575 2408 INFO Initializing node 2014-12-09 18:57:24,705 2408 INFO Location registered 2014-12-09 18:57:24,715 2408 INFO Location registered 2014-12-09 18:57:24,744 2408 INFO Subscriber registered: node2 2014-12-09 18:57:24,748 2408 INFO Location registered 2014-12-09 18:57:24,750 2408 INFO Location registered 2014-12-09 18:57:24,757 2408 INFO Node "node2" initialized for queue "replication_queue" with type "leaf" 2014-12-09 18:57:24,761 2408 INFO Done
[postgres@localhost bin]$ ./londiste3 slave.ini worker 2014-12-09 18:58:53,411 2423 INFO Consumer uptodate = 1
vi pgqd.ini [pgqd] logfile = /home/postgres/log/pgqd.log pidfile = /home/postgres/pid/pgqd.pid
[postgres@localhost bin]$ ./pgqd pgqd.ini 2014-12-09 19:05:56.843 2542 LOG Starting pgqd 3.2 2014-12-09 19:05:56.844 2542 LOG auto-detecting dbs ... 2014-12-09 19:05:57.257 2542 LOG node1: pgq version ok: 3.2 2014-12-09 19:05:58.130 2542 LOG node2: pgq version ok: 3.2
[postgres@localhost bin]$ ./londiste3 londiste3.ini add-table --all 2014-12-09 19:07:26,064 2614 INFO Table added: public.pgbench_accounts 2014-12-09 19:07:26,161 2614 INFO Table added: public.pgbench_branches 2014-12-09 19:07:26,238 2614 INFO Table added: public.pgbench_history 2014-12-09 19:07:26,287 2614 INFO Table added: public.pgbench_tellers
[postgres@localhost bin]$ ./londiste3 slave.ini add-table –all
pgbench -T 10 -c 5 node1
[postgres@localhost bin]$ ./londiste3 slave.ini compare 2014-12-09 19:26:16,421 2982 INFO Checking if node1 can be used for copy 2014-12-09 19:26:16,424 2982 INFO Node node1 seems good source, using it 2014-12-09 19:26:16,425 2982 INFO public.pgbench_accounts: Using node node1 as provider 2014-12-09 19:26:16,441 2982 INFO Provider: node1 (root) 2014-12-09 19:26:16,446 2982 INFO Locking public.pgbench_accounts 2014-12-09 19:26:16,447 2982 INFO Syncing public.pgbench_accounts 2014-12-09 19:26:18,975 2982 INFO Counting public.pgbench_accounts 2014-12-09 19:26:19,401 2982 INFO srcdb: 200000 rows, checksum=167607238449 2014-12-09 19:26:19,706 2982 INFO dstdb: 200000 rows, checksum=167607238449 2014-12-09 19:26:19,715 2982 INFO Checking if node1 can be used for copy 2014-12-09 19:26:19,716 2982 INFO Node node1 seems good source, using it 2014-12-09 19:26:19,716 2982 INFO public.pgbench_branches: Using node node1 as provider 2014-12-09 19:26:19,730 2982 INFO Provider: node1 (root) 2014-12-09 19:26:19,734 2982 INFO Locking public.pgbench_branches 2014-12-09 19:26:19,734 2982 INFO Syncing public.pgbench_branches 2014-12-09 19:26:22,772 2982 INFO Counting public.pgbench_branches 2014-12-09 19:26:22,804 2982 INFO srcdb: 2 rows, checksum=-3078609798 2014-12-09 19:26:22,812 2982 INFO dstdb: 2 rows, checksum=-3078609798 2014-12-09 19:26:22,866 2982 INFO Checking if node1 can be used for copy 2014-12-09 19:26:22,877 2982 INFO Node node1 seems good source, using it 2014-12-09 19:26:22,878 2982 INFO public.pgbench_history: Using node node1 as provider 2014-12-09 19:26:22,919 2982 INFO Provider: node1 (root) 2014-12-09 19:26:22,931 2982 INFO Locking public.pgbench_history 2014-12-09 19:26:22,932 2982 INFO Syncing public.pgbench_history 2014-12-09 19:26:25,963 2982 INFO Counting public.pgbench_history 2014-12-09 19:26:26,008 2982 INFO srcdb: 715 rows, checksum=9467587272 2014-12-09 19:26:26,020 2982 INFO dstdb: 715 rows, checksum=9467587272 2014-12-09 19:26:26,056 2982 INFO Checking if node1 can be used for copy 2014-12-09 19:26:26,063 2982 INFO Node node1 seems good source, using it 2014-12-09 19:26:26,064 2982 INFO public.pgbench_tellers: Using node node1 as provider 2014-12-09 19:26:26,100 2982 INFO Provider: node1 (root) 2014-12-09 19:26:26,108 2982 INFO Locking public.pgbench_tellers 2014-12-09 19:26:26,109 2982 INFO Syncing public.pgbench_tellers 2014-12-09 19:26:29,144 2982 INFO Counting public.pgbench_tellers 2014-12-09 19:26:29,176 2982 INFO srcdb: 20 rows, checksum=4814381032 2014-12-09 19:26:29,182 2982 INFO dstdb: 20 rows, checksum=4814381032
The following is an explanation of the steps performed in the preceding section:
2014-12-09 19:26:18,975 2982 INFO Counting public.pgbench_accounts 2014-12-09 19:26:19,401 2982 INFO srcdb: 200000 rows, checksum=167607238449 2014-12-09 19:26:19,706 2982 INFO dstdb: 200000 rows, checksum=167607238449 2014-12-09 19:26:22,772 2982 INFO Counting public.pgbench_branches 2014-12-09 19:26:22,804 2982 INFO srcdb: 2 rows, checksum=-3078609798 2014-12-09 19:26:22,812 2982 INFO dstdb: 2 rows, checksum=-3078609798 2014-12-09 19:26:25,963 2982 INFO Counting public.pgbench_history 2014-12-09 19:26:26,008 2982 INFO srcdb: 715 rows, checksum=9467587272 2014-12-09 19:26:26,020 2982 INFO dstdb: 715 rows, checksum=9467587272 2014-12-09 19:26:29,144 2982 INFO Counting public.pgbench_tellers 2014-12-09 19:26:29,176 2982 INFO srcdb: 20 rows, checksum=4814381032 2014-12-09 19:26:29,182 2982 INFO dstdb: 20 rows, checksum=4814381032
This article demonstrates the high availability and replication concepts in PostgreSQL. After reading this chapter, you will be able to implement high availability and replication options using different techniques including streaming replication, Slony-I replication and replication using Longdiste.
Further resources on this subject:
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…