Sunday, July 28, 2013

Streaming Replicaiton in Postgresql

A short information on streaming replication in postgresql 9.2
Requesties
The postgresql servers both master and slave, should be of the same configuration (i.e., the data folder should be in the same location for slave as the master).
Master server changes:
open postgresql.conf master and do the changes
listen_addresses = "*"
wal_level = hot_standby
archive_mode = on
archive_command = "copy %p \\\\192.168.1.1\\wal_archive\\%f"
archive_timeout = 3600
wal_senders = 5
wal_keep_segments = 32

pg_hba.conf
host    all    all    0.0.0.0/0    trust
host    replication   all  0.0.0.0/0   trust

In the slave server, either remove the existing data folder or rename it to data_old, coming back to master issue the following command
pg_basebackup -U postgres -p 5432  -D  "\\192.168.1.1\PostgreSQL\9.2\data"
Windows:
"C:\\Program Files(x86)\PostgreSQL\9.2\bin\pg_basebackup" -U postgres -p 5432 -D "\\192.168.1.1\c$\Program Files(x86)\PostgreSQL\9.2\data"

Use the -w attribute to make your shell script to work uninterrupted for passwords, with the help of pgpass.conf file.

Slave Server Changes
After the files are being copied from  master server, open the postgresql.conf file and comment out all the settings and have only the below change
hot_standby = on

After this prepare a recovery.conf file, open a notepad and add the following code and save it as recover.conf in the data folder of the slave server, where its postgresql.conf file exists
standby_mode = on
primary_conninfo = 'host=192.168.1.0 port = 5432 user = postgres password = sydney11'
trigger_file = 'C:\\trigger_file.trigger'
restore_command = 'copy C:\\wal_archive\\%f %p"

Working
The master will continuously send the wal files to the shared folder of the slave server.
the slave will restore the received wal files using the restore_command from the recovery.conf file
when the master is down create a trigger file, as given in the recovery.conf
the presence of trigger file will alert the slave that the master is down and change the recovery.conf as recovery.done and make it as a master server.

2 comments:

Chandana said...

Thanks Bro. this is the only accurate tutorial I found in whole internet.
Thank you.

Unknown said...

But happans when the old master comes back?