How to make backups of Postgresql, which "lives" in a docker container?

Question

PostgreSQL

I want to automate backups of postgresql, which is running in a docker container, and also do it right.

Answer 1

To begin with, to understand, what for do you need the base in the docker. If backups are needed, then obviously the base is needed. More than once I saw the necessary databases without backups, but somehow I don’t remember the configured backups for unnecessary ones. And if you need a base, then why is the docker here? Consider this question carefully. If you can surprise me, I will be very grateful, so far none of the interviewed Docker adherents has coped with such a simple question.
Well, then decide on the requirements for backups. Globally, there are two possibilities, and both have nothing to do with where the base binaries are located. It is possible to execute in general from other host on a network.
First, you can periodically take a logical dump of data using pg_dump or pg_dumpall. Plus: they are more compact, usually much more compact, plus they compress well, for custom and directory formats it is possible to restore objects selectively, for example, only one table. You can backup individual databases, exclude something unnecessary from the dump. Cons: significant recovery time, in particular due to the need to build all the indexes; recovery is possible only at the time the dump is started.
Second, you can make a binary copy and keep a WAL log. Pluses - the ability to restore the base to an arbitrary point in time from the end of pg_basebackup and as long as there is a continuous WAL archive. But basebackup takes exactly the same amount of space as the cluster, plus the WAL archive. You can only backup the entire cluster. Restoration is also possible only in its entirety.
In order not to collect all the binding around pg_basebackup yourself, see pgbackrest, barman. wal-g is available for storage in s3.
It is quite reasonable to use both methods at the same time, the wal archive for point-in-time recovery, as well as, for example, a logical dump once a week / month for longer-term storage. Sometimes this happens, but "let's restore a year-old tablet."

Answer 2

backup
Recovery

cat your_dump.sql | docker exec -i your-db-container psql -U postgres

Answer 3

docker exec to the rescue