Re: postgres 7.1 -> 7.2 ... sigh
Andreas Goesele wrote:
I had big problems going from 6.5 to 7.1 and now going from 7.1.3-7
again doesn't go smooth. (On an up do date woody.) The problem seems
to be related to the fact that I call myself "user" (fanciful name,
isn't it?), but shouldn't one be free to call the users as one likes?
Anyway how can I get my postgres databases again working?
Problem in some more details:
During the upgrade I opted for the automatic upgrade. I was shown a
beautiful db.out and continued.
But then something went wrong, and I can't connect, getting instead
the error message:
psql: FATAL 1: user "user" does not exist
Looking at the content of /var/lib/postgres/data it seems that there
wasn't any database really *imported*.
In my syslog I find:
Apr 20 22:40:58 localhost postgres:  ERROR: CREATE USER: sysid 1 is already assigned
Apr 20 22:40:59 localhost postgres:  FATAL 1: user "user" does not exist
Apr 20 22:41:27 localhost postgres:  FATAL 1: user "user" does not exist
Apr 20 22:41:35 localhost postgres:  FATAL 1: user "user" does not exist
Apr 20 22:54:54 localhost postgres:  FATAL 1: user "user" does not exist
The output during the update process starting from my acceptance of
the database dump:
-- postgresql-dump completed on Sat Apr 20 22:39:32 CEST 2002
On the basis of this dump, is it OK to delete the old database? [y/n] y
Destroying old database...
Copying /var/lib/postgres/data to /var/lib/postgres/data.7.1.3-7.save
Creating the new database structure
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
Fixing permissions on existing directory /var/lib/postgres/data... ok
creating directory /var/lib/postgres/data/base... ok
creating directory /var/lib/postgres/data/global... ok
creating directory /var/lib/postgres/data/pg_xlog... ok
creating directory /var/lib/postgres/data/pg_clog... ok
creating template1 database in /var/lib/postgres/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
creating system views... ok
loading pg_description... ok
vacuuming database template1... ok
copying template1 to template0... ok
Success. You can now start the database server using:
/usr/lib/postgresql/bin/postmaster -D /var/lib/postgres/data
/usr/lib/postgresql/bin/pg_ctl -D /var/lib/postgres/data -l logfile start
Checking that the postmaster is running
Starting the postmaster
Starting PostgreSQL postmaster
/usr/lib/postgresql/bin/postmaster -i -b /usr/lib/postgresql/bin/postgres -D /var/lib/postgres/data >/var/log/postgres.log 2>&1 &
Loading the database from /usr/lib/postgresql/dumpall/db.out
You are now connected to database template1.
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');
CREATE USER "user" WITH SYSID 1 CREATEDB CREATEUSER;
ERROR: CREATE USER: sysid 1 is already assigned
Sysid 1 is probably assigned to the user postgres created by the
install. To get your data reloaded into the database try editing the
dumped file so that 'user' is now number two or something.
DELETE FROM pg_group;
You are now connected to database template1 as user postgres.
CREATE DATABASE "accounts" WITH TEMPLATE = template0 ENCODING = 'LATIN1';
You are now connected to database accounts as user postgres.
\connect: FATAL 1: user "user" does not exist
Restarting PostgreSQL database: postmaster
Stopped /usr/lib/postgresql/bin/postmaster (pid 21507 21508 21509).
Starting PostgreSQL postmaster.
postmaster successfully started
Enabling the PL procedural language in all PostgreSQL databases...
plpgsql added to accounts
plpgsql added to template1
Please help me to get my databases running again!
Thanks in advance!
Jason Chambers (firstname.lastname@example.org)
To UNSUBSCRIBE, email to email@example.com
with a subject of "unsubscribe". Trouble? Contact firstname.lastname@example.org