Project

General

Profile

Actions

Task #11846

closed

PostgreSQL data/conns handling on IPv6-only

Added by Philipp (double-p) Buehler 12 months ago. Updated 6 months ago.

Status:
Closed
Priority:
Normal
Start date:
07/07/2023
Due date:
% Done:

0%

Estimated time:
42.00 h
PM Check date:

Description

Demonstrate a multi-node setup with
- Streaming replication
- WAL replication (repmgr + barman)
- remote backup (barman)
- VIP failover (keepalived || ip6.sh || v6-magic)
- PGpoolII
- pgbouncer

... and restore things (no v6 specials expected, hah).

Actions #1

Updated by Philipp (double-p) Buehler 12 months ago

key for provisioning `ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAINfdGn4eneqqp0AOXZqkQJzeLLy//FiGqYmWimuqIH6h `

Actions #2

Updated by Philipp (double-p) Buehler 12 months ago

need; 4 VMs. each:
1 vCPU, 4 GB RAM, 10 GB Disk. All v6-only in one prefix preferably?

Actions #3

Updated by Nico Schottelius 12 months ago

[16:57] nb3:~% for v in 2a0a:e5c0:2:12:0:f0ff:fea9:c3d9 2a0a:e5c0:2:12:0:f0ff:fea9:c3e4 2a0a:e5c0:2:12:0:f0ff:fea9:c3ea 2a0a:e5c0:2:12:0:f0ff:fea9:c3ee; do ssh root@$v "echo ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAINfdGn4eneqqp0AOXZqkQJzeLLy//FiGqYmWimuqIH6h pbuehler@adm042.local >> .ssh/authorized_keys" ; done                                                          
Warning: Permanently added '2a0a:e5c0:2:12:0:f0ff:fea9:c3d9' (ED25519) to the list of known hosts.
Warning: Permanently added '2a0a:e5c0:2:12:0:f0ff:fea9:c3e4' (ED25519) to the list of known hosts.
Warning: Permanently added '2a0a:e5c0:2:12:0:f0ff:fea9:c3ea' (ED25519) to the list of known hosts.
Warning: Permanently added '2a0a:e5c0:2:12:0:f0ff:fea9:c3ee' (ED25519) to the list of known hosts.
[16:59] nb3:~% 
Actions #4

Updated by Philipp (double-p) Buehler 12 months ago

Debian 11/bullenauge

Actions #5

Updated by Philipp (double-p) Buehler 12 months ago

Nico Schottelius wrote in #note-3:

[...]

i think apt upgrade killed these three
Warning: Permanently added '2a0a:e5c0:2:12:0:f0ff:fea9:c3e4' (ED25519) to the list of known hosts.
Warning: Permanently added '2a0a:e5c0:2:12:0:f0ff:fea9:c3ea' (ED25519) to the list of known hosts.
Warning: Permanently added '2a0a:e5c0:2:12:0:f0ff:fea9:c3ee' (ED25519) to the list of known hosts.
since I tried to update grub only on vda1. could you respawn these - nothing done on them besides 'apt upgrade'.

Actions #6

Updated by Nico Schottelius 12 months ago

for v in 2a0a:e5c0:2:12:0:f0ff:fea9:c3e4 2a0a:e5c0:2:12:0:f0ff:fea9:c3ea 2a0a:e5c0:2:12:0:f0ff:fea9:c3ee ; do ssh root@$v "echo ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAINfdGn4eneqqp0AOXZqkQJzeLLy//FiGqYmWimuqIH6h pbuehler@adm042.local >> .ssh/authorized_keys" ; done
Actions #8

Updated by Philipp (double-p) Buehler 12 months ago

sidenote: pgLoader does not support IPv6 in the connection strings (pgpass). so data has to be copied via scp/rsync/.. and then loaded locally (unix socket)

Actions #9

Updated by Philipp (double-p) Buehler 12 months ago

postgres@pg1:~$ repmgr cluster show
ID | Name | Role | Status | Upstream | Location | Priority | Timeline | Connection string
----+------+---------+-----------+----------+----------+----------+----------+-----------------------------------------------
1 | pg1 | primary | * running | | default | 100 | 1 | host=pg1 user=repmgr dbname=repmgr port=5432
2 | pg2 | standby | running | pg1 | default | 100 | 1 | host=pg2 user=repmgr dbname=repmgr port=5432
3 | pg3 | standby | running | pg1 | default | 100 | 1 | host=pg3 user=repmgr dbname=repmgr port=5432

Actions #10

Updated by Philipp (double-p) Buehler 12 months ago

postgres@pg2:~$ repmgr standby switchover --siblings-follow
NOTICE: executing switchover on node "pg2" (ID: 2)
NOTICE: attempting to pause repmgrd on 3 nodes
NOTICE: local node "pg2" (ID: 2) will be promoted to primary; current primary "pg1" (ID: 1) will be demoted to standby
NOTICE: stopping current primary node "pg1" (ID: 1)
NOTICE: issuing CHECKPOINT on node "pg1" (ID: 1)
DETAIL: executing server command "sudo systemctl stop postgresql"
INFO: checking for primary shutdown; 1 of 60 attempts ("shutdown_check_timeout")
INFO: checking for primary shutdown; 2 of 60 attempts ("shutdown_check_timeout")
NOTICE: current primary has been cleanly shut down at location 0/7071970
NOTICE: promoting standby to primary
DETAIL: promoting server "pg2" (ID: 2) using pg_promote()
NOTICE: waiting up to 60 seconds (parameter "promote_check_timeout") for promotion to complete
NOTICE: STANDBY PROMOTE successful
DETAIL: server "pg2" (ID: 2) was successfully promoted to primary
WARNING: node "pg1" attached in state "startup"
INFO: waiting for node "pg1" (ID: 1) to connect to new primary; 1 of max 60 attempts (parameter "node_rejoin_timeout")
DETAIL: node "pg2" (ID: 1) is currently attached to its upstream node in state "startup"
WARNING: node "pg1" attached in state "startup"
NOTICE: node "pg2" (ID: 2) promoted to primary, node "pg1" (ID: 1) demoted to standby
NOTICE: executing STANDBY FOLLOW on 1 of 1 siblings
INFO: STANDBY FOLLOW successfully executed on all reachable sibling nodes
NOTICE: switchover was successful
DETAIL: node "pg2" is now primary and node "pg1" is attached as standby
NOTICE: STANDBY SWITCHOVER has completed successfully

Actions #11

Updated by Nico Schottelius 6 months ago

  • Status changed from New to Closed
Actions

Also available in: Atom PDF