Setting up PostgreSQL HA

draft – binding slave to its tcp port and acceptance

Installation & startup


echo $PKG_PATH
pkg_add postgresql12
cp /usr/pkg/share/examples/rc.d/pgsql /etc/rc.d/
echo pgsql=yes >> /etc/rc.conf
/etc/rc.d/pgsql start

from source (slackware)

grep ^postgres /etc/group
grep ^postgres /etc/passwd

groupadd -g 209 postgres
useradd -u 209 -g 209 -d /var/lib/postgres postgres

mkdir /var/lib/postgres/
chown postgres:postgres /var/lib/postgres/

mkdir /var/log/postgres/
chown postgres:postgres /var/log/postgres/

grab and build the latest release

tar xzf postgresql-13.0.tar.gz
cd postgresql-13.0/

./configure --prefix=/usr/local
make install

cd contrib/citext/
make install
ls -lF /usr/local/lib/postgresql/

/usr/local/bin/postgres -V

Master setup

This will produce a default configuration file

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

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 = ',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            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


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

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


ps auxfww | grep ^postgres


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


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

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

Database creation


    createdb -e -h -U pgsql nobudget
    psql -U pgsql nobudget


createdb -e -h -U postgres nobudget
    psql -U postgres nobudget


sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'postgres';"
sudo -u postgres psql -c "CREATE DATABASE testdb;"

or yet another way

psql -U postgres

create database nobudget;

and create a user

create user nobudget with encrypted password 'PASSWORD-HERE';
grant all privileges on database nobudget to nobudget;


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


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.


PostgresQL: how to start up the database server and create a database

How to start and stop PostgreSQL server?

How to find out if a directory is a valid PostgreSQL cluster

cannot connect to template1: FATAL: role “_postgres” does not exist


Creating user, database and adding access on PostgreSQL

connection perms

backup & migrate