debian:debian_postgresql_ha

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

====== PostgreSQL HA ======

===== Master-Replica - Transaction Log Shipping in synchronous mode =====
==== On master ====
<code>
master# aptitude install postgresql postgresql-client
master# sed -i "s/^#listen_addresses = 'localhost'\(.*\)/listen_addresses = '*'\1/" /etc/postgresql/9.6/main/postgresql.conf
master# sed -i 's/^#max_replication_slots = 0\(.*\)/max_replication_slots = 10\1/' /etc/postgresql/9.6/main/postgresql.conf
master# sed -i 's/^#wal_level = minimal\(.*\)/wal_level = replica\1/' /etc/postgresql/9.6/main/postgresql.conf
master# sed -i 's/^#max_wal_senders = 0\(.*\)/max_wal_senders = 10\1/' /etc/postgresql/9.6/main/postgresql.conf
master# echo "host    replication     replicator      10.0.120.36/32          md5" >> /etc/postgresql/9.6/main/pg_hba.conf
master# systemctl restart postgresql.service

master# su - postgres
master$ psql
postgres=# CREATE ROLE replicator LOGIN REPLICATION ENCRYPTED PASSWORD 'replicator123';
postgres=# SELECT * FROM pg_create_physical_replication_slot('replicator');

master# systemctl restart postgresql.service
</code>

==== On replica ====

<code>
replica# aptitude install postgresql postgresql-client
replica# systemctl stop postgresql.service
replica# sed -i "s/^#listen_addresses = 'localhost'\(.*\)/listen_addresses = '*'\1/" /etc/postgresql/9.6/main/postgresql.conf
replica# sed -i 's/^#hot_standby = off\(.*\)/hot_standby = on\1/' /etc/postgresql/9.6/main/postgresql.conf

replica# mv /var/lib/postgresql/9.6/main /var/lib/postgresql/9.6/main.bkp
replica# pg_basebackup -h 10.0.120.37 -U replicator -D /var/lib/postgresql/9.6/main/ -P --password --slot=replicator --xlog-method=stream
replica# chown -R postgres:postgres /var/lib/postgresql/9.6/main
</code>

  * create ''/var/lib/postgresql/9.6/main/recovery.conf''
<code>
standby_mode = 'on'
primary_conninfo = 'host=10.0.120.37 port=5432 user=replicator password=replicator123'
primary_slot_name = 'replicator'
trigger_file = '/var/lib/postgresql/9.6/main/failover.trigger'
</code>

<code>
replica# chown postgres:postgres /var/lib/postgresql/9.6/main/recovery.conf
replica# chmod 0600 /var/lib/postgresql/9.6/main/recovery.conf

replica# systemctl start postgresql.service
</code>

==== Failover =====

<code>
replica# touch /tmp/failover.trigger
replica# chown postgres:postgres /tmp/failover.trigger
replica# mv /tmp/failover.trigger /var/lib/postgresql/9.6/main/failover.trigger
</code>

Trace: • ansible • gitolite • netbsd_network_configuration • ansible_installation • debian_postgresql_ha • ipsec

Search

Navigation

Print/export

Tools

QR Code