Setting Up Streaming Replication With Postgresql

Introduction

In PostgreSQL 9.0 and up it is possible to setup streaming replication (SR). When doing this, it will replicate all databases from one server to an other.

To be able to do this the following requirements have to be met:

• Same achritecture and number of bits;
• Same major/minor? version should be used on both systems. (so 9.1.x with 9.1.y = major);
• Same OS or psql source.

The steps to setup SR on the PostgreSQL wiki ?expect? that the source code is used to setup the PostgreSQL installations. In this post Debian packages of postgresql-9.1 will be used to setup SR .

Installation and configuration

On both the master (192.168.1.1) and the slave (192.168.1.2), postgresql should be installed.

apt-get install postgresql-9.1

postgres@master:~$ createuser --no-createdb --encrypted --no-inherit --login --pwprompt --no-createrole --no-superuser repluser
Enter password for new role: *ReplUser*
Enter it again: *ReplUser*
postgres@master:~$ psql -c 'ALTER ROLE repluser WITH REPLICATION;'
postgres@master:~$

Standard PostgreSQL will only listen on localhost, this is not good enough to do SR, so this has to be changed. Change it on both master and slave to allow the slave can act as master after a failover.

vim /etc/postgresql/9.1/main/postgresql.conf

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32

Because of the listen_addresses, wal_level and max_wal_senders changes postgresql has to be restarted. If you didn’t need to change these values, a restart is not needed.

To allow the created replication user access to the database, /etc/postgresql/9.1/main/pg_hba.conf has to be altered.

# TYPE   DATABASE        USER            ADDRESS                 METHOD
host     replication     repluser        192.168.1.2/32        md5

Copying the database

When this is done, a base copy of the database has to be copied to the slave. To be able to use the default data directory, it needs to be empty before the copy is done.

postgres@slave:~$ rm -rf /var/lib/postgresql/9.1/main/*

postgres@slave:~$ pg_basebackup -D /var/lib/postgresql/9.1/main --host=master --port=5432 --username=repluser --password --verbose
Password:

You might get the following messages:

WARNING:  skipping special file "./server.key"
WARNING:  skipping special file "./server.crt"
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
pg_basebackup: base backup completed

The server.key and server.crt files are files from the server that are not part of the database, but are put there by the Debian installation.

The NOTICE can be solved by …? | The NOTICE is no problem, because the slave will connect to the master and then it will get those WAL segments?.

Before starting the slave, set hot_standby = on in the /etc/postgresql/9.1/main/postgresql.conf . And create a new file called recovery.conf

# Note that recovery.conf must be in $PGDATA
(/var/lib/postgresql/9.1/main/) directory.

# Specifies whether to start the server as a standby. In streaming replication,
# this parameter must to be set to on.
standby_mode          = 'on'

# Specifies a connection string which is used for the standby server to connect
# with the primary.
primary_conninfo      = 'host=192.168.1.1 port=5432 user=repluser password=ReplUser'

# Specifies a trigger file whose presence should cause streaming replication to
# end (i.e., failover).
trigger_file = '/etc/postgresql/9.1/main/stop_SR'

Starting the slave

Then on the slave, start the database

/etc/init.d/postgresql start 9.1

This might give some problems, which are discussed here.

When trying to start the slave and you see the following in the log file:

FATAL:  incorrect checksum in control file

This means that the architecture is not the same or #bits.

When the message is:

FATAL:  could not load server certificate file "server.crt": No such file or directory

This has to do with the configuration and clearing of the database files previously. To fix this, you have to fix the config OR create the symlinks again.

FATAL: database files are incompatible with server

DETAIL: The data directory was initialized by PostgreSQL version 9.1, which is not compatible with this version 9.2.6.

Using the trigger file

When you touch the tigger_file on the standby. The standby will stop the walreceiver process and create a new timeline ID. When all this is done the standby is active with the latest data.

FATAL:  hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 200)

This is selfexplainairy.