Arnt Gulbrandsen wrote: >On 11/14/2012 03:32 PM, Martin Rode wrote: >>The point is, you can do incremental backups easily if you store >>attachments in files. If you store them inside the database a full dump >>gets huge and can take a lot of time.
>Yes. An artifact of pg_dump (and dumpall). IMO PITR backups are a >better solution than moving parts of the db where pg_dump cannot see >them. But there are drawbacks to PITR too, and I see your point. Externalising the attachments allows for some extra flexibility: - When disaster hits (disk(s) full), it allows for some immediate relief by creating a script that quickly runs through all attachments and deletes some large/unimportant ones. - It reduces the probability that the db runs into a problem due to full partitions (because you can put the attachments on different partitions than the db itself). - A postgresql vacuum will finish sooner (under normal conditions). - A postgresql vacuum will actually finish (if the db has reached sizes of >300GB, a postgresql vacuum is a problem by itself). - When restoring the database, it would allow the restore to finish more quickly, filling in the attachments at your leisure at some later point in time. Most of the repository is going to be coherent and accessible soon. Restoring the attachments (which first) can then be prioritised according to urgency. -- Stephen.
