pgBackRest VS postgres

This tutorial explains how to backup PostgreSQL database using pgBackRest and S3.

This isn't theoretical; many companies do PostgreSQL async 1:N physical replication, by using e.g. https://pgbackrest.org/ to have the primary push WAL segment files (a.k.a. "the last n milliseconds of packets" in the write-ahead log) as objects to S3, and then to have all read-replicas fetch from S3 and replay.

> You could do even better if you out-of-band signal the readiness so you do not need to poll while idle.

S3 and its clones have "object lifecycle notifications", where you can be informed by a push-based mechanism whenever a new object is put into the bucket.

But — what do you have to do, to get these notifications?

Subscribe to a message queue that S3 puts them into.

I haven't explored the territory in awhile but for bare-metal, you can't go wrong with Percona Distribution, which includes pgBackRest and a minimal web-ui. No one ever got fired for using Percona, etc.

Check out pgBackRest

I found this approach pretty cool in that regard: https://github.com/pgbackrest/pgbackrest

Something like PG barman or pg backrest could be good for you on the Postgres side.

Backups are limited. These days, pgbackrest is the go-to backup solution for PostgreSQL, and having used it I am very impressed so far. It provides full backups, differential, and incremental, as well as archiving of WAL segments for point in time recovery. It allows great flexibility in schedules and destinations for backups, how long to keep backups for, how many full backups. For example, you can have backups made to a local disk, and other backups to an external S3-compatible bucket, each with their own settings and schedules (e.g., scheduled via cron).

EDIT: Somehow i managed to fix it right now (I struggled with this problem yesterday for hours). This github issue have helped: https://github.com/docker-library/postgres/issues/537 If anyone runs into this problem, those are crucial instructions that helped me: docker system prune docker-compose up --force-recreate --build --remove-orphans --always-recreate-deps --renew-anon-volumes

Tragedy of the commons - only need a few actors to ruin it all for us. Almost all distributors face this problem, from Docker Hub to PyPI. This also reminded me of official Postgres Docker image running a cryptominer in the background [1]

[1] - https://github.com/docker-library/postgres/issues/770

If you open the dockerfile of the desired container, you can determine exactly how and what was built. If not satisfied, you can always build your own container with the right postgresql build flags.

Ok according to The Dockerfile, postgres is running in /var/lib/postgresql/data so you will need enter the container and look at the log files in /var/lib/postgresql/data/pg_log/

I got started on trying to make a basic postgres image but I can't seem to figure out how to include a shell script in the same folder as my nix file (fetched from https://github.com/docker-library/postgres/blob/master/docker-entrypoint.sh and to be modified once I get it working) into the docker image as an entrypoint

See example here : https://github.com/docker-library/postgres/issues/601