Revision as of 16:05, 18 February 2020

Master/Slave Replication

A simple example of master/slave replication on PostgreSQL.
Done on CentOS7

Master configuration

Installation

Install PostgreSQL

~$ sudo yum install -y postgresql-server net-tools vim

Setup the initial database

~$ sudo /usr/bin/postgresql-setup initdb
Initializing database ... OK

Start PostgreSQL

~$ sudo systemctl start postgresql
~$ sudo systemctl enable postgresql

Set postgresql password

~$ sudo su - postgres
bash$ psql
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q

Configuration

Setup firewalld

~$ sudo firewall-cmd --add-service=postgresql --permanent
~$ sudo firewall-cmd --reload

Configure postgresql.conf

 ~$ sudo mkdir -p /var/lib/pgsql/archive && sudo chmod 700 /var/lib/pgsql/archive && sudo chown -R postgres:postgres /var/lib/pgsql/archive
~$ sudo vim /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/pgsql/archive/%f'
max_wal_senders = 2
wal_keep_segments = 10
synchronous_standby_names = 'pgslave01'

Configure pg_hba.conf (add to the end)

~$ sudo vim /var/lib/pgsql/data/pg_hba.conf
#Localhost
host      replication     replica     127.0.0.1/32     md5
#Master
host      replication     replica     10.10.0.40/32     md5
#Slave
host      replication     replica     10.10.0.41/32     md5

Restart postgres and create replication user.

~$ sudo systemctl restart postgresql
~$ sudo su - postgres
bash$ createuser --replication -P replica
Enter password for new role:
Enter it again:

Slave Configuration

Installation

Install PostgreSQL

~$ sudo yum install -y postgresql-server net-tools vim

Configuration

Setup firewalld

~$ sudo firewall-cmd --add-service=postgresql --permanent
~$ sudo firewall-cmd --reload

Stop PostgreSQL

~$ sudo systemctl stop postgresql

Copy data from Master

~$ sudo su - postgres
bash$ pg_basebackup -h 10.10.0.40 -U replica -D /var/lib/pgsql/data -P --xlog

Configure postgresql.conf

~$ sudo vim /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*'
hot_standby = on

Configure recovery.conf

~$ sudo touch /var/lib/pgsql/data/recovery.conf
~$ sudo chmod 600 /var/lib/pgsql/data/recovery.conf && sudo chown postgres:postgres /var/lib/pgsql/data/recovery.conf
~$ sudo vim /var/lib/pgsql/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=10.10.0.40 port=5432 user=replica password=Password application_name=pgslave01'
trigger_file = '/tmp/postgresql.trigger.5432'

Start PostgreSQL

~$ sudo systemctl start postgresql

Testing

netstat

Use netstat to look for the proper listening ports on both the master and slave.

~$ sudo netstat -plntu
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      1684/postgres

db query

From the Master:

~$ sudo su - postgres
bash$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name  |   state   | sync_priority | sync_state
-------------------+-----------+---------------+------------
 pgslavel01 | streaming |             1 | sync
(1 row)
bash$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+------------------------------
pid              | 1810
usesysid         | 16384
usename          | replica
application_name | pgslave01
client_addr      | 10.10.0.41
client_hostname  |
client_port      | 59828
backend_start    | 2020-02-18 12:36:53.638717-05
state            | streaming
sent_location    | 0/30145C8
write_location   | 0/30145C8
flush_location   | 0/30145C8
replay_location  | 0/30145C8
sync_priority    | 1
sync_state       | sync

db data replication

Test data replication from the master to slave.
From the Master:

~$ sudo su - postgres
bash$ psql
postgres=# CREATE TABLE test_table (test varchar(100));
postgres=# INSERT INTO test_table VALUES ('This is VPSCheap.net');

From the Slave:

~$ sudo su - postgres
bash$ psql -c "SELECT * FROM test_table;"
         test
----------------------
 This is VPSCheap.net
(1 row)