In this tutorial, we gaze forward to setup streaming replication between two PostgreSQL servers. Streaming Replication has the capability to ship and apply the relentlessly archived xlog files (transaction files ) to the standby servers.How Streaming Replication works:
Streaming replication allows a standby server to stay more up-to-date than is possible with file-based log shipping. The standby connects to the Primary server, which creeks WAL records to the standby as they're developed, without waiting for the WAL document to be filled.
Streaming replication is asynchronous in which case there is a little delay between committing a transaction in the primary and the changes evolving evident in the standby.
Write Ahead Logs (WAL):
WAL is the log that keeps pathway of all transactions. To set up replication, PostgreSQL easily makes the logs available for slaves to drag down. One time slaves have the logs, they just need to execute the transactions therein. In the overhead figure it’s described as pg_xlog. These transaction logs are then shipped from Primary to Standby Server over the network .
Be sure to address that the network bandwidth should be higher than the rate of generation of WAL data.
Furthermore, xlog records transported are replayed as shortly as possible without waiting until xlog files has been topped up. The combination of Hot Standby and SR (Streaming Replication) would make the latest data injected into the primary evident in the standby nearly immediately.
Let's glimpse how to setup streaming replication on windows and Linux. Here needed two PostgreSQL instances running on same/different servers.
State, Master/Primary Server sprints on 10.0.0.100 and Standby Server sprints on 10.0.0.200 make sure that you name it distinctly rather than master and slave to bypass 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
'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();
Then on the standby, you can run:
kamal=# SELECT pg_last_xlog_receive_location();
kamal=# SELECT pg_last_xlog_replay_location();
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 hold up in seconds to receive and replay it on standby servers ( check postgresql.conf for such hold up specified )
although, you can check the process for streaming replication using OS instructions. furthermore, check the wal_sender and wal_receiver process.
Failover and Failback scenarios are not part of this tutorial. We will meetup afres