Standalone PostgreSQL cluster

README.md

Standalone PostgreSQL cluster

A basic example of provisioning and administration of a standalone PostgreSQL cluster running on a single CentOS 8 or CentOS 8 Stream made for Deployment from Scratch.

It demostrates:

• Creating a PostgreSQL cluster from scratch
• Asking for database user passwords interactivelly
• Creating a custom firewalld service for the PostgreSQL service
• Connecting to PostgreSQL with psql over SSL/TLS
• Setting up automatic weekly system update
• Setting up log rotation for the PostgreSQL log and max limit for system log
• Doing cluster backups and restores
• Creating admin tasks

This demo exposes the database to the outside world on port 5432 with a configuration option to limit the access to a IP subnet. A local disk is used for storage.

Configuration

The following variables configure the provisioning and configuration steps:

Variable	Meaning
SERVER	The virtual machine IP address
SSH_KEY	The path to the private SSH key
LISTEN_ADDRESS	The IP address or mask for binding PostgreSQL connections
PGDIR	The PostgreSQL directory on the block storage (optional)
ADMIN	The administrator user name that will replace root
DB_USER	The initial database user name
DB_NAME	The initial database name

Edit the settings.sh file to change this settings.

I recommend adding the SSH key to the OpenSSH authentication agent with:

$ ssh-add $SSH_KEY

And regenerate your SSL keys:

$ cd config
$ openssl req -x509 -nodes -newkey rsa:2048 -days 3650 -keyout privkey.pem > cert.crt

Provisioning

Provisioning step requires a virtual server with SSH-key based authentication preconfigured for root.

To provision the server for the first time, run setup.sh followed by create_db.sh to make a first database:

$ ./setup.sh
$ ./create_db.sh

You will be asked for the database password interactivelly. Choose a strong password!

One way to choose a good password is to generate it:

$ openssl rand -base64 64
tfyQveKcvV2Ca8E+XrYgI5bDORlYTWOnYvB73hT/+dLfP3SZk9cQcmC81Bk4FoP9sFAYT7Mz9f+Eo35feMROMg==

You can also rerun certain steps with the -u and -s options if necessary.

Rerunning the script won't recreate the PostgreSQL data directory to not lose your data. If you want to recreate it as part of the process, set $DELETE_DATA to any value:

$ DELETE_DATA=true ./setup.sh

Tasks

Administration

To connect to the server as $ADMIN with ssh:

$ ./ssh.sh

To connect with psql:

$ DB_USER=user DB_NAME=db ./psql.sh

To connect with the defaults, you don't need to provide DB_USER and DB_NAME.

To add new database:

$ DB_USER=dbuser DB_NAME=newdb ./create_db.sh

It will create a new user and makes him an owner of the provided database which it also creates.

Un/locking root

Once we are done rerunning setup.sh, we can lock root:

$ ./lock.sh

And unlock it later if necessary:

$ ./unlock.sh

Backups

To create a backup and download it:

$ ./backup.sh

To restore a backup:

$ ./restore.sh backup.sql

~

© Josef Strzibny