Scenario

OS :
Debian Jessie (8.x)

Apps :
postgresql-9.5
repmgr-3.3.1

Disk Partition :

/ 30GB
/var/lib/ 1T
/var/log 300-400GB
/boot 512
swap 8GB

Server node :

192.168.34.183 master
192.168.34.184 slave

Add user postgres

# adduser postgres
# echo "export PATH=\$PATH:/opt/pgsql-9.5.3/bin" |tee -a /home/postgres/.bashrc

Setup ssh less password among user postgres on master and slave

$ ssh -keygen -t rsa
$ ssh-copy-id (do on each node to share public key)

Geting source

# wget https://ftp.postgresql.org/pub/source/v9.5.6/postgresql-9.5.6.tar.gz

Install postgresql on master and slave node

install dependency package

# apt-get install gcc zlib1g-dev libreadline-dev make rsync -y

extract source

# tar xzvf postgresql-9.5.6.tar.gz

change dir to postgres

# cd postgresql-9.5.6

run configure and install

# ./configure --prefix=/opt/pgsql-9.5 
# make -j 8 
# make install

create directory for store data and log postgres

# mkdir -p /var/lib/postgresql/data
# mkdir /var/log/postgresql

change owner to postgres user

# chown -R postgres: /var/lib/postgresql
# chown -R postgres: /var/log/postgresql

run initdb to initialize data postgres as user postgres

$ initdb -D /var/lib/postgresql/data

start service postgresql

$ pg_ctl -D /var/lib/postgresql/data -l logfile start

Configuration file Postgresql

Postgresql have two files config are postgresql.conf and pg_hba.conf.
New in postgresql 9.5 and latter checkpoint_segments has replaced with min_wal_size and max_wal_size.

This is my postgresql.conf

postgresql.conf

listen_addresses = '*'					
port = 5432				
max_connections = 500

shared_buffers = 512MB
work_mem = 1048kB
maintenance_work_mem = 128MB
wal_level = hot_standby
wal_buffers = 16
max_wal_size = 4GB
min_wal_size = 2GB
checkpoint_completion_target = 0.9
shared_preload_libraries = 'repmgr_funcs'
archive_mode = on
archive_command = '/bin/true'

max_wal_senders = 10		
wal_keep_segments = 100		
wal_sender_timeout = 60s
max_replication_slots = 3

hot_standby = on

default_statistics_target = 100

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql-%Y-%m-%d.log'
log_truncate_on_rotation = on
log_min_duration_statement = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '
log_lock_waits = on
log_temp_files = 0
lc_messages = 'C'

And my pg_hba.conf

pg_hba.conf

host    all             all             127.0.0.1/32   trust
host    repmgr          repmgr      192.168.34.183/32  trust
host    replication     repmgr      192.168.34.183/32  trust
host    repmgr          repmgr      192.168.34.184/32  trust
host    replication     repmgr      192.168.34.184/32  trust

Install repmgr (replication manager) on master and slave node

repmgr is a suite of open-source tools to manage replication and failover within a cluster of PostgreSQL servers.

Geting source

# wget http://www.repmgr.org/download/repmgr-3.3.1.tar.gz

extract source and install

# tar xzvf repmgr-3.3.1.tar.gz
# cd repmgr-3.3.1
# PATH="/opt/pgsql-9.5/bin:$PATH" (optional)
# make USE_PGXS=1 install

restart service postgresql to activated configuration

$pg_ctl -D /var/lib/postgresql/data -m fast restart

Create the directory for the repmgr configuration files and change owner file and dir to postgres

# mkdir /etc/repmgr
# chown -R postgres: /etc/repmgr

repmgr.conf on master

$ vi /etc/repmgr/repmgr.conf
cluster=db_cluster
node=1
node_name=node1
conninfo='host=node1 user=repmgr dbname=repmgr'
pg_bindir=/opt/pgsql-9.5/bin/
loglevel=DEBUG
logfacility=STDERR
logfile='/var/log/repmgr/repmgr.log'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
priority=100
failover=automatic
promote_command='/opt/pgsql-9.5/bin/repmgr standby promote -f /etc/repmgr/repmgr.conf'
follow_command='/opt/pgsql-9.5/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'
use_replication_slots = 3

repmgr.conf on slave

$ vi /etc/repmgr/repmgr.conf
cluster=db_cluster
node=2
node_name=node2
conninfo='host=node2 user=repmgr dbname=repmgr'
pg_bindir=/opt/pgsql-9.5/bin/
loglevel=DEBUG
logfacility=STDERR
logfile='/var/log/repmgr/repmgr.log'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
priority=100
failover=automatic
promote_command='/opt/pgsql-9.5/bin/repmgr standby promote -f /etc/repmgr/repmgr.conf'
follow_command='/opt/pgsql-9.5/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'
use_replication_slots = 3

On Master node

Create the required users for replication and repmgr and the repmgr DB

postgres=# CREATE USER repmgr SUPERUSER LOGIN ENCRYPTED PASSWORD 'secret';
CREATE ROLE
postgres=# CREATE DATABASE repmgr OWNER repmgr;
CREATE DATABASE

Register node1 as master node for repmgr

run repmgr as user postgres

$ /opt/pgsql-9.5/bin/repmgr -f /etc/repmgr/repmgr.conf master register

On Slave node

Stop service postgresql

$ pg_ctl -D /var/lib/postgresql/data -m fast stop

Remove dir data

$ rm -rfv /var/lib/postgresql/data

run repmgr to clone data master to slave

$ /opt/pgsql-9.5/bin/repmgr -h node1 -U repmgr -d repmgr -D /var/lib/postgresql/data -f /etc/repmgr/repmgr.conf standby clone

start service postgres

$ pg_ctl -D /var/lib/postgresql/data start

run repmgr to register slave node

$ /opt/pgsql-9.5/bin/repmgr -f /etc/repmgr/repmgr.conf standby register

Start repmgr on both server, for permanent i recommend using the option -d, because repmgrd will running on back ground process

/opt/pgsql-9.5/bin/repmgrd -d -f /etc/repmgr/repmgr.conf --verbose > /var/log/repmgr/repmgr.log 2>&1

Testing Replication

Before we will do a test, let’s first look at the status of repmgr

$ /opt/pgsql-9.5/bin/repmgr -f /etc/repmgr/repmgr.conf cluster show

Role      | Name  | Upstream | Connection String
----------+-------|----------|---------------------------------------
* master  | node1 |          | host=debian1 user=repmgr dbname=repmgr
  standby | node2 | node1    | host=debian2 user=repmgr dbname=repmgr

Create the database on the master node

$ /opt/pgsql-9.5/bin/psql -U postgres -c "CREATE DATABASE test_rep"
CREATE DATABASE

list the current databases on the standby

$ psql -U postgres -c "\list"
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 repmgr    | repmgr   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test_rep  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

is to check if the standby is read-only

$ /opt/pgsql-9.5/bin/psql -U postgres -c "CREATE DATABASE test_rep2"
ERROR:  cannot execute CREATE DATABASE in a read-only transaction

Done

CMIIW

Source :
postgresql-rep1
postgresql.com

Advertisements