Setting up PostgreSQL HA

draft – binding slave to its tcp port and acceptance

Installation & startup

packages – see install

from source – see build

Master setup

This will produce a default configuration file

su - postgres
initdb -D /var/lib/postgres
#--auth-local --auth-host
^D

Prepare some heavy-duty folder

mkdir /var/lib/postgres/archive/
chmod 700 /var/lib/postgres/archive/
chown postgres:postgres /var/lib/postgres/archive/

the sample config

cp /usr/local/share/postgresql/postgresql.conf.sample /var/lib/postgres/
grep -vE '^[[:space:]]*(;|#|$)' /var/lib/postgres/postgresql.conf.sample \
    > /var/lib/postgres/postgresql.conf.sample.clean

the default config

mv /var/lib/postgres/postgresql.conf /var/lib/postgres/postgresql.conf.dist
grep -vE '^[[:space:]]*(;|#|$)' /var/lib/postgres/postgresql.conf.dist \
    > /var/lib/postgres/postgresql.conf.dist.clean

and your config

cp /var/lib/postgres/postgresql.conf.dist.clean /var/lib/postgres/postgresql.conf
vi /var/lib/postgres/postgresql.conf

bind to the cluster network interface and setup a few things for the HA to work

max_connections = 100                   # (change requires restart)
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Europe/Paris'
datestyle = 'iso, mdy'
timezone = 'Europe/Paris'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
default_text_search_config = 'pg_catalog.english'

listen_addresses = '127.0.0.1,CLUSTER-IP'

wal_level = hot_standby
synchronous_commit = local
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/archive/%f'
max_wal_senders = 2

Now let’s handle authorizations

psql -U postgres

create role replica login password 'PASSWORD-HERE';
alter role replica replication;

cp /usr/local/share/postgresql/pg_hba.conf.sample /var/lib/postgres/
grep -vE '^[[:space:]]*(;|#|$)' /var/lib/postgres/pg_hba.conf.sample \
    > /var/lib/postgres/pg_hba.conf.sample.clean
cp /var/lib/postgres/pg_hba.conf.sample.clean /var/lib/postgres/pg_hba.conf
vi /var/lib/postgres/pg_hba.conf

and define addresses of clients and slave servers

# TYPE  DATABASE        USER            ADDRESS                 METHOD

...
host    replication     all             CLUSTER-CIDR            md5

host    nobudget        nobudget        127.0.0.1/32            md5
host    nobudget        nobudget        ::1/128                 md5
host    nobudget        nobudget        CLUSTER-CIDR            md5

Slave setup and replica

in case you messed up and already initialized something

    su -c "pg_ctl stop -D /var/lib/postgres -l /var/log/postgres/postgres.log" - postgres

mv /var/lib/postgres/ /var/lib/postgres.trash/
mkdir /var/lib/postgres/
chown postgres:postgres /var/lib/postgres/
chmod 700 /var/lib/postgres/

now proceed with the replica

su - postgres

nmap -p 5432 MASTER-IP
pg_basebackup -h MASTER-IP -U replica -D /var/lib/postgres --progress

Proceed with the same configuration but binding to the cluster IP only and adding those settings

vi /var/lib/postgres/postgresql.conf

primary_conninfo = 'host=MASTER-IP port=5432 user=replica password=PASSWORD-HERE application_name=pro5s2-pgslave'
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
promote_trigger_file = '/tmp/postgresql.trigger.5432'

touch /var/lib/postgres/standby.signal

pg_ctl start -D /var/lib/postgres -l /var/log/postgres/postgres.log

^D

Ready to go

on both nodes

one shot startup as postgres vs root

pg_ctl start -D /var/lib/postgres -l /var/log/postgres/postgres.log

su -c "/usr/local/bin/pg_ctl start -D /var/lib/postgres -l /var/log/postgres/postgres.log" - postgres

at boot time

vi /etc/rc.d/rc.local

#self-verbose
chsh -s /bin/bash postgres >/dev/null 2>&1
su -c '/usr/local/bin/pg_ctl -D /var/lib/postgres -l /var/log/postgres/postgres.log start' - postgres
chsh -s /sbin/nologin postgres >/dev/null 2>&1
echo

status

echo POSTGRES
ps auxfww | grep ^postgres
echo

restart

pg_ctl restart -D /var/lib/postgres -l /var/log/postgres/postgres.log

su -c "pg_ctl restart -D /var/lib/postgres -l /var/log/postgres/postgres.log" - postgres

reload

pg_ctl reload -D /var/lib/postgres -l /var/log/postgres/postgres.log

su -c "pg_ctl reload -D /var/lib/postgres -l /var/log/postgres/postgres.log" - postgres

shut down

#self-verbose
chsh -s /bin/bash postgres >/dev/null 2>&1
su -c "/usr/local/bin/pg_ctl stop -D /var/lib/postgres -l /var/log/postgres/postgres.log" - postgres
chsh -s /sbin/nologin postgres >/dev/null 2>&1
echo

Backup

prepare a folder

mkdir -p /var/backup/postgres/
chown postgres:postgres /var/backup/postgres/

add this in your daily cron job script

echo -n Backing up PostgreSQL...
find /var/backup/postgres/ -type f -mtime +7 -name '*.sql.gz' -execdir rm -- '{}' \;
su -c "pg_dumpall -c -U postgres | gzip > /var/backup/postgres/dumpall-`date +%Y-%m-+%d`.sql.gz" - postgres && echo done || echo FAIL
echo

Troubleshooting

pg_basebackup: error: could not connect to server: FATAL:  role "replica" does not exist

==> create the user as shown above.

FATAL:  using recovery command file "recovery.conf" is not supported

==> this is Postgres 12+ not 8+. See above.

Resources

https://www.postgresql.org/docs/13/server-start.html

https://wiki.netbsd.org/pkgsrc/how_to_install_a_postgresql_server/

https://slackbuilds.org/repository/14.2/system/postgresql/

How to find out if a directory is a valid PostgreSQL cluster https://stackoverflow.com/questions/32013772/how-to-find-out-if-a-directory-is-a-valid-postgresql-cluster

cannot connect to template1: FATAL: role “_postgres” does not exist https://stackoverflow.com/questions/30328374/cannot-connect-to-template1-fatal-role-postgres-does-not-exist