W dniu 2012-02-27 21:59, Tom Lane pisze:
Ireneusz Pluta<i..@xxxxx> writes:
It apperas that I have some entries in pg_class that have relnamespace not appearing in
pg_namespace. So pg_dump fails with "pg_dump: schema with OID 52072764 does not exist", I guess.
That's a bit disturbing --- do you have any idea what triggered that?
Not exactly. This is an environment where a user running his periodic reports, per logic of his
script, for each run creates a new schema which then serves as a separated runtime sandbox. The
schema after such a run is left alone uselessly unless one eventualy wants to look into for
comparing with other runs or debugging, I guess. Just a subject of further garbage collecting. It
may happen that not each run finishes succesfully, particularly in cases of manual runs on a
development/debugging course and Ctrl-C hits. The orphaned tables seem to be the same as appearing
in another schemas I see here. They are not complete set of what can be seen in another schemas,
just two out of much more.
How to get rid of or fix these damaged entries? Just try to delete from pg_class manually (and then
delete files pointed to by their relfilenode)? Or create a new schema and update
pg_class.relnamespace to the oid of the new schema and then inspect and drop?
I'd do the latter I think. Keep in mind that there are probably also
entries in pg_depend linking the tables to the schemas. If your goal is
only to get to a clean dumpable state and then dump and reload the
database, you probably don't need to worry about fixing pg_depend.
However, if you intend to keep on using the database without a reload,
it'd be prudent to make sure pg_depend is straightened out as well.
I found rows of missing namespace oid in pg_depend.refobjid. I understand that I update them to oid
of a newly created empty schema?
But, as in the menatime I played with the case separately on a test database, I found also pg_type
entries need fixing. I was not aware of pg_depend, but found that after only tweaking both pg_class,
and pg_type the database got dumpable.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
