Setting up PostgreSQL HA

draft – binding slave to its tcp port and acceptance

Installation & startup

netbsd

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

wget https://ftp.postgresql.org/pub/source/v13.0/postgresql-13.0.tar.gz
tar xzf postgresql-13.0.tar.gz
cd postgresql-13.0/

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

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

/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
^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

Database creation

netbsd

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

slackware

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

or

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;
^D

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/

PostgresQL: how to start up the database server and create a database https://stackoverflow.com/questions/12746854/postgresql-how-to-start-up-the-database-server-and-create-a-database

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

ops

https://www.postgresql.org/docs/8.2/tutorial-createdb.html

https://www.postgresql.org/docs/9.1/app-createuser.html

Creating user, database and adding access on PostgreSQL https://medium.com/coding-blocks/creating-user-database-and-adding-access-on-postgresql-8bfcd2f4a91e

connection perms

http://customer.docushare.com/s.nl/ctype.KB/it.I/id.29578/KB.187/.f

backup & migrate

https://pgloader.readthedocs.io/en/latest/ref/pgsql.html

scalable

https://www.quora.com/Is-PostgreSQL-a-distributed-and-scalable-database

HA

https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling

https://www.howtoforge.com/tutorial/how-to-set-up-master-slave-replication-for-postgresql-96-on-ubuntu-1604/

https://www.postgresql.org/docs/current/different-replication-solutions.html

https://www.postgresql.org/docs/current/high-availability.html

https://dba.stackexchange.com/questions/57658/how-to-configure-replication-from-postgresql-rds-to-vanilla-postgresql

https://www.2ndquadrant.com/en/blog/replication-configuration-changes-in-postgresql-12/

https://www.postgresql.org/docs/current//runtime-config-replication.html#GUC-PROMOTE-TRIGGER-FILE

https://www.postgresql.org/docs/current/runtime-config-replication.html#RUNTIME-CONFIG-REPLICATION-STANDBY

https://www.postgresql.org/docs/current/runtime-config-wal.html#RUNTIME-CONFIG-WAL-RECOVERY-TARGET

https://www.percona.com/blog/2019/10/11/how-to-set-up-streaming-replication-in-postgresql-12/


GUIDES | LECTURES | BENCHMARKS | SMTP HEALTH