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:
4 Feb, 2005, at 15:19, Tom Lane wrote:
Joseph Kiniry <kiniry (AT) acm (DOT) orgwrites:
>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
; 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:
ERRR: 42P01: relation "pg_user" does not exist
LCATIN: RangeVarGetRelid, namespace.c:193
STATEMENT: SELECT (SELECT usename FRM pg_user WHERE usesysid =
datdba) as dba\
, pg_encoding_to_char(encoding) as encoding, datpath FRM 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/
(end of broadcast)
TIP 3: Have you checked our extensive FAQ?

Joseph Kiniry <kiniry (AT) acm (DOT) orgwrites:
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:

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

So why can I see pg_user and yet pg_dump fails?

gforge=# select * from pg_user;
[ works ]

Hmm you manually recreated the pg_user view you say? I wonder if
you mistakenly put it in the public schema instead of pg_catalog.
The quoted command from pg_dump is done after issuing
set search_path = pg_catalog;
so that nothing user-created will accidentally mess it up. If you
can still manually select from pg_user after issuing that same SET
command, then something is really seriously strange

If you find that indeed pg_user is in public, drop it there and
re-create it in pg_catalog. You'll need to be superuser to do
that but I don't think it'll require any more pushups than that.

regards, tom lane

(end of broadcast)
TIP 3: Have you checked our extensive FAQ?

Hi Tom,

Thank you for the very quick response. I'll let Robin followup on
this for tonight, as I'm heading to bed after a very long day.

4 Jul, 2006, at 22:12, Tom Lane wrote:

Joseph Kiniry <kiniry (AT) acm (DOT) orgwrites:
>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:
>
>ERRR: 42P01: relation "pg_user" does not exist
>LCATIN: RangeVarGetRelid, namespace.c:193
>STATEMENT: SELECT (SELECT usename FRM pg_user WHERE usesysid =
>datdba) as dba\
>, pg_encoding_to_char(encoding) as encoding, datpath FRM pg_database
>WHERE dat\
>name = 'gforge'
>
>So why can I see pg_user and yet pg_dump fails?
>
>gforge=# select * from pg_user;
>[ works ]
>
Hmm you manually recreated the pg_user view you say? I wonder if
you mistakenly put it in the public schema instead of pg_catalog.
The quoted command from pg_dump is done after issuing
set search_path = pg_catalog;
so that nothing user-created will accidentally mess it up. If you
can still manually select from pg_user after issuing that same SET
command, then something is really seriously strange

I did notice the above command and executed it by hand. But as to
whether I did it all at the right time, in the right order, to ensure
that everything is in the right schemas :)

If you find that indeed pg_user is in public, drop it there and
re-create it in pg_catalog. You'll need to be superuser to do
that but I don't think it'll require any more pushups than that.

We'll double-check this.

regards, tom lane

Thanks again for the pointer,
Joe

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

(end of broadcast)
TIP 2: Don't 'kill -9' the postmaster