k-ta-yamada/postgres-ha

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

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

• ssh-copy-id by postgres user
  • pg1 => pg2
  • pg2 => pg1
  • pool1 => pg1
  • pool1 => pg2
  • pool2 => pg1
  • pool2 => pg2
• get database user_name from xxx.yml
  • ./cookbooks/postgresql/create_role.rb
• epel-release need? => don't need.
  • ./roles/db_master.rb
  • ./roles/db_slave.rb
  • ./roles/pool1.rb
  • ./roles/pool2.rb
• database user
  • auth:
    • repl: Is there anything else you need?
    • pgpool: Is SELECT need?
  • enable login by postgres user
    • ALTER ROLE
• recovery_1st_stage.sh
  • archivedir
  • hostname=$(hostname)
  • pg_basebackup
  • rm -rf $archivedir/*
  • recovery.conf
    • primary_conninfo

---

refs:

• Itamae by itamae-kitchen

  • Resources · itamae-kitchen/itamae Wiki
  • Best Practice · itamae-kitchen/itamae Wiki

• Qiita

  • Itamaeチートシート - Qiita
  • Chef脱落者が、Itamaeで快適インフラ生活する話 - Qiita
  • itamae 入門 - Qiita
  • itamae実践Tips - Qiita

postgres-ha's Issues

when itamae is executed on Windows, the line feed code of `failover.sh` becomes `CRLF`

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

• https://github.com/k-ta-yamada/postgres-ha/blob/master/cookbooks/pgpool-II/conf.rb#L15-L19
• https://github.com/k-ta-yamada/postgres-ha/blob/master/cookbooks/postgresql/conf.rb#L33-L50

after remote_file or template run sed command.

pgpool-II version up: 3.6 => 3.7

http://www.pgpool.net/docs/latest/ja/html/release-3-7.html

fail promote

node1: primary
node2: standby

node1 is down

node1: down
node2: primary

pcp_recovery_node node1

node1: standby
node2: primary

node2 is down

node1: standby
node2: down

---

pg_ctl promote is fail in fialover.sh

https://github.com/k-ta-yamada/postgres-ha/blob/master/cookbooks/pgpool-II/files/etc/pgpool-II/failover.sh#L27
anytime $UID is 0

PGDATA directory is not created for db_slave node

PGDATA directory is not created for db_slave node.

It is cookbooks/postgresql/initdb.rb which creates the PGDATA directory, but cookbooks/postgresql/initdb.rb is not executed in roles/db_slave.rb.

hard-coded settings that are not default values

example

• master_slave_mode
• load_balance_mode

cd cookbooks/pgpool-II
diff files/etc/pgpool-II/pgpool.conf templates/etc/pgpool-II/pgpool.conf.erb

pgpool: configured load_balancing on replication mode

• pgpool.conf: replicate_select
• 動作モード：5.3.2. レプリケーションモード
• load_balance_mode

restore_command

pcp_recovery_node fails when host OS is Windows

systemctl start is fail

at initdb

The structure of yaml is bad: `depend_on_hostname`

https://github.com/k-ta-yamada/postgres-ha/blob/master/node/develop.yml#L186-L208

This was a bad solution.

I will change the value of depend_on_hostname to an Array of Hashes rather than Hash and change it to have hostname there.

pgpool.conf: wd_hostname and more are static text

• https://github.com/k-ta-yamada/postgres-ha/blob/master/roles/pool1.rb#L9-L13
• https://github.com/k-ta-yamada/postgres-ha/blob/master/roles/pool2.rb#L9-L13

Change as specified by yaml

vagrant-init

refs:

• https://github.com/yteraoka/postgresql-ha-test/blob/master/repmgr/Vagrantfile
• https://www.vagrantup.com/intro/getting-started/networking.html

linked clone

• https://dev.classmethod.jp/tool/vagrant-linked-clone/
• https://qiita.com/seiyakubo/items/cc211288d5a3c92445e2
• https://www.vagrantup.com/docs/virtualbox/configuration.html

timedatectl set-timezone Asia/Tokyo

add timedatectl set-timezone Asia/Tokyo

ref:

• https://github.com/yteraoka/postgresql-ha-test/blob/master/repmgr/setup.sh#L20-L23
• https://weblabo.oscasierra.net/centos7-timezone-1/

Change to startup with systemd

https://github.com/k-ta-yamada/postgres-ha/blob/master/cookbooks/postgresql/files/var/lib/pgsql/9.6/data/pgpool_remote_start#L8

and cofigure sudoers sudo systemctl start

PostgreSQL: logging setting

relating to # 12

use replication slot or Adjust wal_keep_segments

PostgreSQL本体のレプリケーション機能強化 (3/4)

• postgresql.conf
  • standby node
    • hot_standby = on
    • hot_standby_feedback = on
  • master node
    • wal_level = hot_standbyreplica
    • max_wal_senders = 2
    • max_replication_slots = 2
• create(on master node)
  • SELECT * FROM pg_create_physical_replication_slot('a_slot');
• recovery.conf(on standby node)
  • primary_slot_name = 'a_slot'
• check
  • SELECT slot_name, active FROM pg_replication_slots;
• delete
  • SELECT pg_drop_replication_slot('a_slot');

---

19.6.1. 送出サーバ群

• max_replication_slots (integer)
• wal_keep_segments (integer)

26.2.6. レプリケーションスロット

failover.sh

rpm -q package_name is static

pcp_recovery_node error

pcp_recovery_node -U postgres -h pool -n 1
Password:
ERROR:  executing recovery, execution of command failed at "1st stage"
DETAIL:  command:"recovery_1st_stage.sh"

duplicate pcp.conf entries

cause
https://github.com/k-ta-yamada/postgres-ha/blob/master/cookbooks/pgpool-II/conf.rb#L25-L27

not exits `/var/log/pgpool`

https://github.com/k-ta-yamada/postgres-ha/blob/master/cookbooks/pgpool-II/files/etc/pgpool-II/failover.sh#L21

directory resouce don't need check directory existance

https://github.com/k-ta-yamada/postgres-ha/blob/master/cookbooks/postgresql/directory_archivedir.rb#L7

xxx_dir = /tmp

pgpool-II

• socket_dir
• pcp_socket_dir
• logdir
• wd_ipc_socket_dir

postgresql.conf

• WRITE AHEAD LOG
• ERROR REPORTING AND LOGGING
• others

create $PGDATA directory

directory "#{PGDATA}" do
  owner     'postgres'
  group     'postgres'
  mode      '600'
end

before
https://github.com/k-ta-yamada/postgres-ha/blob/master/cookbooks/postgresql/initdb.rb#L16

execute "rm -rf #{PGDATA}*"