PDA

View Full Version : Trouble upgrading to 7.2.3

luca200
10-17-02, 20:23
I have just installed PostgreSQL 7.2.3 and I need to load it with a dump from 7.0.2, but when I submit my dump I get an error.
After the install, I created the data directory with mkdir /usr/local/pgsql723/data, then chown postgres /usr/local/pgsql723/data (obviously postgres user did already exist).
After that, I made initdb (by postgres user) and then started postmaster (by pg_ctl).
Finally I tried to restore my db with/usr/local/pgsql723/bin/psql -d template1 -f restorefile and I get an error that's something like "I cannot create a database because some other users are connected to template1" (I'm sorry I don't have the exact error text, but I didn't work on this machine when I got it).
This is the beginning of the dump:

\connect template1
select datdba into table tmp_pg_shadow from pg_database where datname = 'template1';
delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
drop table tmp_pg_shadow;
copy pg_shadow from stdin;
root 29 t f t f \N \N
callcenter 27 t f t f \N \N
nobody 28 t f t t \N \N
\.
delete from pg_group;
copy pg_group from stdin;
callgroup 1 {26,29,27}
\.
\connect template1 callcenter
create database "callcenter" with encoding='SQL_ASCII';
\connect callcenter callcenter

It gives me the first error in "create database callcenter", stating that some other users are connected to template1; then it gets a fatal error when it tries to connect to that db (obviously!).
The strange matter (to me) is that some months ago I loaded the same 7.0.2 backup to version 7.2.1, and it worked perfectly.
Help...?!?!?
eperich
10-18-02, 08:28
It's amazing that this worked in 7.2.1

you connect to template1

the you try to create a database

\connect template1 callcenter
create database "callcenter" with encoding='SQL_ASCII';

When you create the database a 1:1 copy is made of template1
Regarding to this script you are in template1 when you try to attempt to create the new one

create a database test
write this in the backup file

\connect template1 callcenter
\connect test callcenter
create database "callcenter" with encoding='SQL_ASCII';

I think it should work this way
luca200
10-18-02, 14:43
There are some things I can't understand.
1) The dump is as PostgreSql created it. If it can't work that way, why is it like that?
2) I tried adding the \connect command you suggested, but I got the SAME error. (I simply added your \connect, not removing the connection to template1. Anyway, the new \connect is AFTER the old, of course.) I get the following response from PostgreSQL:

...
You are now connected to database template1 as user callcenter.
You are now connected to database test as user callcenter.
psql: path_to_restorefile:16:ERROR: CREATE DATABASE: source database "template1" is being accessed by other users
psql: path_to_restorefile:17: \connect: FATAL 1: Database "callcenter" does not exist in the system catalog.


3) You were right saying it was amazing it worked with 7.2.1. I tried again with 7.2.1, and now I get the same error. So it seems I made something different when it worked, the first time.

Thank you anyway.
eperich
10-19-02, 08:55
One question did you changed the template1 database?
luca200
10-19-02, 09:49
Originally posted by eperich
One question did you changed the template1 database?

Of course not.
As you can read in my first post, I just ran initdb.
luca200
10-19-02, 11:02
I tried launching psql connecting to template1, and inside there I made a 'create database xxx': it worked regularly.
So...?
It doesn't seem I can't create a db if I'm connected to template1....
eperich
10-20-02, 08:36
I have no idea whats going on there.

Have you installed both database versions on the same machine
maybe some version conflicts