How to create a hot standby with PostgreSQL

yum install postgresql-server

# postgresql-setup initdb
Initializing database ... OK

systemctl enable postgresql

# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

## myconf: replication
host    replication     repuser         10.10.10.101/32  md5

Now that the settings are okay, let’s start up the primary server:

# systemctl start postgresql

And use psql to create the database user that will handle the replication:

# su - postgres
-bash-4.2$ psql
psql (9.2.23)
Type "help" for help.

postgres=# create user repuser replication login encrypted password 'secretPassword' connection limit -1;
CREATE ROLE

Take note of the password you give to the repuser, we’ll need it on the standby side.

Setup the slave

We left the standby with the initdb step. We’ll work as the postgres user, who is superuser in the context of the database. We’ll need an initial copy of the primary database, and we’ll get that with pg_basebackup command. First we wipe the data directory on standby (make a copy beforehand if you wish, but it’s only an empty database):

$ rm -rf /var/lib/pgsql/data/*

Now we are ready to make a consistent copy of the primary to standby:

$ pg_basebackup -h 10.10.10.100 -U repuser -D /var/lib/pgsql/data/
Password: 
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived

---

---

We need to specify the master’s IP address after -h, and the user we created for replication, in this case repuser. As the primary is empty besides this user we created, the pg_basebackup should complete in seconds (depending on network bandwidth). If something goes wrong, check the hba rule on primary, the correctness of the IP address given to the pg_basebackup command, and that port 5432 on primary is reachable from standby (for example, with telnet).

When the backup finishes, you’ll notice that the data directory is populated on the slave, including configuration files (remember, we deleted everything from this directory):

# ls /var/lib/pgsql/data/
backup_label.old  pg_clog        pg_log        pg_serial     pg_subtrans  PG_VERSION       postmaster.opts
base              pg_hba.conf    pg_multixact  pg_snapshots  pg_tblspc    pg_xlog          postmaster.pid
global            pg_ident.conf  pg_notify     pg_stat_tmp   pg_twophase  postgresql.conf  recovery.conf

Now we need to make some adjustments to the standby’s configuration. The IP address enabled for the repuser to connect from need to be the master server’s address in pg_hba.conf:

# tail -n2 /var/lib/pgsql/data/pg_hba.conf
## myconf: replication
host    replication     repuser         10.10.10.100/32         md5

The changes in the postgresql.conf are the same as on the master, as we copied that file with the backup too. This way both systems can take the role of master or standby regarding these configuration files.

In the same directory, we need to create a text file called recovery.conf, and add the following settings:

# cat /var/lib/pgsql/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=10.10.10.100 port=5432 user=repuser password=secretPassword'
trigger_file= '/var/lib/pgsql/trigger_file'

Note that for the primary_conninfo setting we used the IP address of the primary and the password we gave to repuser in the master database. The trigger file could virtually be anywhere readable by the postgres operating system user, with any valid filename – on the event of primary crash the file can be created (with touch for example) which will trigger failover on the standby, meaning the database starts to accept write operations as well.

If this file recovery.conf is present, the server will enter recovery mode on startup. We have everything in place, so we can start up the standby, and see if all works as it should be:

# systemctl start postgresql

It should take a bit more time than usual to get the prompt back. The reason is that the database performs the recovery to a consistent state in the background. You can see the progress in the main logfile of the database (your filename will differ depending on the day of week):

$ tailf /var/lib/pgsql/data/pg_log/postgresql-Thu.log
LOG:  entering standby mode
LOG:  streaming replication successfully connected to primary
LOG:  redo starts at 0/3000020
LOG:  consistent recovery state reached at 0/30000E0
LOG:  database system is ready to accept read only connections

---

---

Verifying the setup

Now that both databases are up and running, let’s test the setup by creating some objects on primary. If all goes well, those objects should eventually appear on standby.

We can create some simple objects on primary (that my look familiar) with psql. We can create the below simple SQL script called sample.sql:

-- create a sequence that will serve as the PK of the employees table
create sequence employees_seq start with 1 increment by 1 no maxvalue minvalue 1 cache 1;
-- create the employees table
create table employees (
        emp_id numeric primary key default nextval('employees_seq'::regclass),
        first_name text not null,
        last_name text not null,
        birth_year numeric not null,
        birth_month numeric not null,
        birth_dayofmonth numeric not null
);
-- insert some data into the table
insert into employees (first_name, last_name, birth_year, birth_month, birth_dayofmonth) values ('Emily','James',1983,03,20);
insert into employees (first_name, last_name, birth_year, birth_month, birth_dayofmonth) values ('John','Smith',1990,08,12);

It is a good practice to keep database structure modifications in scripts (optionally pushed into a code repository) too, for later reference. Pays off when you need to know what you modified, and when. We can now load the script into the database:

$ psql < sample.sql 
CREATE SEQUENCE
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employees_pkey" for table "employees"
CREATE TABLE
INSERT 0 1
INSERT 0 1

And we can query for the table we created, with the two records inserted:

postgres=# select * from employees;

 emp_id | first_name | last_name | birth_year | birth_month | birth_dayofmonth 
--------+------------+-----------+------------+-------------+------------------
      1 | Emily      | James     |       1983 |           3 |               20
      2 | John       | Smith     |       1990 |           8 |               12
(2 rows)

Let’s query the standby for data we expect to be identical to the primary. On standby we can run the above query:

postgres=# select * from employees;
 emp_id | first_name | last_name | birth_year | birth_month | birth_dayofmonth 
--------+------------+-----------+------------+-------------+------------------
      1 | Emily      | James     |       1983 |           3 |               20
      2 | John       | Smith     |       1990 |           8 |               12
(2 rows)

And with that we are finished, we have a running hot standby configuration with one primary and one standby server, synchronizing from master to slave, while read-only queries are allowed at slave.

Conclusion

There are many ways to create replication with PostgreSQL, and there are many tuneables regarding the streaming replication we set up as well to make the configuration more robust, failsave, or even have more members. This tutorial is not applicable to a production system – it is meant to show some general guidelines on what is being involved in such a setup.

Keep in mind that the tool pg_basebackup is only available from PostgreSQL version 9.1+. You may also consider adding valid WAL archiving to the configuration, but for the sake of simplicity, we skipped that in this tutorial to keep things to do minimal while reaching a working synchronizing pair of systems. And finally one more thing to note: standby is not backup. Have a valid backup at all times.