رپلیکیشن PostgreSQL با Patroni؛ quorum، جابه‌جایی و بازیابی امن

ماشین آزمایشی پایگاه داده؛ Bash با دسترسی مدیر

set -eu
. /etc/os-release
test "$VERSION_CODENAME" = 'bookworm'
test "$(dpkg --print-architecture)" = amd64
sudo systemctl mask postgresql.service postgresql@18-main.service patroni.service
sudo apt-get update
sudo apt-get install --no-install-recommends postgresql-common curl ca-certificates gnupg nano python3
sudo sed -i -E '/^[# ]*create_main_cluster[ ]*=/d' /etc/postgresql-common/createcluster.conf
printf '%s\n' 'create_main_cluster = false' | sudo tee -a /etc/postgresql-common/createcluster.conf >/dev/null
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt-get update
apt-cache policy postgresql-18 patroni python3-etcd python3-psycopg2
sudo apt-get install --no-install-recommends postgresql-18 postgresql-client-18 patroni python3-etcd python3-psycopg2
/usr/lib/postgresql/18/bin/postgres --version
patroni --version
dpkg-query -W postgresql-18 patroni python3 python3-etcd
pg_lsclusters
sudo systemctl is-active postgresql.service patroni.service || true

ماشین آزمایشی پایگاه داده؛ Bash با دسترسی مدیر

set -eu
test ! -e /opt/academy-etcd-3.7.2
download=$(mktemp -d)
cd "$download"
curl -fLO https://github.com/etcd-io/etcd/releases/download/v3.7.2/etcd-v3.7.2-linux-amd64.tar.gz
curl -fLO https://github.com/etcd-io/etcd/releases/download/v3.7.2/SHA256SUMS
grep ' etcd-v3.7.2-linux-amd64.tar.gz$' SHA256SUMS > selected.sha256
test "$(wc -l < selected.sha256)" -eq 1
sha256sum -c selected.sha256
tar -xzf etcd-v3.7.2-linux-amd64.tar.gz
sudo install -d -m 0755 /opt/academy-etcd-3.7.2
sudo install -m 0755 etcd-v3.7.2-linux-amd64/etcd etcd-v3.7.2-linux-amd64/etcdctl /opt/academy-etcd-3.7.2/
/opt/academy-etcd-3.7.2/etcd --version

ماشین آزمایشی پایگاه داده؛ Bash با دسترسی مدیر

set -eu
test ! -e '/srv/academy-pg18'
test ! -L '/srv/academy-pg18'
sudo install -d -o postgres -g postgres -m 0700 '/srv/academy-pg18'
sudo install -d -o postgres -g postgres -m 0700 '/srv/academy-pg18/tls' '/srv/academy-pg18/socket'
# Transfer the reviewed certificates and keys into tls now, then continue.
sudo chown postgres:postgres '/srv/academy-pg18/tls/'*
sudo chmod 0600 '/srv/academy-pg18/tls/'*.key
sudo chmod 0644 '/srv/academy-pg18/tls/'*.crt
sudo openssl x509 -in '/srv/academy-pg18/tls/server.crt' -noout -subject -ext subjectAltName
sudo openssl verify -CAfile '/srv/academy-pg18/tls/ca.crt' '/srv/academy-pg18/tls/server.crt' '/srv/academy-pg18/tls/api.crt' '/srv/academy-pg18/tls/patroni.crt'

ماشین آزمایشی پایگاه داده؛ Bash با دسترسی مدیر

sudo -u postgres nano '/srv/academy-pg18/make-config.py'

ماشین آزمایشی پایگاه داده؛ Bash با دسترسی مدیر

import json, pathlib, sys, ipaddress
root = pathlib.Path('/srv/academy-pg18')
ips = ['10.77.18.11', '10.77.18.12', '10.77.18.13']
network = ipaddress.ip_network('10.77.18.0/24')
assert len(set(ips)) == 3
assert all(ipaddress.ip_address(ip) in network for ip in ips)
assert root.is_absolute() and len(root.parts) >= 3 and not root.is_symlink()
assert str(root).startswith('/srv/') and str(root) != '/srv/wiunix'
index = int(sys.argv[1]) - 1
assert index in range(3)
name, ip = f'pg{index+1}', ips[index]
pg, api, dc, peer = 5432, 8008, 2379, 2380
assert len({pg, api, dc, peer}) == 4
tls = str(root / 'tls')
for filename in ('ca.crt', 'server.crt', 'server.key', 'api.crt', 'api.key', 'patroni.crt', 'patroni.key'):
    assert (root / 'tls' / filename).is_file(), filename
for filename in ('patroni.yml', 'etcd.yml', 'data', 'etcd-data'):
    assert not (root / filename).exists(), filename
cluster = ','.join(f'pg{i+1}=https://{address}:{peer}' for i,address in enumerate(ips))
etcd = {
 'name': name, 'data-dir': str(root/'etcd-data'),
 'listen-client-urls': f'https://{ip}:{dc}',
 'advertise-client-urls': f'https://{ip}:{dc}',
 'listen-peer-urls': f'https://{ip}:{peer}',
 'initial-advertise-peer-urls': f'https://{ip}:{peer}',
 'initial-cluster': cluster, 'initial-cluster-state': 'new',
 'initial-cluster-token': 'academy_pg18-dcs',
 'client-transport-security': {'cert-file': tls+'/server.crt', 'key-file': tls+'/server.key', 'trusted-ca-file': tls+'/ca.crt', 'client-cert-auth': True},
 'peer-transport-security': {'cert-file': tls+'/server.crt', 'key-file': tls+'/server.key', 'trusted-ca-file': tls+'/ca.crt', 'client-cert-auth': True}}
hba = ['local all postgres peer']
hba += [f'hostssl replication replicator {address}/32 scram-sha-256' for address in ips]
hba += [f'hostssl all postgres {address}/32 scram-sha-256' for address in ips + ['10.77.18.50']]
config = {
 'scope': 'academy_pg18', 'namespace': '/academy/', 'name': name,
 'restapi': {'listen': f'{ip}:{api}', 'connect_address': f'{ip}:{api}',
    'certfile': tls+'/api.crt', 'keyfile': tls+'/api.key', 'cafile': tls+'/ca.crt',
    'verify_client': 'required', 'authentication': {'username': 'operator', 'password': 'REPLACE_API_SECRET'}},
 'ctl': {'cacert': tls+'/ca.crt', 'certfile': tls+'/api.crt', 'keyfile': tls+'/api.key'},
 'etcd3': {'hosts': [f'{address}:{dc}' for address in ips], 'protocol': 'https', 'username': 'patroni', 'password': 'REPLACE_DCS_SECRET',
    'cacert': tls+'/ca.crt', 'cert': tls+'/patroni.crt', 'key': tls+'/patroni.key'},
 'bootstrap': {
    'dcs': {'ttl': 30, 'loop_wait': 10, 'retry_timeout': 10,
       'maximum_lag_on_failover': 1048576, 'check_timeline': True,
       'synchronous_mode': True, 'synchronous_mode_strict': True, 'synchronous_node_count': 1,
       'postgresql': {'use_pg_rewind': True, 'use_slots': True,
          'parameters': {'wal_level': 'replica', 'hot_standby': 'on',
             'max_wal_senders': 10, 'max_replication_slots': 10,
             'wal_keep_size': '256MB', 'max_slot_wal_keep_size': '2GB',
             'password_encryption': 'scram-sha-256', 'synchronous_commit': 'on'}}},
    'initdb': [{'encoding': 'UTF8'}, {'auth-host': 'scram-sha-256'}, {'auth-local': 'peer'}, 'data-checksums'], 'pg_hba': hba},
 'postgresql': {'listen': f'{ip}:{pg}', 'connect_address': f'{ip}:{pg}',
    'data_dir': str(root/'data'), 'bin_dir': '/usr/lib/postgresql/18/bin',
    'pgpass': str(root/'pgpass'),
    'authentication': {
       'superuser': {'username': 'postgres', 'password': 'REPLACE_POSTGRES_SECRET', 'sslmode': 'verify-full', 'sslrootcert': tls+'/ca.crt'},
       'replication': {'username': 'replicator', 'password': 'REPLACE_REPLICATION_SECRET', 'sslmode': 'verify-full', 'sslrootcert': tls+'/ca.crt'}},
    'parameters': {'unix_socket_directories': str(root/'socket'), 'ssl': 'on',
       'ssl_cert_file': tls+'/server.crt', 'ssl_key_file': tls+'/server.key', 'ssl_ca_file': tls+'/ca.crt'},
    'remove_data_directory_on_rewind_failure': False,
    'remove_data_directory_on_diverged_timelines': False},
 'watchdog': {'mode': 'off'},
 'tags': {'nofailover': False, 'noloadbalance': False, 'nosync': False}}
for filename, data in [('etcd.yml', etcd), ('patroni.yml', config)]:
    with (root/filename).open('x') as f:
        json.dump(data, f, indent=2)
        f.write('\n')
    (root/filename).chmod(0o600)
print(name, ip, 'new configuration files created; edit secrets before starting')