Thread: pg_restore 7.4.7 locks itself out
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 //
Alban Hertroys <alban@magproductions.nl> writes: > postgres 15092 0.0 0.3 43692 12924 ? D 14:11 0:00 postgres: > postgres vh3_live [local] INSERT This process is not blocked on a lock: it's waiting for disk I/O. Thoughts that come to mind include (1) it's going fine and you're not patient enough; (2) something wrong with your disk drive; (3) DB is mounted across NFS and you're having network problems. > 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). If your sysadmin wants to use 7.4.7 rather than 7.4.<latest>, he needs swift application of a cluestick. I'll grant that there might be application-compatibility reasons to stay on 7.4.*, but not to avoid being up to date in that release series. See http://developer.postgresql.org/docs/postgres/release-7-4-12.html and following pages. regards, tom lane
Tom Lane wrote: > Alban Hertroys <alban@magproductions.nl> writes: > >>postgres 15092 0.0 0.3 43692 12924 ? D 14:11 0:00 postgres: >>postgres vh3_live [local] INSERT > > > This process is not blocked on a lock: it's waiting for disk I/O. > > Thoughts that come to mind include (1) it's going fine and you're not > patient enough; (2) something wrong with your disk drive; (3) DB is > mounted across NFS and you're having network problems. Really? I've been waiting for it to finish ever since, amounting to almost 4 hours now. It doesn't seem to have progressed one bit since it started. Well, I'll let it run overnight and see what has happened by tomorrow morning. As for points (2) and (3); I was logged in on the machine where the database lives, which is AFAIK on a SATA RAID configuration of some type (don't know the details). If it's a mirror (and I believe it is), that kind of makes the bad-disk scenario not too plausible, and I'd think we would have noticed something like that in other ways too. That server handles most of our diskless machines - one of which I'm using (No, I wasn't restoring from there...). >>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). > > If your sysadmin wants to use 7.4.7 rather than 7.4.<latest>, he needs > swift application of a cluestick. I'll grant that there might be > application-compatibility reasons to stay on 7.4.*, but not to avoid > being up to date in that release series. See > http://developer.postgresql.org/docs/postgres/release-7-4-12.html > and following pages. Well, in this case it seems like the debian package maintainers could use a thorough bat with the cluestick - but I'm biased against debian, so that could be prejudice... <rant> OTOH, our sysadmin can be rather stubborn - I'm in the process of convincing him that it's a bad idea to rely on filesystem backups of the pg data directory for restoring a DB, or at least stop the database while doing so. I'd much rather he'd use dumps, but apparently that takes disk space - no matter how little, apparently. His response is that he's restored the databases twice already this way, and things didn't break - I wonder how he can be sure about that... Trouble is, if he does break the database, I'll be the one to receive the complaints :( Stubborn he is... </rant> 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 //
Alban Hertroys wrote: > > If your sysadmin wants to use 7.4.7 rather than 7.4.<latest>, he > > needs swift application of a cluestick. I'll grant that there > > might be application-compatibility reasons to stay on 7.4.*, but > > not to avoid being up to date in that release series. See > > http://developer.postgresql.org/docs/postgres/release-7-4-12.html > > and following pages. > > Well, in this case it seems like the debian package maintainers could > use a thorough bat with the cluestick - but I'm biased against > debian, so that could be prejudice... This problem is going to be addressed. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On 2006-04-10, Alban Hertroys <alban@magproductions.nl> wrote: > Tom Lane wrote: >> Alban Hertroys <alban@magproductions.nl> writes: >> >>>postgres 15092 0.0 0.3 43692 12924 ? D 14:11 0:00 postgres: >>>postgres vh3_live [local] INSERT >> >> This process is not blocked on a lock: it's waiting for disk I/O. >> >> Thoughts that come to mind include (1) it's going fine and you're not >> patient enough; (2) something wrong with your disk drive; (3) DB is >> mounted across NFS and you're having network problems. > > Really? I've been waiting for it to finish ever since, amounting to > almost 4 hours now. It doesn't seem to have progressed one bit since it > started. Well, I'll let it run overnight and see what has happened by > tomorrow morning. Notice the "INSERT" there. For a restore, you'd expect it to be "COPY", _unless_ you used the -d option to pg_dump (this is a common mistake to make, given that all the other utilities use -d to specify the database name). Restoring an inserts dump is _SLOW_ to the point of being glacial, because (unless it's a text dump and you edited in BEGIN/COMMIT statements yourself) it'll be committing each row individually, which limits the restore speed to a couple of hundred rows/sec unless you have evil (or battery-backed) write caching or you've turned fsync off. COPY (which pg_dump will generate if you didn't use -d) runs two or three orders of magnitude faster. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
Andrew - Supernews wrote:
> On 2006-04-10, Alban Hertroys <alban@magproductions.nl> wrote:
> Notice the "INSERT" there. For a restore, you'd expect it to be "COPY",
> _unless_ you used the -d option to pg_dump (this is a common mistake to
> make, given that all the other utilities use -d to specify the database
> name).
That explains a lot, thanks. Looking at my command history, it does
indeed include "-d <database name>".
I was wondering why it was waiting on an insert, would never have
guessed that my dump was made that way... It also explains why I was
seeing locks this way.
This must be a very common mistake, isn't there some way to prevent this
from happening in the future?
> Restoring an inserts dump is _SLOW_ to the point of being glacial, because
Yeah, I know. Luckily that restore turned out to have finished this
morning, so everything is alright. I don't need this dump for restoring
anything serious, luckily.
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 //