Postgres: Support Continuous Archiving - Point In Time Recovery (PITR) WAL Archive

[TJM]

We have been running this container for a while, but after a couple recent "issues" we have decided it would be a "good thing" to have PITR (point in time recovery) easily enabled. We currently have the data volume on NFS (sigh), which is snapshotted once per day. We have been looking into implementing this: https://severalnines.com/blog/become-postgresql-dba-point-time-database-restoration

https://www.postgresql.org/docs/9.6/continuous-archiving.html

It might be something good to support as a "switch" (option)?

[yosifkit]

We have POSTGRES_INITDB_WALDIR to change the folder where it will store the wal (only works on a fresh DB). The default directory is within the db directory. Otherwise you can just add any postgres config to a custom config file or pass it in as arguments (see the Hub docs).

[TJM]

Hi @yosifkit

Thanks for responding, but changing the location of the WAL dir is not exactly the same thing. This setup archives off the WAL files once the database is done with them to provide the ability to recover from a crash or other "problem" (such as administrative errors). I realize that we have the ability to enable this, but my request was to make an "easy" button for enabling it, perhaps even by providing an environment variable WAL_ARCHIVE_DIR or something? I am not 100% sure how to best implement it, which is why I am requesting feedback from the community here.

[tianon]

As far as I can tell, WAL is always enabled in recent PostgreSQL releases. Any of the settings from https://www.postgresql.org/docs/current/runtime-config-wal.html should be easy to apply via either .conf in the image/container or by just using -c wal_level=archive (for example), as documented in the link @yosifkit shared:

https://github.com/docker-library/docs/tree/59062e8136118bcf84f5bde9ecaba4e2011cfd92/postgres#database-configuration

So it sounds like you want to set the level to archive and set POSTGRES_INITDB_WALDIR to somewhere reasonable. There are probably also other configuration settings you'll want to set from https://www.postgresql.org/docs/current/runtime-config-wal.html#RUNTIME-CONFIG-WAL-ARCHIVING, but those are probably going to vary case-by-case.

[TJM]

I don't think you want to change POSTGRES_INITDB_WALDIR ? ... maybe if you had some high(er) performance storage you wanted to put it on? but I think that is separate from the archive destination. The way I understood it, was when the DB itself is done with the WAL file (from the WALDIR above), it will call an "archive command" which will copy it elsewhere (NFS, S3, another "directory"), and remove the old WAL file. Thus providing the ability to restore the last "full" backup and replay transactions from the WAL files that were archived to provide "point-in-time" recovery.