Re: Accidentally truncated pg_type

On Tue, 2011-07-12 at 01:12 +0100, Matthew Byrne wrote:
> I have a large database full of irreplaceable data, and due to a
> ridiculous happenstance I accidentally executed this code (as a superuser,
> of course):
>
> DELETE FROM pg_catalog.pg_type;
>
> Now the database is *seriously* unhappy - every SQL command returns an
> error message. How do I get at my data?

[ Only consider this after you've taken Craig's advice. ]

Did you have any user-defined types or extensions?

You might try something as simple as (on your throw-away experimental
copy, of course):

1. Make a new cluster with initdb (or just connect to a different
database, if that still works).
2. Load any extensions or user-defined types into that one, and make
sure they get the same OIDs (or hack the output of the next step).
3. Copy out the contents of pg_type, including OIDs.
4. Copy that data back into your empty pg_type.
5. Try to do a logical backup, load that data into a fresh instance, and
you might be OK.

I haven't really thought this plan through, but that's the first thing
I'd try (after doing file-level copies of everything, of course!).

Regards,
Jeff Davis