2

We have some large staging/scratch tables in one of our PostgreSQL databases. The data in them can be automatically regenerated from other databases after recovery of the database, and aren't business critical. Is there a way to ignore those tables when backing up using Barman? They are all in one schema.

This is to reduce recovery time and save space.

Maybe we can use some sort of persistent temporary tables? Tablespaces?

We have the tables because it is convenient when we do ETL. But aren't used by any applications or clients.

Are there other alternatives to do something similar?


Some documentation I've been reading through:

https://www.pgbarman.org/index.html

http://docs.pgbarman.org/release/2.12/

4

Barman will create online file system backups, and such backups can only be done of the complete cluster.

To backup parts of a database, you must use a logical backup with pg_dump, but then you cannot perform point-in-time recovery.

One way to work around that is to have the tables you don't want to back up be part of a different cluster and access them with postgres_fdw, or store them as “flat files” and use file_fdw to access them. Be warned, though, that there are performance penalties when querying such foreign tables.

  • Thank you. We used postgres_fdw for ETL before, but the amount of data we are working with made it unmanageable. Using file_fdw is interesting, didn't know about it. Will have to look into it. – André Christoffer Andersen Jan 14 at 10:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.