Infrastructure at your Service

Daniel Westermann

getting started with postgres plus advanced server (3) – setting up a hot standby server

So, we have a ppas 94 database up and running and we have a backup server for backing up and restoring the database. Now it is time to additionally protect the database by setting up a hot standby database. This database could even be used to offload reporting functionality from the primary database as the standby database will be open in read only mode. Again, I’ll use another system for that so that the system overview looks like this:

server ip address purpose
ppas 192.168.56.243 ppas database cluster
ppasbart 192.168.56.245 backup and recovery server
ppasstandby 192.168.56.244 ppas hot standby database

As the standby database will need the ppas binaries just follow the first post for setting this up again. Once the binaries are installed and the database is up and running I’ll completely destroy it but keep the data directory:

[[email protected] tmp]# service ppas-9.4 stop
Stopping Postgres Plus Advanced Server 9.4: 
waiting for server to shut down.... done
server stopped
[[email protected] tmp]# rm -rf /opt/PostgresPlus/9.4AS/data/*
[[email protected] tmp]# 

Ready to go. It is amazingly easy to setup a hot standby server with postgres. In a nutshell, everything that needs to be done is to create a replication user in the database, do a base backup of the primary database, copy that to the standby server, create a recovery.conf file and startup the standby database. Lets start by creating the user which will be used for the recovery in the primary database:

[[email protected] ~]# su - enterprisedb
-bash-4.2$ . ./pgplus_env.sh 
-bash-4.2$ psql
psql.bin (9.4.1.3)
Type "help" for help.

edb=# edb=# create role standby LOGIN REPLICATION UNENCRYPTED PASSWORD 'standby';
CREATE ROLE
edb=# commit;
COMMIT
edb'# 

… and adjust the pg_hba.conf file (the second entry is for the base backup later):

-bash-4.2$ tail -1 data/pg_hba.conf
host    replication     standby         192.168.56.244/24          md5
local   replication     standby                                              md5

… and adjust the wal-level in postgresql.conf

-bash-4.2$ grep wal_level data/postgresql.conf 
wal_level = hot_standby			# minimal, archive, hot_standby, or logical

For the settings in pg_hba.conf and postgresql.conf to take effect either a reload of the main server process or a complete restart is required:

-bash-4.2$ pg_ctl -D data/ restart
waiting for server to shut down..... done
server stopped
server starting

Now it is a good time to test if we can connect to the primary database from the standby node:

[[email protected] tmp]# /opt/PostgresPlus/9.4AS/bin/psql -h 192.168.56.243 -U standby edb
Password for user standby: 
psql.bin (9.4.1.3)
Type "help" for help.

edb=> 

Ready for the basebackup of the primary database?

mkdir /var/tmp/primary_base_backup/
-bash-4.2$ pg_basebackup -D /var/tmp/primary_base_backup/ -U standby -F t -R -x -z -l for_standby -P
Password: 
56517/56517 kB (100%), 1/1 tablespace
-bash-4.2$ 

Especially notice the “-R” switch of pg_basebackup as this creates a minimal recovery.conf for us which we can use as a template for our standby database. Transfer and extract the file written to the standby server (I again prepared passwordless ssh authentication between the primary and the standby server. check the second post on how to do that).

bash-4.2$ pwd
/opt/PostgresPlus/9.4AS/data
bash-4.2$ scp 192.168.56.243:/var/tmp/primary_base_backup/* .
base.tar.gz                                                                                                  100% 5864KB   5.7MB/s   00:00    
-bash-4.2$ 
-bash-4.2$ tar -axf base.tar.gz 
-bash-4.2$ ls
backup_label  dbms_pipe  pg_dynshmem    pg_log        pg_notify    pg_snapshots  pg_subtrans  PG_VERSION            postgresql.conf
base          global     pg_hba.conf    pg_logical    pg_replslot  pg_stat       pg_tblspc    pg_xlog               recovery.conf
base.tar.gz   pg_clog    pg_ident.conf  pg_multixact  pg_serial    pg_stat_tmp   pg_twophase  postgresql.auto.conf
-bash-4.2$ 

Almost ready. Now we need to adjust the recovery.conf file:

standby_mode = 'on'
primary_conninfo = 'host=192.168.56.243 port=5444 user=standby password=standby'
restore_command = 'scp [email protected]:/opt/backup/ppas94/archived_wals/%f %p'

… and enable hot standby mode in the postgresql.conf file on the standby server and adjust the listen address:

-bash-4.2$ grep hot postgresql.conf 
wal_level = hot_standby			# minimal, archive, hot_standby, or logical
hot_standby = on			# "on" allows queries during recovery
#hot_standby_feedback = off		# send info from standby to prevent
-bash-4.2$ grep listen data/postgresql.conf
listen_addresses = '192.168.56.244'		# what IP address(es) to listen on;

Startup the standby database and if everything is fine messages similar to this should be reported in the postgresql log file (/opt/PostgresPlus/9.4AS/data/pg_log/):

2015-04-29 14:03:36 CEST LOG:  entering standby mode
scp: /opt/backup/ppas94/archived_wals/000000010000000000000017: No such file or directory
2015-04-29 14:03:36 CEST LOG:  consistent recovery state reached at 0/17000090
2015-04-29 14:03:36 CEST LOG:  redo starts at 0/17000090
2015-04-29 14:03:36 CEST LOG:  record with zero length at 0/170000C8
2015-04-29 14:03:36 CEST LOG:  database system is ready to accept read only connections
2015-04-29 14:03:36 CEST LOG:  started streaming WAL from primary at 0/17000000 on timeline 1

To further prove the setup lets create a simple table in the primary database and add some rows to it:

edb=# create table standby_test ( a int ); 
CREATE TABLE
edb=# insert into standby_test values (1);
INSERT 0 1
edb=# insert into standby_test values (2);
INSERT 0 1
edb=# commit;
COMMIT
edb=# \! hostname
ppas.local
edb=#

Lets see if we can query the table on the standby:

-bash-4.2$ psql
psql.bin (9.4.1.3)
Type "help" for help.

edb=# select * from standby_test;
 a 
---
 1
 2
(2 rows)

edb=# \! hostname
ppasstandby.local
edb=# 

Cool. Minimal effort for getting a hot standby database up and running. Make yourself familiar with the various settings that influence the behavior of the standby database. I’ll write another post on how to do failovers in near future.

5 Comments

  • […] getting started with postgres plus advanced server (3) – setting up a hot standby server […]

  • dhananjaya says:

    Sir
    Plz expain simple backup and restore method with wal (archive log)

  • dhananjaya says:

    sir im trying to take backup but below command i am getting error

    -bash-4.1$ /usr/edb-bart-1.1/bin/bart SHOW-SERVERS -s ppas93
    SERVER NAME : ppas93
    HOST NAME : 172.16.11.197
    USER NAME : enterprisedb
    PORT : 5444
    REMOTE HOST :
    RETENTION POLICY : none
    DISK UTILIZATION : 0.00 bytes
    NUMBER OF ARCHIVES : 0
    ARCHIVE PATH : /opt/backup/ppas93/archived_wals
    /usr/edb-bart-1.1/bin/bart: symbol lookup error: /usr/edb-bart-1.1/bin/bart: undefined symbol: PQping

  • dhananjaya says:

    -bash-4.1$ pg_basebackup -D /opt/primary_basr_backup/ -U enterprisedb -F t -R -x -z -l for_standby -P
    pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host “[local]”, user “enterprisedb”, SSL off

    -bash-4.1$ pg_basebackup -D /opt/primary_basr_backup/ -U standby -F t -R -x -z -l for_standby -P
    pg_basebackup: could not connect to server: FATAL: no pg_hba.conf entry for replication connection from host “[local]”, user “standby”, SSL off

    sir plz look at this

  • dhananjaya says:

    Sir
    after extract the backup file on standby server i started my standby server but i am getting below error.
    -bash-4.1$ pg_ctl start -D data/ -m fast
    server starting
    -bash-4.1$ edb-postgres cannot access the server configuration file “/opt/PostgresPlus/9.3AS/data/data/postgresql.conf”: No such file or directory

    Sir plz help me…

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure