Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Location
    Italy
    Posts
    9

    Unanswered: Trouble upgrading to 7.2.3

    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:

    Code:
    \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...?!?!?

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Problem

    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
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Oct 2002
    Location
    Italy
    Posts
    9
    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:
    Code:
    ...
    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.

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    strange

    One question did you changed the template1 database?
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  5. #5
    Join Date
    Oct 2002
    Location
    Italy
    Posts
    9

    Re: strange

    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.

  6. #6
    Join Date
    Oct 2002
    Location
    Italy
    Posts
    9

    Another thing

    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....

  7. #7
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    very strange

    I have no idea whats going on there.

    Have you installed both database versions on the same machine
    maybe some version conflicts
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •