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:
[root@postgresql1 ~]# 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):
[root@postgresql2 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: unijic@gmail.com.
