Saturday, 17 August 2013

Failover - Streaming Replication

Continuing from last posts about Streaming replication, we gaze ahead performing fail-over between servers. 

If the Master server fails then the standby server should start failover procedures.

If the standby server falls short then no failover need take location. If the standby server can be restarted, even some time subsequent, then the recovery process can also be restarted directly, taking benefit of restartable recovery. If the standby server will not be restarted, then a full new standby server example should be conceived.

If the primary server fails and the standby server becomes the new primary, and then the old prime restarts, you should have a means for informing the vintage prime that it is no longer the prime. This is sometimes renowned as STONITH (Shoot The Other Node In The Head), which is necessary to bypass situations where both schemes think they are the prime, which will lead to disarray and finally facts and figures loss.

So, switching from primary to standby server can be very quick but requires some time to re-prepare the failover cluster. Regular swapping from primary to standby is helpful, since it permits normal downtime on each scheme for maintenance. This furthermore serves as a test of the failover means to ensure that it will actually work when you need it. in writing administration methods are suggested.

To initiate failover of a log-shipping standby server, conceive a initiate file with the filename and path specified by the trigger_file setting in recovery.conf. If trigger_file is not granted, there is no way to exit recovery in the standby and encourage it to a expert. That can be helpful for e.g reporting servers that are only utilised to offload read-only queries from the primary, not for high accessibility purposes.

Create recovery command file in the standby server; the following parameters are needed for streaming replication.

trigger_file = '/path_to/trigger'

Once the trigger file is discovered, the primary server puts itself down and standby server change it's state to master(temp). futhermore, the recovery.conf file will be altered to recovery.done. As happened, we could sense that failover had been performed.

Friday, 1 February 2013

Streaming replication on windows and Linux

In this tutorial, we look forward to setup streaming replication between two PostgreSQL servers. Streaming Replication has the capability to ship and apply the continuously archived xlog files (transaction files) to the standby servers. 

These xlog records shipped are replayed as soon as possible without waiting until xlog file has been filled. The combination of Hot Standby and SR would make the latest data inserted into the primary visible in the standby almost immediately.

Let's see how to setup streaming replication on windows and Linux. All you need here is two PostgreSQL instances running on same/different servers. 

Say, Master/Primary Server runs on and Standby Server runs on make sure that you name it differently rather than master and slave to avoid confusion.

Performed at Master/Primary Servers runs at

Step 1 --> Open postgresql.conf file from your Primary server's data directory.
listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 5
archive_mode = on
archive_command = 
'copy "%p" "mountpoint(or)sharedstorage:\\archivelocation\\%f"' (for windows)
'cp %p /mountpoint(or)sharedstorage/archive/%f (for Linux)
Now, open pg_hba.conf file and register an entry for your Standby server for accepting connection request.
host  replication   repuser      trust

Step 2 --> Make the changes on Standby server.

Edit recovery.conf and postgresql.conf on the standby to start up replication and hot standby.
First, in postgresql.conf, change this line:
hot_standby = on
Then create a file in the standby's data directory (which is often the same directory as postgresql.conf and pg_hba.conf, except on some Linux distributions such as Debian and Ubuntu), called recovery.conf, with the following lines:
restore_command = (provide archived path)
standby_mode = 'on'
primary_conninfo = 'host= port= xxxx user=repuser password=xxxxxxx'

Step 3 --> Start the Master/Primary PostgreSQL server.
Create a replication user as repuser and password.
kamal=# select pg_start_backup('base backup');
Meanwhile, you need to copy all the contents of data directory (excluding, and all configuration files).
Here is how,
rsync -av --exclude pg_xlog --exclude postgresql.conf data/* (for Linux)
xcopy --exclude source destination (for Windows)
kamal=# select pg_stop_backup();

Step 4 --> Start the Standby server.
You will be noticed that your streaming replication between your primary and standby is successful. All your xlog files are being received and replayed at the standby side. 
On the master, you can run the following command to see the current WAL write location:
kamal=# SELECT pg_current_xlog_location();
(1 row)
Then on the standby, you can run:
kamal=# SELECT pg_last_xlog_receive_location();
(1 row)
kamal=# SELECT pg_last_xlog_replay_location();
(1 row)

Here, the three values displayed on screen should match if it does, then we have synced our master and standby servers. If not, there will be some delay in seconds to receive and replay it on standby servers ( check postgresql.conf for such delay specified ).

However, you can check the process for streaming replication using OS commands. Also, check the wal_sender and wal_receiver process.