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
My Documents 