Replication

Postgres 9.3 Replication on CentOS 6

# configure replication
# on master and slave, backup postgresql.conf
cp /var/lib/pgsql/9.2/data/postgresql.conf /var/lib/pgsql/9.2/data/postgresql.conf.`date +%Y-%m-%d-%H%M`
# edit wal_level configuration with sed
sed -i ‘s/#wal_level = minimal/wal_level = hot_standby/g’ /var/lib/pgsql/9.2/data/postgresql.conf
sed -i ‘s/#max_wal_senders = 0/max_wal_senders = 1/g’ /var/lib/pgsql/9.2/data/postgresql.conf
sed -i ‘s/#wal_keep_segments = 0/wal_keep_segments = 50/g’ /var/lib/pgsql/9.2/data/postgresql.conf
echo “host replication postgres 10.118.98.0/24 trust” >> /var/lib/pgsql/9.2/data/pg_hba.conf

sed -i ‘s/listen_addresses…# we want to change this, just do it manually, uncomment and make the address ‘*’ for all

# chown in case permissions got screwy
chown postgres:postgres /var/lib/pgsql/9.2/data/postgresql.conf
chown postgres:postgres /var/lib/pgsql/9.2/data/pg_hba.conf

# take backup from master to copy to slave
su – postgres
psql -c “SELECT pg_start_backup(‘replbackup’);”
tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/9.2/data
psql -c “SELECT pg_stop_backup();”
scp /tmp/db_file_backup.tar root@10.118.98.171:/tmp/
rm -f /tmp/db_file_backup.tar

# now on slave
# stop postgresql
/etc/init.d/postgresql-9.2 stop
mv /var/lib/pgsql/9.2/data/ /var/lib/pgsql/9.2/data.`date +%Y-%m-%d-%H%M`
# extract backup and remove master’s pid file
tar xvfP /tmp/db_file_backup.tar
rm -f /var/lib/pgsql/9.2/data/postmaster.pid
sed -i ‘s/#hot_standby = off/hot_standby = on/g’ /var/lib/pgsql/9.2/data/postgresql.conf
# edit recovery.conf
sed -i ‘s/#standby_mode = off/standby_mode = on/g’ /var/lib/pgsql/9.2/data/recovery.conf

# sed doesn’t work, manually edit primary_conninfo and put in info below
# sed -i ‘s/#primary_conninfo = ”/primary_conninfo = ‘host=10.118.98.170 port=5432’/g’ /var/lib/pgsql/9.2/data/recovery.conf
vi /var/lib/pgsql/9.2/data/recovery.conf

chown postgres:postgres /var/lib/pgsql/9.2/data/recovery.conf

# start postgres on slave
/etc/init.d/postgresql-9.2 start

# query from master to see if it’s connected
postgres=# select client_addr, state, sent_location, write_location,
flush_location, replay_location from pg_stat_replication;
client_addr | state | sent_location | write_location | flush_location | replay_lo
cation
—————+———–+—————+—————-+—————-+———-
——-
10.118.98.171 | streaming | 2/F80092A0 | 2/F80092A0 | 2/F80092A0 | 2/F80092A
0
(1 row)

(Comments)

Comments