How To Setup PostgreSQL Replication On CentOS

Nowadays, services can’t stop, and is system’s administratos reponsability keep applications, services and servers on line. Let’s make an scenario with 2 virtual machines using virtualbox, both running centos 6.5 64 bits, and postgresql 9.3 for a short tutorial.

You can use this link to install postgres in your machine:

So, now we assume some points:

Master server: is the main server, all connections win connect in this server, read queries and write queries. All write queries will be replicated to slave server, and, after replicated, the slave server advise the main server that all data has been received and updated. Let’s put here the ip: 192.168.1.31.

Slave server: as the name says, this server will be our backup server. If the master server fails, we put this server as the main. Let’s put here the ip: 192.168.33.

So, in the master server, change this parameters in the postgressql.conf:

vim /var/lib/pgsql/9.3/data/postgresql.conf
wal_level = hot_standby

max_wal_senders = 1

synchronous_standby_names = 'postgresql2'

wal_keep_segments = 100

The line max_wal_senders is the number of slave servers, in this case one server.

On the console, type:

su postgres
psql
create user replicador replication;
\du+ (for see if the replicador user was created with the right rule)
exit
/etc/init.d/postgresql-9.3 restart

In the pg_hba.conf:

vim /var/lib/pgsql/9.3/data/pg_hba.conf

Put on the end of file:

host replication replicador 192.168.1.33/32 trust

Save and exit.

Restart postgresql service.

/etc/init.d/postgresql-9.3 restart

Now, on the slave server:

Stop the postgresql server:

service postgresql-9.3 stop
cd /var/lib/pgsql/9.3/data/
rm -rf *
su postgres
pg_basebackup -D /var/lib/pgsql/9.3/data -h 192.168.1.31 -U replicador
ls

As you can see, all data was copied from the main server.

In this directory, make the recovery.conf file with this content:

standby_mode=on
trigger_file='/tmp/promotedb'
primary_conninfo='host=192.168.1.31 port=5432 user=replicador application_name=postgresql2'

We can enable the slave server to accept read queries, on the posgresql.conf file locate the line:

hot_standby = off

and type:

hot_standby = on

And now, run:

/usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data/ start
/etc/init.d/postgresql-9.3 start

Now, all data inserted / deleted on the master server, will be updated to the slave server.

There are some tools to see if the replication is working:

On the linux terminal, type (on master server):

ps aux | grep postgresql1

The output will be something:

[[email protected] ~]# ps aux | grep postgres
postgres 22252  0.0  0.4 228244 16168 ?        S    10:04   0:00 /usr/pgsql-9.3/bin/postmaster -p 5432 -D /var/lib/pgsql/9.3/data
postgres 22254  0.0  0.0 178316  1196 ?        Ss   10:04   0:00 postgres: logger process    
postgres 22256  0.0  0.0 228244  2112 ?        Ss   10:04   0:00 postgres: checkpointer process                                 
postgres 22257  0.0  0.0 228244  1632 ?        Ss   10:04   0:00 postgres: writer process                                       
postgres 22258  0.0  0.0 228244  1388 ?        Ss   10:04   0:00 postgres: wal writer process                                   
postgres 22259  0.0  0.0 229088  2596 ?        Ss   10:04   0:00 postgres: autovacuum launcher process                          
postgres 22260  0.0  0.0 180692  1504 ?        Ss   10:04   0:00 postgres: stats collector process                              
postgres 22705  0.3  0.0 231388  3724 ?        Ss   10:21   0:00 postgres: wal sender process replicador 192.168.1.33(49730) streaming 3/85000208
root     22733  0.0  0.0 103260   828 pts/0    S+   10:22   0:00 grep postgres

The wal sender process is here!

On the linux terminal, type (on slave server):

[[email protected] data]# ps aux | grep postgres
postgres 15909  0.0  0.4 228252 16124 pts/0    S    10:21   0:00 /usr/pgsql-9.3/bin/postgres -D /var/lib/pgsql/9.3/data
postgres 15910  0.0  0.0 178324  1204 ?        Ss   10:21   0:00 postgres: logger process                             
postgres 15911  0.0  0.0 228332  2060 ?        Ss   10:21   0:00 postgres: startup process   recovering 000000010000000300000085
postgres 15912  0.2  0.1 236156  3924 ?        Ss   10:21   0:00 postgres: wal receiver process   streaming 3/85000208
postgres 15913  0.0  0.0 228252  1432 ?        Ss   10:21   0:00 postgres: checkpointer process                       
postgres 15914  0.0  0.0 228252  1628 ?        Ss   10:21   0:00 postgres: writer process                             
root     16032  0.0  0.0 103260   828 pts/0    S+   10:23   0:00 grep postgres

The wal receiver process is here!

On the psql we have an view that is very helpful (on the master server):

psql -U postgres
\x
table_pg_stat_replication;
postgres=# \x
postgres=# table pg_stat_replication ;
-[ RECORD 1 ]----+-----------------------------

pid              | 22705

usesysid         | 278007

usename          | replicador

application_name | postgresql2

client_addr      | 192.168.1.33

client_hostname  | 192.168.1.33

client_port      | 49730

backend_start    | 2014-04-08 10:21:28.28756-03

state            | streaming

sent_location    | 3/85000348

write_location   | 3/85000348

flush_location   | 3/85000348

replay_location  | 3/85000348

sync_priority    | 1

sync_state       | sync

On the slave:

psql -U postgres
select pg_is_in_recovery();

This command returns true (t) or false (f).

And, we have one more command that show the delay replication status on the slave:

psql -U postgres
select now() - pg_last_xact_replay_timestamp() AS replication_delay;
replication_delay

-------------------

00:00:14.937037

(1 row)

This is the time that the slave server was received the last stream from the master. In high concurrence, this time is very little.

That’s it.

About the Author:

This is a guest post, written by Éder Pereira from Brazil. He is a Linux Administrator, and Network Administrator, and too a DBA postgresql / mysql, certified LPI ID LPI000301602. For any queries contact the author: [email protected].

  • Dilip Kumar

    when I configure this synchronous_standby_names = ‘postgresql2’ then database taked lot of time to execute query and error generation it is locally committed but may not replicated on slave server.

  • kshitija joshi

    you have to set standby_name = application name

  • Prasad Dissanayake

    /usr/pgsql-9.3/bin/pg_ctl -D /var/lib/pgsql/9.3/data/ start
    /etc/init.d/postgresql-9.3 start

    after running 1st command i cannot run 2nd command.It’s giving below error.
    * Error: Port conflict: another instance is already running on /var/run/postgresql with port 5432

    how can i resolve this?