Re: Backing up and restoring a database with the SELinux pg_user problem. - Mailing list pgsql-general

Hi all,

Returning to a really old problem that is biting me again...

We upgraded our server (from Fedora Core 3 to Core 5), and thus
upgraded Postgres.  Unfortunately, we did not know that the new
version of Postgres included with 5 uses a different data format than
that included with 3.  Thus, we need to upgrade our database.  But
one can only do that by doing a dump, and initdb, and a restore.

But... we cannot do a dump because our database is borked because the
original initdb partially failed due to the SELinux bug in FC3 (see
the discussion from back in Feb 2005 on this list and RedHat bugzilla
id 149237).  Tom and I exchanged some emails about this back in
February, the last of which was:

On 4 Feb, 2005, at 15:19, Tom Lane wrote:

> Joseph Kiniry <kiniry@acm.org> writes:
>> I'm currently blocked on the system catalog schema "pg_catalog";
>> whence is it initialized?
>
> That row in pg_namespace is missing, you mean?  That's very odd ...
> what
> rows do you see in pg_namespace?  That should be loaded as part of the
> basic bootstrap operation, and if basic bootstrap had failed you'd
> definitely not have failed to notice ;-)
>
>> I have looked though all initdb-related
>> scripts, SQL files, and BKI files and have found several
>> references to
>> pg_catalog, but I have not found its definition/initialization.
>
> The definition/initialization is basically driven from macros in
> src/include/catalog/pg_namespace.h; in particular all the rows defined
> by DATA macros in that file should have been created during bootstrap.
> You might look in the .bki file to verify that there is a section
> creating and loading pg_namespace.
>
>             regards, tom lane

As I said above, I have re-examined, and executed if necessary, by
hand, all sql commands in initdb and postgres.bki, but it seems that
pg_catalog is still screwed up.  Attempting to dump, or perform
several other actions results in failures of the form:

ERROR:  42P01: relation "pg_user" does not exist
LOCATION:  RangeVarGetRelid, namespace.c:193
STATEMENT:  SELECT (SELECT usename FROM pg_user WHERE usesysid =
datdba) as dba\
, pg_encoding_to_char(encoding) as encoding, datpath FROM pg_database
WHERE dat\
name = 'gforge'

Tom asked what pg_namespace looks like, and here it is:

gforge=# select * from pg_namespace;
   nspname   | nspowner |                nspacl
------------+----------+---------------------------------------
pg_toast   |        1 |
pg_temp_1  |        1 |
pg_catalog |        1 | {postgres=U*C*/postgres,=U/postgres}
public     |        1 | {postgres=U*C*/postgres,=UC/postgres}
(4 rows)

pg_catalog has tons of stuff in it, so it looks like bki
initialisation worked.

Just to be clear, all database operations for our GForge install work
fine, we just cannot backup our database, and thus we cannot upgrade
postgres.

So why can I see pg_user and yet pg_dump fails?

gforge=# select * from pg_user;
usename  | usesysid | usecreatedb | usesuper | usecatupd |  passwd  |
valuntil| useconfig
----------+----------+-------------+----------+-----------+----------
+----------+-----------
postgres |        1 | t           | t        | t         | ********
|         |
gforge   |      100 | t           | f        | f         | ********
|        |
(2 rows)

How do I get my data out of this database?

Thanks,
Joe
---
Joseph Kiniry
School of Computer Science and Informatics
UCD Dublin
http://secure.ucd.ie/
http://srg.cs.ucd.ie/