/postgres-ha

itamae: PostgreSQL High availability configuration by pgpool-II with watchdog.

Primary LanguageHTML

PostgreSQL HA

PostgreSQL High availability configuration by pgpool-II with watchdog.

NOTE: This repository is for self-study.

Usage

Server setup

# vagrant up
vagrant plugin install vagrant-vbguest
vagrant plugin install vagrant-proxyconf # if you needed
vagrant up
vagrant reload # to reflect SELINUX setting change, reload.

Using itamae

# install itamae
# NOTE: in the following command example omit "bundle exec"
bundle install --path vendor/bundle

# Primary node `pg1`: PostgreSQL and pgpool-II
itamae ssh -h pg1 -y node/develop.yml roles/db_master.rb
itamae ssh -h pg1 -y node/develop.yml roles/pgpool.rb

# Standby node `pg2`: PostgreSQL and pgpool-II
itamae ssh -h pg2 -y node/develop.yml roles/db_slave.rb
itamae ssh -h pg2 -y node/develop.yml roles/pgpool.rb

Remaining 3 Task

1. ssh postgres@pg[1|2] for pcp_recovery_node

Set up so that ssh connection without passphrase can be connected with postgres user from both servers.

Note: host names used for the connection are backend-pg1 and backend-pg2.
because Streaming Replication and pg_basebackup use the backend network.
ref: ./cookbooks/postgresql/templates/var/lib/pgsql/9.6/data/recovery_1st_stage.sh.erb#L18
ref: ./cookbooks/pgpool-II/templates/etc/pgpool-II/pgpool.conf.erb#L65
please check common.backend_prefix and common.hostnames of node/xxx.yml for the actual host name.

  1. generate key: ssh-keygen on both servers.
  2. copy the contents of the public key to ~/.ssh/authorized_keys of the other server
  3. and copy the contents of the public key to ~/.ssh/authorized_keys of the self
    • cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
  4. connect by postgres user
    • primary node: pg1
      1. su - postgres
      2. ssh backend-pg1
      3. ssh backend-pg2
    • standby node: pg2
      1. su - postgres
      2. ssh backend-pg1
      3. ssh backend-pg2

2. Start up primary node and standby node(only pgpool)

NOTE: Start up PostgreSQL with pg_ctl instead of systemctl.

primary node

ssh pg1
systemctl start postgresql-9.6.service
systemctl start pgpool.service

standby node

ssh pg2
systemctl start pgpool.service

and check node status

pcp_watchdog_info -h pool -U pgpool -v
Password:
Watchdog Cluster Information
Total Nodes          : 2
Remote Nodes         : 1
Quorum state         : QUORUM EXIST
Alive Remote Nodes   : 1
VIP up on local node : YES
Master Node Name     : backend-pool1:9999 Linux pg1
Master Host Name     : backend-pool1

Watchdog Node Information
Node Name      : backend-pool1:9999 Linux pg1
Host Name      : backend-pool1
Delegate IP    : 192.168.1.200
Pgpool port    : 9999
Watchdog port  : 9000
Node priority  : 1
Status         : 4
Status Name    : MASTER

Node Name      : backend-pool2:9999 Linux pg2
Host Name      : backend-pool2
Delegate IP    : 192.168.1.200
Pgpool port    : 9999
Watchdog port  : 9000
Node priority  : 1
Status         : 7
Status Name    : STANDBY
pcp_node_info -h pool -U pgpool -v 0
Password:
Hostname   : backend-pg1
Port       : 5432
Status     : 1
Weight     : 0.500000
Status Name: waiting
pcp_node_info -h pool -U pgpool -v 1
Password:
Hostname   : backend-pg2
Port       : 5432
Status     : 3
Weight     : 0.500000
Status Name: down

3. Start up standby node

for start streaming replication.

pcp_recovery_node -h pool -U pgpool -n 1

Troubleshoot

If the execution of pcp_recovery_node fails, if the host OS is windows,
check recovery_1st_stage.sh.erb or recovery_1st_stage.sh on the server.

In the case of windows

file cookbooks\postgresql\templates\var\lib\pgsql\9.6\data\recovery_1st_stage.sh.erb

On the server

file /var/lib/pgsql/9.6/data/recovery_1st_stage.sh

change CRLF to LF sample

sed -i "s/\r//g" /var/lib/pgsql/9.6/data/recovery_1st_stage.sh

todo

refs: