Обсуждение: 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