Jan 13, 2014

Create Your Postgres Cluster in 6 Steps

Setting a Postgres cluster (or even a Master-Slave configuration) was not an easy task before version Postgres 9.0.

In the following lines you will find a short guideline that will help you create you Postgres HA solution:

1. Install postgres on both machines
sudo yum -y install postgresql-server
sudo service postgresql initdb
sudo service postgresql start
sudo sudo -u postgres psql

2. Stop the Postgres instance on both machines, delete the /var/lib/postgresql folder at the slave, and recover it by copy the files from the master.

3. Do initial configuration on both machines to enable logging:
sudo mkdir /var/log/postgresql/
sudo chown -R postgres:postgres /var/log/postgresql/
sudo vi /var/lib/pgsql9/data/postgresql.conf
> log_directory = '/var/log/postgresql'

4. Configure the Master and Slave to enable replication
sudo vi /var/lib/pgsql9/data/postgresql.conf @ Master (enable logging and listening)
> listen_addresses = '*'
> wal_level = hot_standby

> max_wal_senders = 3

sudo vi /var/lib/pgsql9/data/pg_hba.conf @ Master (enable access to the server, tune it, if your cluster is not in a secure environment):
> host  replication   all      trust
> host    all             all                  trust

sudo vi /var/lib/pgsql9/data/postgresql.conf @ Slave (turn on standby)
> hot_standby = on

sudo vi /var/lib/pgsql9/data/recovery.conf @ Slave  (turn on standby a new file)
> standby_mode = 'on'

> primary_conninfo = 'host='

5. Finally, start your slave, and then your master on both sides:
sudo service postgresql start

6. And check replication on by creating database at the Master, and check it at the slave
master> sudo sudo -u postgres psql
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
 a         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

slave>  sudo sudo -u postgres psql
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
 a         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

P.S If you are using a CentOS and yum (and not AWS), the following link will be useful for you.

Recovering from Cluster Crash (Thanks for Yariv Rachmani for contribution)
If you find the following errors in the your Postgres error:
> 2015-01-01 19:41:14.419 IST >LOG: started streaming WAL from primary at 0/86000000 on timeline 1
> 2015-01-01 19:41:14.420 IST >FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000086 has already been removed

In this case the quickest procedure to recover the slave:

  1. Stop the service: > service postgresql-9.3 stop
  2. Backup the data folder: > cp -prf /var/lib/pgsql/9.3/data /var/lib/pgsql/9.3/data-org
  3. Remove the data folder: > rm -rf data
  4. Copy the data folder from master: > pg_basebackup -h -D /var/lib/pgsql/9.3/data -U postgres -P -v -x
  5. Change permissions: > chown -Rf postgres:postgres data
  6. Copy the configuration file from the backup:> cp -p data-org/*.conf data/
  7. Start again the slave: service postgresql-9.3 start
  8. Verify the slave log file
Bottom Line
Postgres replication is not so complicated starting with Postgres 9.0. Now, it's your turn to keep your system uptime!

Keep Performing,
Moshe Kaplan


Intense Debate Comments

Ratings and Recommendations