Install Postgresql on CentOS/RHEL/SL with Replication for Esmond

This Wiki topic covers installing Postgresql with replication to support the Esmond DB. You will need two servers (real or virtual) to install and configure postresql on.

Host OS and Requirements

You will need two hosts to install. Use your typical provisioning system to install CentOS/RHEL/SL 6.x/7.x. Sizing of memory and processing power will depend upon the size and activity of the Postgresql DB. I would try 2 processors and 8GB of RAM. I recommend creating allocating a separate disk for the /var/lib/pgsql mount point which is the default install location for Postgresql. Ideally this would be an SSD RAID-1 or RAID-10 location. OS shouldn't require a large amount of disk space (~20GB?) and the /var/lib/pgsql locations should be sized to comfortably host the DB size required with 30% to spare.

<Edit the above info based upon input from Andy Lake>

Source for Postgresql

Repos exist for all current versions of postgresql at http://yum.postgresql.org/repopackages.php I would recommend trying Postgresql 9.4 (current) BUT I have not yet worked with it. You can install the repo using something like this for Postgresql 9.4 on RHEL-6.x

rpm -Uvh http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm

Or here is how to install the right repo for Postgresql 9.3 on SL-7.x:

rpm -Uvh http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-sl93-9.3-1.noarch.rpm

You can survey what is availalbe at the repopackages.php link above to get exactly what you want. Run (on both the master and slave systems):

yum update

To get the repos in place. Once the Repo is ready you can install Postgresql (example for 9.3 but you can modify 93->94) via:

yum install postgresql93-server postgresql93-contrib postgresql93-docs

I leave yum-cron (or yum-autoupdate) on to get new versions automatically. NOTE: This may cause DB interruptions when using replication.

Configuring

Once Postgresql is installed we need to initialize the DB and configure the master and slave systems.

Master Configuration

First lets configure the master server postgresql. We need to do a few things:

• Create the initial DB

On CentOS/RHEL/SL 6.x systems:

            service postgresql-9.3 initdb

On CentOS/RHEL/SL 7 systems:

            /usr/pgsql-9.3/bin/postgresql94-setup initdb

• Start Postgresql and configure it to be on for restart:

On CentOS/RHEL/SL 6.x systems:

       service postgresql-9.3 start
       chkconfig postgresql-9.3 on

On CentOS/RHEL/SL 7 systems:

       systemctl enable postgresql-9.3
       systemctl start postgresql-9.3

• Create a user for replication (note we use REPLICATION privilege instead of making a superuser):

    sudo -u postgres psql -c "CREATE USER repuser REPLICATION LOGIN ENCRYPTED PASSWORD 'thepassword';"

• Now authorize the new user appropriately. In the /var/lib/pgsql/9.3/pg_hba.conf: nano /var/lib/pgsql/9.3/data/pg_hba.conf and append:

    host replication repuser <IP_of_Slave>/32 trust

Save and exit.

• Now let's get the postgresql.conf file setup to use hot-standby streaming replication. nano /var/lib/pgsql/9.3/data/postgresql.conf and append:

# Edit addresses to listen on (substitute the correct IP for the master host)
listen_addresses = 'localhost,<IP_of_Master>' 
# To enable read-only queries on a standby server, wal_level must be set to
# "hot_standby". But you can choose "archive" if you never connect to the
# server in standby mode.
wal_level = hot_standby

# Set the maximum number of concurrent connections from the standby servers.  We only have one slave so:
max_wal_senders = 1

# To prevent the primary server from removing the WAL segments required for
# the standby server before shipping them, set the minimum number of segments
# retained in the pg_xlog directory. At least wal_keep_segments should be
# larger than the number of segments generated between the beginning of
# online-backup and the startup of streaming replication. If you enable WAL
# archiving to an archive directory accessible from the standby, this may
# not be necessary.  Each segment is 16KB
wal_keep_segments = 100

# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'

In the above, you should setup /path_to/archive as the location you want to keep the WAL segments. This should be owned (or at least readable/writeable) by the postgresql user.

• Now you can restart the database

service postgresql-9.3 restart

Slave Configuration

This is very similar to the master system configuration above. The slave must be running the same version of postgresql.

• Make sure postgresql is stopped and configure it to be on for restart:

On CentOS/RHEL/SL 6.x systems:

       service postgresql-9.3 stop
       chkconfig postgresql-9.3 on

On CentOS/RHEL/SL 7 systems:

       systemctl enable postgresql-9.3
       systemctl stop postgresql-9.3

• Now authorize the new user appropriately. In the /var/lib/pgsql/9.3/pg_hba.conf: nano /var/lib/pgsql/9.3/data/pg_hba.conf and append:

    host replication repuser <IP_of_Master>/32 trust

Save and exit.

• Now let's get the postgresql.conf file setup to use hot-standby streaming replication. nano /var/lib/pgsql/9.3/data/postgresql.conf and append:

# Edit addresses to listen on (substitute the correct IP for the slave host)
listen_addresses = 'localhost,<IP_of_Slave>' 
# To enable read-only queries on a standby server, wal_level must be set to
# "hot_standby". But you can choose "archive" if you never connect to the
# server in standby mode.
wal_level = hot_standby

# Set the maximum number of concurrent connections from the standby servers.  We only have one slave so:
max_wal_senders = 1

# To prevent the primary server from removing the WAL segments required for
# the standby server before shipping them, set the minimum number of segments
# retained in the pg_xlog directory. At least wal_keep_segments should be
# larger than the number of segments generated between the beginning of
# online-backup and the startup of streaming replication. If you enable WAL
# archiving to an archive directory accessible from the standby, this may
# not be necessary.  Each segment is 16KB
wal_keep_segments = 100

# Enable WAL archiving on the primary to an archive directory accessible from
# the standby. If wal_keep_segments is a high enough number to retain the WAL
# segments required for the standby server, this is not necessary.
archive_mode    = on
archive_command = 'cp %p /path_to/archive/%f'
#  Turn on hot_standby
hot_standby = on

In the above, you should setup /path_to/archive as the location you want to keep the WAL segments. This should be owned (or at least readable/writeable) by the postgresql user. While acting in slave mode it is not needed (only if promoted to master)

• Last we need to setup a recovery.conf file to control failover for the slave host. Edit /var/lib/pgsql/9.3/data/recovery.conf on the slave:

standby_mode = 'on'
primary_conninfo = 'host=<IP_of_Master> port=5432 user=repuser password=thepassword'
restore_command = 'cp /path_to/archive/%f %p'
trigger_file = '/var/lib/pgsql/9.3/data/stop_recovery_slave'
archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /path_to/archive %r'

Note that you must make sure the thepassword is the one you setup when creating repuser and the /path_to/archive matches what you choose for the location of the WAL files above. Make sure the file is owned by postgres and protected:

chown postgres.postgres /var/lib/pgsql/9.3/data/recovery.conf

chmod 600 /var/lib/pgsql/9.3/data/recovery.conf

Setup a password for the postgres user

On both the master and slave systems you should setup a password for the postgres user by running:

sudo passwd postgres

The next section assumes this has been done.

Setup SSH-keys for root and postgresql

To make it easier to manage your postgresql master/slave systems, you should setup ssh-keys on each. The following assumes you know the password for the root and postgres users on your slave system. Login as root and postgres on the master host and run the following commands:

ssh-keygen

Just hit <ENTER> for each prompt

ssh-copy-id <IP_of_Slave>

When prompted use the right password

Ensure firewall allows access between the master and slave

Next, adjust iptables to access postgresql from remote systems.

On CentOS 6.x systems:

nano /etc/sysconfig/iptables

Add the following line on the slave:

-A INPUT -s <IP_of_Master> -m tcp -p tcp --dport 5432 -j ACCEPT

Or on the master:

-A INPUT -s <IP_of_Slave> -m tcp -p tcp --dport 5432 -j ACCEPT

Save and exit the file. Restart iptables service.

service iptables restart

On CentOS 7 systems:

(NOTE: Not sure how to restrict IP...needs updating for master*/*slave)

firewall-cmd --permanent --add-port=5432/tcp

firewall-cmd --reload

Adjust SELinux

Run the following command to make PostgreSQL work if SELinux enabled on your system.

setsebool -P httpd_can_network_connect_db 1

You may not login to PostegreSQL if you didn\x92t run the above command.

Initial Replica of Databse

You now need to "seed" the database on the slave from the master. I use the script below to do this. A suitably edited version of it should be run on the master and assumes passwordless ssh-keys are setup for the root account.

#!/bin/bash
#
#  This will "reseed" the hot_standby streaming postgresql setup on the SLAVE
#  from the MASTER.  Edit the ##VARIABLES## below.  For example replace
#  ##SLAVE## with the hostname of the SLAVE server
#
#  Shawn McKee <smckee@umich.edu>
#  May 5, 2011
# Updated for 9.3 March 5, 2015
################################################

# First make sure postgresql-9.3 is stopped on the SLAVE
ssh root@##SLAVE## "/sbin/service postgresql-9.3 stop"
if [ "$?" -ne "0" ]; then
    echo " ERROR: Failed to stop postgresql-9.3 on SLAVE: return $?"
else
# Get copy of *.conf files
    echo " Copying existing .conf files..."
    ssh root@##SLAVE## "cp -av /var/lib/pgsql/9.3/data/*.conf /root/"

    echo " Postgresql-9.3 stopped on SLAVE"
#  Checkpoint primary server
psql -U postgres -c "SELECT pg_start_backup('Hot_standby_to_SLAVE', true)"
#  Beginning rsync
echo " Doing rsync to SLAVE..."
rsync -arv --exclude=*.conf --exclude=postmaster.pid /var/lib/pgsql/9.3/data/ ##SLAVE##:/var/lib/pgsql/9.3/data/
# End backup
echo " Stopping pg backup..."
psql -U postgres -c "SELECT pg_stop_backup()"
echo " Make sure postgres owns the files on SLAVE..."
ssh root@##SLAVE## "chown -R postgres.postgres /var/lib/pgsql/9.3/data"
echo " Restarting postgresql on SLAVE..."
ssh root@##SLAVE## "/sbin/service postgresql-9.3 start"
if [ "$?" -ne 0 ]; then
    echo " ERROR: Restarting postgresql on SLAVE. May need to restore /root/*.conf files?  $?"
fi
fi
echo " Done!"
exit

Alternately you can run a command like this:

sudo -u postgres pg_basebackup -h master.host -D /usr/local/pgsql/data -U repuser -v -P

Note this replaces the entire pgsql data directory with the one from the master. You will need to adjust the configuration files to be suitable for the slave if you run this way.

At this point the slave should be tracking the master. The next section gives some information on checking the replication status.

Verifying Replication

You can check that status of the replication in the following ways.

On the master do the following:

pgsql -U postgres

postgres=# \x
Expanded display is on.
postgres=# table pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 3082333
usesysid         | 16385
usename          | repuser
application_name | walreceiver
client_addr      | 10.10.4.205
client_hostname  |
client_port      | 53332
backend_start    | 2015-06-16 14:12:09.019447-04
state            | streaming
sent_location    | 21C/A2BA7230
write_location   | 21C/A2BA7230
flush_location   | 21C/A2BA7230
replay_location  | 21C/A2BA64C8
sync_priority    | 0
sync_state       | async

Or on the slave try:

psql -U postgres

postgres=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

The above command returns 't' (for "true") or 'f' (for "false"). You want to see 't'.

postgres=# select now() - pg_last_xact_replay_timestamp() AS replication_delay;
 replication_delay
-------------------
 00:00:00.025955
(1 row)

The above shows the "delay" in getting the updates from the master.

Some URLs for further reference

See the following

• http://www.unixmen.com/setup-postgresql-replication-centos/
• https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
• https://wiki.postgresql.org/wiki/Streaming_Replication

-- ShawnMcKee - 16 Jun 2015