Main motivation here is to have a cheap and simple Postgres Database server with backups, but without the failover. And in order to get cheap (in terms of memory) connections - use the PgBouncer. Why without the failover? Because in my case I didn't need a 100% stable cluster, but I needed a cheap and simple solution, which in case of disaster could be recovered in some X hours. If you are looking for a failover cluster - there is nice automation here: https://github.com/vitabaks/postgresql_cluster
- Virtual Server with Ubuntu 22.04
I like to use Hetzner. It is cheap und good.
Run the container with Postgres itself.
docker run -d \
-e POSTGRES_USER=<db_username> \
-e POSTGRES_PASSWORD=<db_password> \
-v /pg-data:/var/lib/postgresql/data \
-v ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d \
-p 5432:5432 \
--restart=always \
--name db-postgres \
postgres:16.2 \
-c ssl=on \
-c ssl_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem \
-c ssl_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
Few notes:
<db_username>&<db_password>- set the credentials for the main database user.- I use here version 16.2, but you can use the latest one. I prefer to lock the version.
- SSL connection will be ready to use.
- More info - https://hub.docker.com/_/postgres
PgBouncer - is a lightweight connection pooler for PostgreSQL (https://github.com/pgbouncer/pgbouncer).
Since PgBouncer is located logically between the client and PostgreSQL you have the option of using TLS and cert authentication from client to PgBouncer and from PgBouncer to PostgreSQL. We will configure only client→PgBouncer part.
Let's prepare the certificates first:
- Go to VM
- Create directory
certs - Install https://github.com/square/certstrap by downloading a binary from https://github.com/square/certstrap/releases
- Generate the CA using
./certstrap-linux-amd64 init --common-name <certificate_authority_name>(<certificate_authority_name> - pick your name) - No passphrase
- It will create:
- <certificate_authority_name>.crt - which is the CA certificate,
- <certificate_authority_name>.key - which is the CA certificate key that will sign certificate requests,
- <certificate_authority_name>.crl - which is the Certificate Revocation List(a list of revoked certificates).
- Request key pairs from our custom CA
./certstrap-linux-amd64 request-cert --common-name pgbouncer --domain <fqdn_or_ipaddress>(<fqdn_or_ipaddress> - of your VM)./certstrap-linux-amd64 sign pgbouncer --CA <certificate_authority_name>- The --domain option adds a list of domains (called Subject Alternative Names) that the generated certificate will be valid for. We set ours to localhost because the database will run on localhost, if yours is running remotely you can add the URL instead.
- As a result you will have:
pgbouncer.crt- certificatepgbouncer.csr- certificate sign requestpgbouncer.key- private key
- Make your private key readable for docker container:
chmod +r pgbouncer.keyIf your certificates located at /root/certs/out then run the pgbouncer like this:
docker run -d \
-e DB_USER=<db_username> \
-e DB_PASSWORD=<db_password> \
-e DB_HOST=<db_ipaddress_or_fqdn> \
-e DB_NAME=<db_name> \
-e POOL_MODE=transaction \
-e LISTEN_PORT=6432 \
-e AUTH_TYPE=scram-sha-256 \
-e MAX_CLIENT_CONN=200 \
-v /root/certs/out:/certs \
-e CLIENT_TLS_SSLMODE=require \
-e CLIENT_TLS_CA_FILE=/certs/<certificate_authority_name>.crt \
-e CLIENT_TLS_KEY_FILE=/certs/pgbouncer.key \
-e CLIENT_TLS_CERT_FILE=/certs/pgbouncer.crt \
-p 6432:6432 \
--restart=always \
--name pgbouncer \
edoburu/pgbouncer:1.22.1-p0
Based on:
- https://www.crunchydata.com/blog/improving-pgbouncer-security-with-tlsssl
- https://dev.to/danvixent/how-to-setup-postgresql-with-ssl-inside-a-docker-container-5f3
Now your Postgres and Pgbouncer can use SSL client connections.
Test it:
psql postgresql://postgres@<ip_address>/postgres
So the connections string is the same, but using the port 6432
Connecting to the admin console of PgBouncer https://github.com/edoburu/docker-pgbouncer?tab=readme-ov-file#connecting-to-the-admin-console
psql postgres://postgres@<ip_address>:6432/pgbouncer # outside container
psql postgres://127.0.0.1:6432/pgbouncer # inside container
# THEN:
SHOW STATS;
SHOW SERVERS;
SHOW CLIENTS;
SHOW POOLS;
- Create a cron job which will make local dumps
- Upload the dump to the S3 storage
Create a script:
#!/bin/sh
cd /root/backups
# make the db backup
docker exec db-postgres pg_dump -U postgres -Fc my_database > my_database_dump_`date +%Y-%m-%d"_"%H_%M_%S`.pgsql
# leave only 7 last backups
ls -tp | grep -v '/$' | tail -n +8 | xargs -I {} rm -- {}
For example, database-backup-job.sh inside the /root.
Then setup a cron job: nano /etc/crontab:
0 3 * * * root sh /root/database-backup-job.sh
# it is every night at 3am
Install S3cmd apt-get install s3cmd
Create a bucket and an application key in BackBlaze. When you create an App Key - select concrete bucket and then “Allow List All Bucket Names”.
Configure the s3cmd:
s3cmd --configure
Test access:
s3cmd info s3://your-storage-bucket/
Sending backup to the bucket works like this:
s3cmd put dump_2024-03-12_09_52_37.sql.br s3://your-storage-bucket/
Modify cron job to send the new dump to the bucket:
#!/bin/sh
DUMP_FILE=my_database_dump_`date +%Y-%m-%d"_"%H_%M_%S`.pgsql
cd /root/backups
# make the db backup
docker exec db-postgres pg_dump -U postgres -Fc my_database > $DUMP_FILE
# send it to s3 bucket
s3cmd put $DUMP_FILE s3://your-storage-bucket/
# leave only 5 last backups
ls -tp | grep -v '/$' | tail -n +6 | xargs -I {} rm -- {}
Based on: