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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
