Archive for category Replication

PostgreSQL streaming replication (hot_standby)

This is an inbuilt replication system in PostgreSQL that uses wal files to apply changes to the secondary server. Since this replication uses wal files any changes on the primary server(schema or data)are automatically applied to the secondary server.

Points to note:

*One thing to keep into consideration is that primary and secondary database servers should have the same major release installed in order to have this replication working. For eg if you have PostgreSQL 9.3.3 installed on your secondary server you can have 9.3.x on your master or vice a versa. However, any major release 9.1.x will not work with 9.3.x.

*Another point to take into cosideration is that whatever data objects you have on your primary server instance, all of that will be replicated to the secondary instance. You cannot have selective tables database or schemas replicate to your secondary database as it applies wal logs to the secondary server. Exceptional objects that are not replicated to your secondary database are the unlogged tables for eg temp tables.


*I assume you have postgreSQL 9.0 or higher installed on both primary and secondary server.
*I am using rsync to copy the wal files over to the standby. Make sure rsync works from the primary to the secondary server without using a password (public private key sharing).

On the Primary server:

1. Open the postgresql.conf file and make the following changes:

wal_level = hot_standby
archive_mode = on
archive_command = 'rsync %p hostname_or_ipaddress_secondary_server:path_to_the_archive_dir_on_secondary_server/%f' (create this directory on the secondary server)
max_wal_senders = 1 #This is the number of secondary servers you want to send the wal files to. If you add another server to replication increment this value to 2.
wal_keep_segments = 1200 (this is the number of wal files to keep in case the replication lags behind. Make sure there is enough space in the pg_xlog mount point to accomodate the wal files in the pg_xlog dir. 1200*16/1024=18.75GB)

2. Edit the pg_hba.conf file and grant the secondary server access for replication

host replication postgres ip_address_of_secondary_server/22 trust

3. Restart the PostgreSQL database

4. Make sure that wal files are being pushed to the archive directory on the secondary server (if not check the error in postgreql log file and rectify any permission or file transfer related issues)

5. Now take online-backup of the primary database

$ psql -c "SELECT pg_start_backup('label', true)"

$ rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude path_of data_dir_on_primary/* ip_address_of_secondary_server:path_of data_dir_on_secondary

$ psql -c "SELECT pg_stop_backup()"

On the Secondary server:

1. Edit the postgresql.conf file:

hot_standby = on
archive_mode = off

2. Create recovery.conf and add following entries:

# Note that recovery.conf must be in $PGDATA directory.

standby_mode = 'on'

primary_conninfo = 'host=ip_address_of_primary port=5432 user=postgres'

trigger_file = '/path_to/' (this is the file that will be created when we want to make the secondary server as primary)

restore_command = 'rsync /path_to/archive/%f "%p"'

3. Start the standby database


You can calculate the replication lag by comparing the current WAL write location on the primary with the last WAL location received/replayed by the standby. They can be retrieved using pg_current_xlog_location on the primary and the pg_last_xlog_receive_location/pg_last_xlog_replay_location on the standby, respectively.

$ psql -c "SELECT pg_current_xlog_location()" -h ip_address_of_primary (primary host)
(1 row)

$ psql -c "select pg_last_xlog_receive_location()" -h ip_address_of_secondary (standby host)
(1 row)

$ psql -c "select pg_last_xlog_replay_location()" -h ip_address_of_secondary (standby host)
(1 row)

Streaming replication process can also be checked by using ps -ef


$ ps -ef | grep sender
postgres 4950 4925 0 12:32 ? 00:00:00 postgres: wal sender process postgres streaming 0/5000000


$ ps -ef | grep receiver
postgres 5955 5941 1 12:33 ? 00:00:01 postgres: wal receiver process streaming 0/5000000


Fixing slony error (invalid input for txid_snapshot) without rebuilding slony replication

This is probably a slony bug that I will log with the slony community but for now I have figured out a workaround that would keep slony running after the below procedure:

OS: CentOS 6
PostgreSQL version: 9.3.3
Slony version: 2.2.2 (I have seen this issue in earlier versions also since the origin of 2.1.3 or maybe prior)

NOTE: This fix is specifically for the error invalid input for txid_snapshot. It has worked for me numurous times and I thought this might be useful for someone. Please be aware that manually updating slony catalog tables is not recommended and may cause further issues in slony replication. Use the below procedure at your own risk.

The Error:

ERROR:  invalid input for txid_snapshot: "233477269:233478374:233477269,233477846,233477978,233478062,233478210,233478287,233478308,233478314,233478360,233478360,233478371,233478372"

The above error although is not clear as to what the invalid string is in the slony event logs but a little more searching for it through the slony schema suggested that there is a duplication entry for a transaction id in the event log that slony is not happy about. Looking at the above error we see:


The duplicate entry for the transaction id is 233478360,233478360. It looks like it has two entries of the same transaction id in the event log and cannot process the request.
NOTE: we are only looking at the transaction id’s that are listed after the colon entries for the transaction list. For eg the entries below in bold is what we are concerned about.

Procedure to fix the above error:

1. Connect to the master replication node with psql

#psql testdb

2. Search the transaction id in the sl_event table to find the ev_seqno like below

testdb=# select * from _slonyschema.sl_event where ev_snapshot::text like '233477269:233478374:233477269,233477846,233477978,233478062,233478210,233478287,233478308,233478314,233478360,233478360,233478371,233478372';
 ev_origin |  ev_seqno  |         ev_timestamp          |                                             ev_snapshot                                             | ev_type | ev_data1 | ev_data2 | ev_data3 | ev_
data4 | ev_data5 | ev_data6 | ev_data7 | ev_data8 
         2 | 5002155051 | 2014-03-11 16:27:20.155651+00 | 233477269:233478374:233477269,233477846,233477978,233478062,233478210,233478287,233478308,233478314,233478360,233478360,233478371,233478372 | SYNC    |          |          |          |    
(1 row)

NOTE: make sure you have one row listed in there so that you can update the row by removing the duplicate value of the transaction id. In this case it is 233478360,233478360

3. Update the row in sl_event table

testdb=# UPDATE _slonyschema.sl_event set ev_snapshot='233477269:233478374:233477269,233477846,233477978,233478062,233478210,233478287,233478308,233478314,233478360,233478371,233478372' where ev_origin=2 and ev_seqno=5002155051;

NOTE: I updated the sl_event table and took out the duplicate value from ev_snapshot.

4. Restart slony process, restarting slony will clear out the errors as slony retries with the updated information. There could be more duplicates in the sl_event table that the slony log file may show up and you would need to follow the above procedure for all of them. But in most cases slony starts up fine and replication is back online.

Remember this is a temperory solution(be careful while you make the above change) and updating the wrong row might mess up slony but I guess if you have the above error in replication it just means you have to rebuild it anyway. So might as well give the above procedure a try and let me know if it worked for you.

Leave a comment

Slony replication using perl tools

I have been working on Slony for a couple of years now and have started to feel very comfortable with this replication solution. For beginners it might not be so user friendly but as long as you understand the working of this replication module things will start becoming easy. I am going to list out easy ways to setup, configure and maintain this replication module.


I have 2 CentOS boxes that I am going to setup replication on. You can have as many as you want its as simple as adding another node and subscribing the data to it. PostgreSQL version I am using is 8.4.9. Same steps would follow in any other version of postgres. Slony version I have is 2.1.0. It is always a good idea to read the release notes of Slony version to check the compatibility with the postgres server version.

There are two ways to use Slony. Shell or Perl, I prefer perl its easier and reliable option. Although I do not see much information on the web about using perl tools.


I assume you have postgres installed on both the servers and and the Master node has the database setup and ready to be replicated. I also assume that the tables you are going to replicate have primary keys on them as this is a requirement for setting up replication.

1. Download slony 2.1.0 source from the following link:

2. This is a source install and we would need to configure with the current install location in this case it is ( /opt/PostgreSQL/8.4 ) use prefix option for it and then make and make install. Eg:

./configure '--prefix=/opt/slony/slony2.1'

make install

The above step needs to be performed on all nodes (master + slaves)

NOTE: This would be a good time to add a schema only copy of the database to all the slony slaves. If you already have some data in the slave tables it will be deleted when we start the slony subscribe process.

3. Create slony configuration file. This is the file that will list information on all the master and slave nodes and tables and sequences that will be added to replication. Sample copy of the config file is given below for reference.

} else {
$CLUSTER_NAME = 'slony_test';
$LOGDIR = '/postgres/tracelogs/slony_log';
add_node(node => 1,
host => '',
dbname => 'test',
port => 6543,
user => 'postgres',
password => );
add_node(node => 2,
host => '',
dbname => 'test',
port => 6543,
user => 'postgres',
password => );

#(you can add more nodes here by copying the above node)


"set1" => {
"set_id" => 1 ,
"table_id" => 1 ,
"sequence_id" => 1 ,
"pkeyedtables" => [
"keyedtables" => {},
"serialtables" => [],
"sequences" => [

#sequences go in this block for the 1st set
if ($ENV{"SLONYSET"}) {
require $ENV{"SLONYSET"};

4. Initialize the slony cluster using the config file we created above:

./slonik_init_cluster -c slon_test.conf |slonik

5. Now we start slon process as:

./slon_start -c slon_test.conf 1

./slon_start -c slon_test.conf 2

What we are doing above is starting slon processes on node 1 and node 2. A good practice here would be start these processes on the slave box so that they use memory on the slony slave.

6. Then we need to create the set:

./slonik_create_set -c slon_test.conf 1|slonik

7. Last step is to subscribe the set for one node at a time :

./slonik_subscribe_set -c slon_test.conf 1 2|slonik

Make no mistake here or your master node will be empty by the time subscribe set completes.

slon_test.conf 1 2 above me that subscribe set 1 to node 2 if you make a mistake by doing slon_test.conf 2 1 slony will truncate tables on the master node and you will loose data.

8. Now its waiting time. let the initial sync complete. Its a good idea here to look at the log files for any errors and also the sl_status view in the slony schema in your database to check how things are moving along.

Maintaining slony replication:

The easiest part about maintaining slony replication is to remember never ever do a DDL on slony nodes without slonik_execute or taking the table out of replication. Reason is that slony does not replicate DDL statements to nodes it only does DML.

slonik_execute_script is a utility that will let you alter a table without breaking replication

if you have multiple DDL statements just put them in a script and call it using slonik_execute_script

execute_script [options] set# full_path_to_sql_script_file
execute_script [options] -c SCRIPT set#

Executes the contents of a SQL script file on the specified set.
The script only needs to exist on the machine running the slon

set# The set to which this script applies.

-c SCRIPT Pass the SQL to be executed via the command line instead
of as a file.

-n NUM
--node=NUM Override the set origin specified in the configuration

If you need to add a node or drop a node or a table or do much more about managing slony. There are a bunch of scripts available in the slony install directory. I am not going to explain them as they are pretty straight forward. Refer the below document for further details on it and if you need help with slony just leave a comment and I will be sure to write back.

, , ,


%d bloggers like this: