Friday, March 25, 2016

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 runningcentos 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.

No comments:

Post a Comment