Postgresql-9.6.1 Database Streamline Replication from Master to Slave:
=====================================================
I have two VM's with Centos 7 and Ip's are as follows::
Master : 192.168.1.1
Slave : 192.168.1.2
Create user postgres
Download postgresql-9.6.1.tar.gz and untar it
# ./configure --prefix=/home/postgres/pgsql
# make
# make install
Initialize new database:
# /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data
Add entries for host ips to connect and the replications slave details as follows::
# host all all 192.168.1.0/24 trust
Edit postgresql.conf
Create user postgres
Download postgresql-9.6.1.tar.gz and untar it
# ./configure --prefix=/home/postgres/pgsql
# make
# make install
Initialize new database:
# /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data
Rename the main directory in the slave as follows:
mv /home/postgres/pgsql/data /home/postgres/pgsql/data.backup
pg_basebackup -h 192.168.1.1 -D /home/postgres/pgsql/data -U rep -v -P
Edit pg_hba.conf
Add entries for host ips to connect and the replications slave details as follows::
# host all all 192.168.1.0/24 trust
# host replication rep 192.168.1.2/24 trust
Now create recovery.conf and add the entries as follows in slave
vi /home/postgres/pgsql/data/recovery.conf
=====================================================
I have two VM's with Centos 7 and Ip's are as follows::
Master : 192.168.1.1
Slave : 192.168.1.2
Master Configuration::
Create user postgres
Download postgresql-9.6.1.tar.gz and untar it
# ./configure --prefix=/home/postgres/pgsql
# make
# make install
Initialize new database:
# /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data
# /home/postgres/pgsql/bin/pg_ctl -D /home/postgres/pgsql/data/ -l logfile start
# /home/postgres/pgsql/bin/pg_ctl -D /home/postgres/pgsql/data/ -l logfile stop
Edit pg_hba.conf# /home/postgres/pgsql/bin/pg_ctl -D /home/postgres/pgsql/data/ -l logfile stop
Add entries for host ips to connect and the replications slave details as follows::
# host all all 192.168.1.0/24 trust
Edit postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
checkpoint_segments = 8
archive_mode = on
max_wal_senders = 3
wal_keep_segments = 8
Create replication user on master::
wal_level = hot_standby
checkpoint_segments = 8
archive_mode = on
max_wal_senders = 3
wal_keep_segments = 8
# /home/postgres/pgsql/bin/psql -U
Create replication user on master::
postgres=# CREATE ROLE rep WITH REPLICATION PASSWORD
'********'
LOGIN
Client Configuration ::
Create user postgres
Download postgresql-9.6.1.tar.gz and untar it
# ./configure --prefix=/home/postgres/pgsql
# make
# make install
Initialize new database:
# /home/postgres/pgsql/bin/initdb -D /home/postgres/pgsql/data
Rename the main directory in the slave as follows:
mv /home/postgres/pgsql/data /home/postgres/pgsql/data.backup
pg_basebackup -h 192.168.1.1 -D /home/postgres/pgsql/data -U rep -v -P
Edit pg_hba.conf
Add entries for host ips to connect and the replications slave details as follows::
# host all all 192.168.1.0/24 trust
# host replication rep 192.168.1.2/24 trust
Now create recovery.conf and add the entries as follows in slave
vi /home/postgres/pgsql/data/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=192.168.1.1 port=5432 user=rep password=password'
trigger_file = '/tmp/postgresql.trigger.5432'
or
standby_mode = 'on'
primary_conninfo = 'host=192.168.240.41 port=5432 user=rep'
trigger_file = '/tmp/postgresql.trigger.5432'
and create .pgpass file under the home directory of the postgres user as follows
192.168.240.41:5432:*:rep:123456
Now configure pgadmin on your desktop and create tables on the master database server.
It should be automatically replicated to the slave server.
No comments:
Post a Comment