Karl O. Pinc wrote:
Hi, What is the best pg_dump format for long-term database archival? That is, what format is most likely to be able to be restored into a future PostgreSQL cluster. Mostly, we're interested in dumps done with --data-only, and have preferred the default (-F c) format. But this form is somewhat more opaque than a plain text SQL dump, which is bound to be supported forever "out of the box". Should we want to restore a 20 year old backup nobody's going to want to be messing around with decoding a "custom" format dump if it does not just load all by itself.
For schema dumps the custom format has advantages IMHO, mainly because it adds flexibility. When creating text-formatted dumps, you have to specify options like "--no-owner, ..." at _dumping_ time, while custom-format dumps allow you to specify them at _restoration_ time. For data-dumps this is less relevant, since the amount of available options is much smaller. But even there, restoring with "insert-statements" as opposed to "copy from stdin" could be usefull in some situations. Anyway, 20 years is a _long_, _long_ time. If you _really_ need to keep your data that long, I'd suggest you create text-only schema dumps, and text-only data dumps. The postgres developers are very concerned about backward compatibility in my experience, but probably _not_ for versions from 20 years ago ;-) But since the probability of the need to restore your backup in 6 months is _much_ larger than the one of needing to restore it in 20 years, I'd create customer-format dumps too. For the near future, they're the better choice IMHO.
Is the answer different if we're dumping the schema as well as the data?
The above holds true for the schema as well as for the data. greetings, Florian Pflug
