Thread: 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