Tips and Tricks for Administrators: How to Implement PostgreSQL Cluster on Linux

PostgreSQL Installer Download

http://www.enterprisedb.com/products-services-training/pgdownload

Reference Document link

Action Plan

Install Postgres in both the Server
Configure Password less ssh on both the server for Postgres user
Modify Primary Side configuration
Take Primary side Backup from Standby side
Configure Standby Side

Environment Details

Operating System Version : Redhat Linux 6.4 (64 Bit)
PostgreSQL Version : 9.3.5
RAM : 4 GB
HDD : 120 GB.

Configuration Details

Server 1 (Primary)->

IP Address : 192.168.0.21
Hostname : postgres1.spectrum-bd.com

Server 2 (Standby)->

IP Address : 192.168.0.22
Hostname : postgres2.spectrum-bd.com

Postgres Installation Step

Install Postgres software on both the server's using below command

[root@postgres1 u01]# ./postgresql-9.3.5-3-linux-x64.run

Installation Directory [/opt/PostgreSQL/9.3]: 

Data Directory [/opt/PostgreSQL/9.3/data]:

Please provide a password for the database superuser (postgres). A locked Unix
user account (postgres) will be created if not present.

Password :
Retype password :

Port [5432]:

Please choose an option [1] :

Setup is now ready to begin installing PostgreSQL on your computer.

Do you want to continue? [Y/n]:

Please wait while Setup installs PostgreSQL on your computer.

 Installing
 0% ______________ 50% ______________ 100%
 #########################################
Setup has finished installing PostgreSQL on your computer.

By default Postgres user will be created with the installation. Change the password for the postgres user using below command

[root@postgres1 ~]# passwd postgres

Changing password for user postgres.
New password:
BAD PASSWORD: it is based on a dictionary word
Retype new password:

passwd: all authentication tokens updated successfully.

Primary Site Configuration

Setting up Streaming Replication with WAL Archiving

PostgreSQL tracks changes made to the database data files via a log called the write ahead log (WAL). The log exists mainly in case there is a system crash, so the database can be restored to a correct state by “replaying” the log entries made since the last checkpoint.

This method of relaying changes gives PostgreSQL users yet another option for backing up a database, in that one can combine the regular file system level backups with the backup of of the WAL files.

To enable the archiving of the WAL files onto another machines, whether it’s a slave database, an NFS mount or other media, PostgreSQL does not make any assumptions on how it is done and gives the flexibility for the administrator to decide.

Here we will be shipping the logs to the Standby machine. On the Primary server we will have to activate the archive mode. In conjunction with this method, we will also set up PostgreSQL's internal streaming replication service. Within master’s postgresql.conf file, we can begin to change some of the defaults:

[root@postgres1 ~]# su - postgres

[postgres@postgres1 u01]# cd /opt/PostgreSQL/9.3/data

[postgres@postgres1 data]$ vi postgresql.conf

listen_addresses = '*'
port = 5432 
max_connections = 100 
wal_level = hot_standby
checkpoint_segments = 8 
archive_mode = on
archive_command = '/opt/PostgreSQL/9.3/data/ship_logs.sh %p %f  < /dev/null'
max_wal_senders = 3
wal_keep_segments = 8 
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s

Create ship_logs.sh file required for archive backup and modify the parameters accordingly

[postgres@postgres1 data]$ vi ship_logs.sh

#!/bin/sh
#
## This is called to back up the WAL files to the slave.
## This is on top of replication and is used as another
## method to secure data successfully transferring from one
## database server to another.

ARCHIVE_DIR_ON_SLAVE="/opt/PostgreSQL/9.3/walfiles"
LOG=1
LOG_FILE="/tmp/postgres_wal_archiving.log"

log() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; }
log_error() { echo "`date --rfc-3339=ns` $1" >> "$LOG_FILE"; exit 1; }

wal_path="$1"
wal_file="$2"
backup_server="192.168.0.22"

if [ $LOG -eq 1 ]
then
   log "Transfering file to backup server, filename: $wal_file"
fi

rsync -e "ssh -p 22" "$wal_path" "$backup_server:$ARCHIVE_DIR_ON_SLAVE"

if [ $LOG -eq 1 ]
then
   if [ "$?" -eq 0 ]; then
     log "Transfer to slave server completed"
   else
     log_error "Sending $wal_file failed."
   fi
fi

Once the script has been saved on the primary, from the shell, make it executable

[postgres@postgres1 data]$ chmod +x /opt/PostgreSQL/9.3/data/ship_logs.sh

Modify pg_hba.conf file and add below parameter value,

[postgres@postgres1 data]$ vi pg_hba.conf
local   all             all                          trust
host    all             all       192.168.0.0/24     trust
host    all             all       ::1/128            trust
host    replication     postgres  192.168.0.22/24    trust

Create a directory for archive backup using below command

[postgres@postgres1 ~]$ mkdir /opt/PostgreSQL/9.3/walfiles
[postgres@postgres1 ~]$ chmod 700 /opt/PostgreSQL/9.3/walfiles

After a restart of the PostgreSQL engine on the Primary server, PostgreSQL will start the continuous archiving of logs and moving them to the archive directory /opt/PostgreSQL/9.3/walfiles over on the Standby server. This will occur when the logs reach the check point size which is usually at 16MB (this setting can be tweaked on postgresql.conf on master but the defaults should be fine). At this point, PostgreSQL on Primary can be restarted.

Upon the restart, if you kept the LOG=1 variable on in the ship_logs.sh script, you should see this in /tmp/postgres_wal_archiving.log, and confirm the files are over on the Standby:

2015-02-11 13:59:31.376895207+06:00 Transfering file to backup server, filename: 00000001000000000000002B
2015-02-11 13:59:31.705610928+06:00 Transfer to slave server completed
2015-02-16 20:20:10.562084442+06:00 Transfering file to backup server, filename: 00000001000000000000002C
2015-02-16 20:20:10.836316969+06:00 Transfer to slave server completed
2015-02-23 18:20:17.241729500+06:00 Transfering file to backup server, filename: 00000001000000000000002D
2015-02-23 18:20:17.540416879+06:00 Transfer to slave server completed

Once you see that, it's probably okay to change the LOG value from a 1 to a 0.

Standby Side Configuration

Create a directory both in Primary and Standby for archive backup using below command

[postgres@postgres2 ~]$ mkdir /opt/PostgreSQL/9.3/walfiles
[postgres@postgres2 ~]$ chmod 700 /opt/PostgreSQL/9.3/walfiles

Prior to finishing the setup on the Standby, we need to copy the data directory from Primary to catch the Standby up, and then start up replication. We will need pg_basebackup to help us copy the data directory from Primary.

$ sudo service postgresql stop

# This will remove the data directory on the slave - careful!

$ rm -rf /opt/PostgreSQL/9.3/data
$ mkdir /opt/PostgreSQL/9.3/data
$ chown postgres.postgres /opt/PostgreSQL/9.3/data
$ chmod 700 /opt/PostgreSQL/9.3/data

Use the below command to backup primary database backup from standby side

[postgres@postgres2 data]$ ./pg_basebackup -x -R -P -D /opt/PostgreSQL/9.3/data -h 192.168.0.21

PostgreSQL will show the status of the base backup from master as it is going. When completed, you should see the output as follows:

36924/36924 kB (100%), 1/1 tablespace
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

pg_basebackup: base backup completed

One last step is to create a recovery.conf file which will essentially turn on replication on the standby. This file, recovery.conf, should be within /opt/PostgreSQL/9.3/data on the standby

[postgres@postgres2 data]$ vi recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=postgres password=postgres host=192.168.0.21 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'
#archive_cleanup_command = '/opt/PostgreSQL/9.3/bin/pg_archivecleanup /opt/PostgreSQL/9.3//walfiles %r'
restore_command='cp /opt/PostgreSQL/9.3/walfiles/%f "%p"'
recovery_target_timeline='latest'

Ensure standby database postgresql.conf file has the below parameter values

[postgres@postgres2 data]$ vi postgresql.conf

listen_addresses = '*'
port = 5432 
max_connections = 100 
wal_level = hot_standby
checkpoint_segments = 8 
archive_mode = on
archive_command = '/opt/PostgreSQL/9.3/data/ship_logs.sh %p %f  < /dev/null'
max_wal_senders = 3
wal_keep_segments = 8 
hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s

Ensure standby database pg_hba.conf file has the below property

[postgres@postgres2 data]$ vi pg_hba.conf

local   all             all                          trust
host    all             all       192.168.0.0/24     trust
host    all             all       ::1/128            trust
host    replication     postgres  192.168.0.21/24    trust

Cluster Verification

PostgreSQL can now be started on the Standby. If all goes well, you should see this in the logfile:

2014-07-02 14:01:13 CDT   LOG:  consistent recovery state reached at 0/A041870
2014-07-02 14:01:13 CDT   LOG:  redo starts at 0/A041870
2014-07-02 14:01:13 CDT   LOG:  record with zero length at 0/A041910
2014-07-02 14:01:13 CDT   LOG:  database system is ready to accept read only connections
2014-07-02 14:01:13 CDT   LOG:  started streaming WAL from primary at 0/A000000 on timeline 1

If a table is created on the Standby machine, PostgreSQL will automatically complain, since the Standby are read only:

# create table test_this_replication (some_col text);
ERROR:  cannot execute CREATE TABLE in a read-only transaction

At this Point we can Say PostgreSQL Cluster configuration Completed Successfully.

Some Useful command

Use the below command to Start / Stop / Restart Postgres Service

[root@postgres1 u01]# cd /etc/init.d 

[root@postgres1 init.d]# service postgresql-9.3 stop 

[root@postgres1 init.d]# service postgresql-9.3 start 

[root@postgres1 init.d]# service postgresql-9.3 restart

Log File Location

cd /opt/PostgreSQL/9.3/data/pg_log

Database Shutdown / Startup

Shutdown ->

su - postgres
cd /opt/PostgreSQL/9.3/bin
./pg_ctl stop -D /opt/PostgreSQL/9.3/data/ -m fast

Startup ->

su - postgres
cd /opt/PostgreSQL/9.3/bin
./pg_ctl start -D /opt/PostgreSQL/9.3/data/

Tips and Tricks for Administrators

Wednesday, March 25, 2015

How to Implement PostgreSQL Cluster on Linux