Обсуждение: Still having trouble importing 6.3 DB

AT Tom Lanes sugestion I upgraded to 6.4.2. While this was undoubtablya good die, it did not solve the basic
problem.
I am runing as the postgres superuser, and trying to inport the datafrom my 6.3 databse inot my nice new 6.4.2 one. I
amgeting errorsalmost as soon as the first non-postgres user is encuntered. Here isthe tail end of the atempt:
 

\connect template1 postgres
connecting to new database: template1 as user: postgres
create database stan;
QUERY: create database stan;
CREATEDB
\connect stan postgres
connecting to new database: stan as user: postgres
\connect - stan
connecting as new user: stan
FATAL 1:  SetUserId: user 'stan' is not in 'pg_shadow'

Could not connect to new database. exiting
I am using the example from the INSTALL document to import this data,that is:
psql -e template1 < db.out
I can connect to this db using psql, and I have run creatdb. What is this pg_shadow & how do I fix it?
Or do I need to create the varios database first before doing theimport?
Thanks.

-- 
Stan Brown     stanb@netcom.com                                    843-745-3154
Westvaco
Charleston SC.
-- 
Windows 98: n.useless extension to a minor patch release for 32-bit extensions anda graphical shell for a 16-bit patch
toan 8-bit operating systemoriginally coded for a 4-bit microprocessor, written by a 2-bit company that can't stand for
1bit of competition.
 
-
(c) 1999 Stan Brown.  Redistribution via the Microsoft Network is prohibited.

"Stan Brown" <stanb@awod.com> writes:
>     I am runing as the postgres superuser, and trying to inport the data
>     from my 6.3 databse inot my nice new 6.4.2 one. I am geting errors
>     almost as soon as the first non-postgres user is encuntered. 
> \connect - stan
> connecting as new user: stan
> FATAL 1:  SetUserId: user 'stan' is not in 'pg_shadow'
>     What is this pg_shadow & how do I fix it?

Ah, I see what's going on.  You're getting burnt by not having upgraded
to 6.3.1 or 6.3.2 before moving to 6.4.  The pg_user table was changed
at 6.3.1, and pg_dumpall was modified accordingly --- but the 6.3
version of pg_dumpall produces a script that will not successfully load
the user definitions into 6.3.1 or later.

I think you can make it work by just changing "copy pg_user from stdin;"
to "copy pg_shadow from stdin;" a couple lines down from the top of
the dump script.

Memo to hackers: we need a better way of coping with cross-version
changes that affect dump scripts...
        regards, tom lane

> Ah, I see what's going on.  You're getting burnt by not having upgraded
> to 6.3.1 or 6.3.2 before moving to 6.4.  The pg_user table was changed
> at 6.3.1, and pg_dumpall was modified accordingly --- but the 6.3
> version of pg_dumpall produces a script that will not successfully load
> the user definitions into 6.3.1 or later.

Ooh, good catch.  You are correct.

> 
> I think you can make it work by just changing "copy pg_user from stdin;"
> to "copy pg_shadow from stdin;" a couple lines down from the top of
> the dump script.
> 
> Memo to hackers: we need a better way of coping with cross-version
> changes that affect dump scripts...

Not sure how to deal with that one.

--  Bruce Momjian                        |  http://www.op.net/~candle maillist@candle.pha.pa.us            |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

On Mon, 4 Jan 1999, Tom Lane wrote:


> I think you can make it work by just changing "copy pg_user from stdin;"
> to "copy pg_shadow from stdin;" a couple lines down from the top of
> the dump script.
> 

I had this problem ypgrading from 6.4betaX to 6.4{0,1,2}
A quick note , first do a pg_dumpall do dump all your databases the mv 
data data.old recreate the data directory and do an initdb that will create
all the relations properly , otherwise the pg_user view is not installed and
you will have some problems! 

--
Incredible Networks LTD       Angelos Karageorgiou
20 Karea st,                  +30.1.92.12.312 (voice)
116 36 Athens, Greece.        +30.1.92.12.314 (fax)
http://www.incredible.com     angelos@incredible.com (e-mail)

Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> Memo to hackers: we need a better way of coping with cross-version
>> changes that affect dump scripts...

> Not sure how to deal with that one.

I don't have any good general-purpose answers either, but I can think
of one thing we probably oughta do: seems to me that using a direct
copy to a system table is just asking for cross-version trouble.
pg_dumpall ought to create users using the CREATE USER command, not
by copy to pg_shadow, don't you think?

I don't mind pg_dumpall doing a select from pg_shadow in order to get
the info; that should be OK on the premise that the dump utility will be
run against a database of the same version as the utility itself.  The
thing not to assume is that the output script will be reloaded into
exactly the same version of Postgres.
        regards, tom lane