pg_restore 7.4.7 locks itself out - Mailing list pgsql-general

Hi all,

I'm trying to restore one of our production databases on our development
system, but restore locks itself out.
The symptoms: restoring goes fine up to a certain point. Reaching that
point the database is idle, and apparently waiting on a lock. Server
load is minimal.

As this is a newly created database that hasn't been configured in any
of our applications yet, pg_restore really is the only app connecting to
it. I restore on the server, so problems with network, nfs etc. are
ruled out.

The restore command used: pg_restore -U postgres -d vh3_live vh3.dump
The dump file is in "custom" format, and about 70MB in size.

Here's some output (input for you guys):

template1@[local] SQL> select version();
                                                 version

-------------------------------------------------------------------------------------------------------
  PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc
(GCC) 3.3.5 (Debian 1:3.3.5-12)
(1 row)

template1@[local] SQL> select * from pg_locks ;
  relation | database | transaction |  pid  |       mode       | granted
----------+----------+-------------+-------+------------------+---------
     16759 |        1 |             | 15083 | AccessShareLock  | t
           |          |    74701637 | 15092 | ExclusiveLock    | t
  77680019 | 77680006 |             | 15092 | AccessShareLock  | t
  77680019 | 77680006 |             | 15092 | RowExclusiveLock | t
           |          |    74701638 | 15083 | ExclusiveLock    | t
(5 rows)

Below these pid's indeed seem to be causing a lock:

alban:blackmag * ps aux | grep postg
postgres  4521  0.0  0.0 42212 2464 ?        S    Apr07   0:00
/usr/lib/postgresql/bin/postmaster -D /var/lib/postgres/data
postgres  4527  0.0  0.0  7908 1864 ?        S    Apr07   0:00 postgres:
stats buffer process
postgres  4528  0.0  0.0  7492 1440 ?        S    Apr07   0:00 postgres:
stats collector process
alban    15076  0.0  0.0  6392 1920 pts/8    S+   14:11   0:00
/usr/lib/postgresql/bin/psql -U postgres -d template1
postgres 15083  0.0  0.0 43020 3772 ?        S    14:11   0:00 postgres:
postgres template1 [local] idle
alban    15085  0.0  0.0  6724 2184 pts/7    S+   14:11   0:00
/usr/lib/postgresql/bin/pg_restore -U postgres -d vh3_live
vh30_20060410.dump
postgres 15092  0.0  0.3 43692 12924 ?       D    14:11   0:00 postgres:
postgres vh3_live [local] INSERT

The data in the table it's trying to restore does contain circular
references. I imagine this could cause such trouble. It's a pain to
remove records too, they don't cascade...

Is there some way to 'unlock' my restore?

I suppose this may be fixed in a newer version, but our sysadmin'd
prefer to stay with versions packaged by the distributor (Debian in this
case). OTOH, I would like to test this database on 8.1 some time (this
is our development DB after all), so this could be a good opportunity...

Regards,

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
    7500 AK Enschede

// Integrate Your World //