Backup when the database is missing all of the pg_* tables

From: Daniel Browning <db(at)kavod(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: Daniel Browning <db(at)kavod(dot)com>
Subject: Backup when the database is missing all of the pg_* tables
Date: 2005-05-05 18:17:54
Message-ID: 20050505181754.GA9167@kavod.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

How can I backup a database (for restoration) when it is missing all of the
pg_* tables?

I've been bitten by the "SELinux silently ruins initdb" bug as reported by Tom
Lane: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=149237 and another
user: http://www.mail-archive.com/pgsql-bugs(at)postgresql(dot)org/msg11191.html

Unfortunately, I didn't notice the problem until after the database had new
data added to it. I need to backup the database and recreate it with initdb,
but pg_dump doesn't work:

relation "pg_user" does not exist

I tried "--data-only --no-owner --no-privileges" to no avail. I can still do
'\d <table>' and 'COPY <table> to ...', which is enough to get the information
needed for a minimal dump, so it's theoretically possible.

If there isn't a way to do this already, I'm going to build the schema from a
backup, and then write a script that will COPY each table, then COPY them back
after the initdb.

Thanks,
--
Daniel Browning <db(at)kavod(dot)com> - Kavod Technologies. Random Fortune:
Consider well the proportions of things. It is better to be a young June-bug
than an old bird of paradise.
-- Mark Twain, "Pudd'nhead Wilson's Calendar"

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2005-05-05 18:19:15 Re: A real puzzler: ANY way to recover?
Previous Message Samngan 2005-05-05 18:16:46 Re: REMOVE