Thread: Accidentally truncated pg_type
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? Regards, Matt
On 12/07/11 08:12, 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? Do not attempt any recovery yet. STOP doing whatever you are doing. If any programs are accessing the database, stop them. Make a file-system level copy of your database ***NOW***. Put one duplicate on a CD, external hard disk or other media you can completely remove from your computer and put it somewhere safe. Keep the duplicate copy on your hard drive to attempt recovery with. Personally I'd make a copy, stop the postmaster, and make a second copy. That's just because I don't know which would work out better. Up to you. I don't suppose you have any backups of any older versions of the database? If they are, are they PITR backups or are they pg_dump backups? Have you already attempted any recovery steps? Document them in detail if you have. If this database is in any way important to you, you should consider hiring an experienced professional to assist you with recovery. See: http://www.postgresql.org/support/professional_support -- Craig Ringer
On 12/07/11 08:12, 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? Oh, once you've copied your database you should stop the postmaster and not start it again without further advice/instructions. The data in pg_type may not have been vacuumed or overwritten yet if you haven't been messing about trying to fix it before asking for help. -- Craig Ringer
On Mon, Jul 11, 2011 at 6:25 PM, Craig Ringer <craig@postnewspapers.com.au> wrote: > On 12/07/11 08:12, 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? > > Oh, once you've copied your database you should stop the postmaster and > not start it again without further advice/instructions. The data in > pg_type may not have been vacuumed or overwritten yet if you haven't > been messing about trying to fix it before asking for help. > Second the suggestion of copying everything. Of course with autovacuum the chances that things have been vacuumed is not 0 and may be fairly high depending on configuration. In addition to those suggestions, the obvious question is: Do you have backups? What do they contain? How old are they? What sort of backups do you have? Best Wishes, Chris Travers
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