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 10.0.0.100 and Standby Server runs on 10.0.0.200 make sure that you name it differently rather than master and slave to avoid confusion.

Performed at Master/Primary Servers runs at 10.0.0.100

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   10.0.0.200      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=10.0.0.100 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 postmaster.pid, and all configuration files).
Here is how,
rsync -av --exclude pg_xlog --exclude postgresql.conf data/* 10.0.0.200:/var/lib/postgresql/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();
pg_current_xlog_location
--------------------------
 0/1C000080
(1 row)
Then on the standby, you can run:
kamal=# SELECT pg_last_xlog_receive_location();
pg_last_xlog_receive_location
--------------------------
 0/1C000080
(1 row)
kamal=# SELECT pg_last_xlog_replay_location();
pg_last_xlog_replay_location
--------------------------
 0/1C000080
(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.