Continuous archiving?

[isundaylee]

I was looking for a way to incrementally backup PostgreSQL, and came to this feature here http://www.postgresql.org/docs/9.1/static/continuous-archiving.html Maybe it is useful to add commands to this plugin to enable that?

Currently we have psql:dump and psql:restore for backup and restoring. They are great and simple. However, they both operate on a single database (which, in itself, is often desirable), and all operate on a complete backup for the database. In addition to this, I think it'd be useful to have a incremental backup solution for the all databases at once (just for disaster recovery case).

It could be something that works like:

• psql:archive_enable: write PostgreSQL config to enable continuous archiving, and initiate the base backup
• psql:archive_disable: disable continuous archiving, and (optionally) delete the archive
• psql:archive_path: gives a path that the user can use rsync on to extract the archive files out of the container
• psql:archive_restore: restore the databases from the archive files

And for disaster recovery, the user can rsync the archive files back to psql:archive_path, and then run psql:archive_restore.

I can write a PR to implement this if it sounds like something useful :)

[Flink]

Hey Jiahao!

Sorry for answering a bit late, I'm on vacation ;) Your idea seems great albeit it seems quite complex too (quickly read your link) so yes if you want to write a PR, you're welcome :smile:

Maybe we could use psql:cab:enable, etc. as commands? (cab for Continuous Archive Backup) Just an idea, what do you think?

Anyway, thanks for your idea and help :+1:

[Flink]

Also you should target the develop branch when developping your feature instead of master, thanks :)

[isundaylee]

I like the suggestion about psql:cab:subcommand! And yeah, I'll target the develop branch.

So I tested the idea out a bit, and I think it's very feasible. There's one thing we need to think about, though: how do we get the files out of the container?

I tried putting all the archive files in a archive directory under the data folder. However, the mounted volume (at $PSQL_ROOT/.psql_sc) doesn't seem to grant access to anybody (owner uid 999). Maybe that should only be accessed by Docker itself?

Any idea on that? Thanks! :)

[Flink]

Well, I don't know if access is a problem. I think backup should be an administrative task and so you should have a sudo/root account access to do whatever you want. Another possibility is to use docker cp I think.