I'm a developer learning Postgresql and I'd like to know more about backups.
As long-time Sql Server user, I understand backup and restore usually is done on a database level.
So far every tool I see for Postresql (except pg_dump) - barman, wal-e, wal-g, pgbackrest, etc. - seems to work cluster-wide. How does that work?
If I have 20 databases inside my cluster and I deployed an application with a bug, how do I restore just that one broken DB? Is that possible?
2 Answers 2
Except pg_dump which a logical backup tool, all other tools are physical backup tools and copy all database files from the cluster and transaction logs (WAL). AFAIK currently there is no way to make a physical backup of a single database.
If you need to restore one single database from a physical backup you need to restore the whole cluster-wide backup to a temporary instance and use pg_dump/pg_restore to restore the single database from the temporary cluster into the original cluster.
There's a third-party free tool (albeit from company that contributes to Postgres) named pg_probackup that allows to restore specific databases from the cluster.
As I understand it, the backup operation still works on cluster level. I should check that out