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@xxxxxxxxxxxxxxxxx
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 //
