Обсуждение: Backup when the database is missing all of the pg_* tables

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@postgresql.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@kavod.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"

Daniel Browning <db@kavod.com> writes:
> 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@postgresql.org/msg11191.html

While I have not tried to get out of that situation myself, I think it
should be doable.  The basic point is that the SELinux bug^H^H^Hsecurity
policy prevented certain parts of the initdb script from taking effect;
in particular those that created system views such as pg_user.  So what
you gotta do is execute those CREATE VIEW commands by hand as superuser,
and then pg_dump should work.  I think.

The parts of initdb that were foiled are the parts that look like
    postgres <<EOF
        ... SQL script here ...
    EOF
I suspect that not all these parts absolutely have to be redone to get
to a state where pg_dump will work, but it might be easiest to just
do 'em all.

Note that you should "SET search_path = pg_catalog" in order to
duplicate the state that is set up by the parameters the initdb script
passes to postgres.

If you are feeling like this might take a bit of experimentation to get
right, I quite agree.  I *strongly* recommend that you shut down the
postmaster and then make a tarball backup of /var/lib/pgsql/data before
you start trying any of this stuff.  Then you can go back to the backup
as needed till you get it right.

Good luck!  Please keep notes and post here with details if it works
(or not).  You're probably not the only one in this fix.

            regards, tom lane