pg_dump – Handling Table Dependencies with –table Option

pg-dumppostgresql

The documentation says this when using -t table:

Note When -t is specified, pg_dump makes no attempt to dump any other
database objects that the selected table(s) might depend upon.
Therefore, there is no guarantee that the results of a specific-table
dump can be successfully restored by themselves into a clean database.

However, when the table has a sequence, and the owned by for the sequence is set to the table, it is included in the dump.

Shouldn't it be excluded according to this part of the documentation? (If so, why are sequences exceptions?)

What kind of database objects are going to be excluded in -t table mode then?

Best Answer

pg_dump -t will dump the definition of foreign key constraints. But will not dump the definition (or data) of the table which that constraint points to.

Similarly, if you have a check constraint which uses a function, the definition of the function will not get dumped.

I don't know the thought process behind dumping sequences. That does seem like a documentation bug.

Related Solutions

Postgresql – Can stable (or immutable) functions call volatile functions

In the case of purely mathematical circumstances, it is my understanding that the premise of a function f calling a volatile function g would indicate that f is inherently volatile as well. That's because functions in mathematics are equivalencies designed to reduce the work of explaining systems and the one (function or expression) can be substituted for the other, often as syntactic sugar moreso than anything else.

However, my answer isn't specifically from the context of postgres, as I don't normally work in PG. It may be reasonable in PG that the f function would be stable even while the g function were volatile.

I would encourage every developer to examine their code as being basically just mathematics exposed, and so therefore would encourage you to consider the code here as volatile.

Postgresql – pg_dump does not finish

I was able to figure out which file in the database storage was the culprit, by copying all the files to /dev/null.

cp -vR /usr/lib/postgresql/8.4 /dev/null

(The path to your DB files might differ)

The currupt file could not be copied, but there was nothing I could do to change that. (so it was most probably a FS error or hardware failure)

So I restarted the server with a forced fsck (e.g. touch /forcefsck), to make sure the FS would do the best to fix itself. This might not be the way you'll want to go, since it is possible to have a total data loss afterwards, but I was able to preserve the most precious data already beforehand, so I took the risk.

After reboot I could finally access the inaccessable table again, but I am not sure, if the data contained is corrupted or not. Anyway, I do have a backup now, which I can disect to find out, and my server can go back online for now...

I recommend reading the wiki of postgres about corruption and the slides of this FOSDEM presentation for some more info on DB corruption

Best Answer

Related Solutions

Postgresql – Can stable (or immutable) functions call volatile functions

Postgresql – pg_dump does not finish

Related Question