I have two postgres 7.4.6 databases, running on different hosts (Linux
2.6.11 kernel), and I'm copying data from one to the other as follows:
psql -h 10.3.1.154 mydb -U username -c "\copy sometable to stdout" | \
psql mydb -U username -c "\copy sometable from stdin"
Such copies are pretty frequent, and have always worked well.
Today, the processes doing the copy have been stuck for a few
hours. ps axw says
21167 ? S 0:00 /usr/local/pgsql/bin/psql -h 10.3.1.154 mydb -U username -c \copy
sometable to stdout
21168 ? S 0:00 /usr/local/pgsql/bin/psql mydb -U username -c \copy sometable from stdin
21169 ? S< 0:00 postgres: risdba ris [local] COPY
The amount of data is small, about 13,000 rows, an average of 150-200
bytes each.
pg_locks shows this on the local host:
table | transaction | pid | mode | granted
---------------+-------------+-------+---------------------+---------
...
sometable | | 21169 | RowExclusiveLock | t
...
(I joined pg_locks with other catalog tables to get the table name.)
One other pid is waiting to get an AccessExclusiveLock on sometable,
but that's it. pg_locks doesn't have any other entries with granted =
f.
On the remote host, I see no sign of a COPY going on in pg_locks, (no lock
of any kind for sometable).
So it looks like the copy from stdin completed, as far as the remote
db is concerned, but that the process reading from the remote db,
21167, doesn't realize this, and the copy from process (21168) is
simply waiting for more data.
The postgres logs don't indicate any trouble on either host around the
time that the copy starts, or for the next few minutes.
Why is process 21167 stuck?
Jack Orenstein