Objective

Our objective is to create a copy of a PostgreSQL database that is constantly synchronizing with the original one and accepts read-only queries.

Operating System and Software Versions

  • Operating system: Red Hat Enterprise Linux 7.5
  • Software: PostgreSQL server 9.2

Requirements

Privileged access to both master and slave systems

Difficulty

MEDIUM

Conventions

  • # - requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command
  • $ - given linux commands to be executed as a regular non-privileged user

Introduction

PostgreSQL is an open source RDBMS (Relational DataBase Management System), and with any databases, the need may arise to scale and provide HA (High Availability). A single system providing a service is always a possible single point of failure - and even with virtual systems, there may be a time when you can't add more resources to a single machine to cope with the ever-increasing load. There also may be a need to another copy of the database contents that can be queried for long-running analytics, that are not fit to be run on the highly transaction-intensive production database. This copy could be a simple restore from the most recent backup on another machine, but the data would be outdated as soon as it is restored.

By creating a copy of the database that is constantly replicating it's contents with the original one (called master or primary), but while doing so accept and return results to read-only queries, we can create a hot standby which have closely the same contents.

In case of failure on master, the standby (or slave) database can take over the role of the primary, stop the synchronization, and accept read and write requests, so operations can proceed, and the failed master can be returned to life (maybe as standby by switching the way of synchronization). When both primary and standby are running, queries that do not attempt to modify database content can be offloaded to the standby, so the overall system will be able to handle greater load. Note however, that there will be some delay - the standby will be behind the master, to the amount of the time it takes to synchronize changes. This delay may wary depending on the setup.

There are many ways to build a master-slave (or even master-master) synchronization with PostgreSQL, but in this tutorial we'll setup streaming replication, using the latest PostgreSQL server available in Red Hat Repositories. The same process generally applies to other distributions and RDMBS versions, but there may be differences regarding filesystem paths, package and service managers and such.


Installing required software

Let's install PostgreSQL with yum to both systems:
yum install postgresql-server
After successful installation, we need to initialize both database clusters:

# postgresql-setup initdb
Initializing database ... OK
To provide automatic startup for the databases on boot, we can enable the service in systemd:
systemctl enable postgresql
We'll use 10.10.10.100 as the primary, and 10.10.10.101 as the standby machine's IP address.

Setup the master

It is generally a good idea to backup any configuration files before we make changes. They don't take up space worth mentioning, and if something goes wrong, the backup of a working configuration file can be a lifesaver.

We need to edit the pg_hba.conf with a text file editor like vi or nano. We need to add a rule that will allow the database user from the standby to access the primary. This is the server side setting, the user does not exist yet within the database. You can find examples at the end of the file commented out that are related to the replication database:

# 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
Let's add another line to the end of the file, and mark it with a comment so it can be easily seen what is changed from the defaults:

## myconf: replication
host    replication     repuser         10.10.10.101/32  md5
On Red Hat flavors, the file is located by default under the /var/lib/pgsql/data/ directory.

We also need to make changes to the database server's main configuration file, postgresql.conf, which is located in the same directory we found the pg_hba.conf.

Find the settings found in the below table, and modify them as follows:



SectionDefault settingModified setting
CONNECTIONS AND AUTHENTICATION#listen_addresses = 'localhost'listen_addresses = '*'
WRITE AHEAD LOG#wal_level = minimalwal_level = 'hot_standby'
WRITE AHEAD LOG#archive_mode = offarchive_mode = on
WRITE AHEAD LOG#archive_command = ''archive_command = 'true'
REPLICATION#max_wal_senders = 0max_wal_senders = 3
REPLICATION#hot_standby = offhot_standby = on


Note that the above settings are commented out by default; you need to uncomment and change their values.

You can grep the modified values for verification. You should get something like the following:

Verifying changes with grep
Verifying changes with grep


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.
ARE YOU LOOKING FOR A LINUX JOB?
Submit your RESUME or create a JOB ALERT on LinuxCareers.com job portal.
DO YOU NEED ADDITIONAL HELP?
Get extra help by visiting our LINUX FORUM or simply use comments below.

You may also be interested in:



Comments and Discussions