PostgreSQL – Binary / Hot Standby Replication

I followed the PostgreSQL Wiki to set up binary replication. But had a few more steps to take care of in order to easily complete the replication process. Therefore listing it down here for myself and other users:

Assume that you have a master server at 192.168.0.1 and a standby server at 192.168.0.2. Note: If you’re using AWS, you muse use the Private IP’s or Elastic IP’s. Recommend using Elastic IP’s because the Private IP’s may change if the system is rebooted.

Changes to the master
Navigate to the configuration directory (/etc/postgresql/9.1/main) and make the following changes to postgresql.conf

listen_address = '*'
wal_level = hot_standby
max_wal_senders = 3

Edit pg_hba.conf on the master in order to let the standby connect

# IPv4 local connections:
host all         all 192.168.0.2/32 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication all 192.168.0.2/32 trust

Note about auth-method trust: trust allows the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication. See Section 19.3.1 for details.

Note about Masking and CIDR: The /32 followed by the IP address is used to denote the only IP address to allow connections from. A different number like /29 is used to denote a range of IP or CIDR. An IP address is 32 bits total. There are two parts to an IP address. The network side and the host side. A /21 means that 21 of the 32 bits are set. I will use /24 as an example because it’s easier. A /24 is the same as a 255.255.255.0 subnet mask. Each octet in the subnet mask is 8 bits. There are four (4) octets. So 8 x 4 = 32 total bits in an IP address. So if we’re using a /24 (255.255.255.0) subnet mask, and the IP address of your machine is 192.168.1.1, and mine is 192.168.1.2, and your computer wants to send me a packet, it takes your IP address and compares it to the subnet mask. This tells it that any destination IP address in which the first three octets (192.168.1.x) match is therefore on the same network and the packet can be sent directly, instead of having to go through a router.

CIDR stands for “Classless Inter-Domain Routing”. Back in the old days of networking, things were classified into “classes”, as in Class A, Class B, Class C, etc… You will still hear some people refer to a 255.255.255.0 subnet mask as a “Class C”. Picture a chocolate pie…in the old days, you could only slice the pie in specific sizes (ie: Classful). Now with CIDR, it’s considered classless networking – and the pie can be cut in almost any real size….so you can create a network with only 6 IP addresses, 4 usable….CIDR is a really great thing.

Changes to the slave
Navigate to the configuration directory (/etc/postgresql/9.1/main) and make the following changes to postgresql.conf

hot_standby = on

Navigate to the data directory (/var/lib/postgresql/9.1/main) and create a new file recovery.conf with the following content. Make sure that the owner of the file is postgres.

standby_mode = 'on'
primary_conninfo = 'host=192.168.0.1'

Final steps post configuration:

  1. Shutdown the master server’s postgres process
  2. Shutdown the slave server’s postgres process
  3. Execute rsync on master so that the latest changes from master are copied over to slave.
  4. rsync -av --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.1/main/* 192.168.0.2:/var/lib/postgresql/9.1/main/
    

    OR if the servers are only accessible via private key

    rsync -av --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.1/main/* --progress -e 'ssh -i /home/admin/.ssh/pgs-aws.pem' root@192.168.0.2:/var/lib/postgresql/9.1/main/
    
  5. After rsync is complete, start the slave server’s postgres process first
  6. After slave’s postgres process is started, start the master server’s postgres process

If you’re doing this setup on AWS under a restricted Security Group policy, make sure you have the following Inbound rule for the slave server’s IP address:
Type: SSH, Protocol: TCP, Port Range: 5432, Source: 192.168.0.2/32

Validation:
Create db, table on master and insert a few rows. The slave will automatically have the changes.
Try inserting/deleting data to the slave server and it won’t be allowed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s