Thread: pg_restore encounter deadlock since PostgreSQL bringing up
Hi, While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL bringing up. I encounter pg_restore failure because of deadlock detected. postgres[2737]: [3-1] LOG: process 2737 detected deadlock while waiting for AccessExclusiveLock on relation 33337 of database 24577 after 1000.070 ms postgres[2737]: [3-2] STATEMENT: DROP SCHEMA public CASCADE; postgres[2737]: [4-1] err-1: deadlock detected postgres[2737]: [4-2] DETAIL: Process 2737 waits for AccessExclusiveLock on relation 33337 of database 24577; blocked by process 2720. postgres[2737]: [4-3] Process 2720 waits for AccessShareLock on relation 33344 of database 24577; blocked by process 2737. postgres[2737]: [4-4] Process 2737: DROP SCHEMA public CASCADE; postgres[2737]: [4-5] Process 2720: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL ELSE min_value END AS min_value, cache_value, is_cycled FROM zonepreprovisioningrules_id_seq postgres[2737]: [4-6] HINT: See server log for query details. postgres[2737]: [4-7] STATEMENT: DROP SCHEMA public CASCADE; I suspect competition between process that bringing up PostgreSQL and process drop schema by pg_restore. So my question is how to guarantee (e.g by inquiring some parameters from system tables? ) PostgreSQL is totally start up to accept drop schema via pg_restore? Brs. -- View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, Nov 17, 2015 at 3:24 PM, zh1029 <zh1029@sina.com> wrote:
Hi,
While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL
bringing up. I encounter pg_restore failure because of deadlock detected.
postgres[2737]: [3-1] LOG: process 2737 detected deadlock while waiting for
AccessExclusiveLock on relation 33337 of database 24577 after 1000.070 ms
postgres[2737]: [3-2] STATEMENT: DROP SCHEMA public CASCADE;
postgres[2737]: [4-1] err-1: deadlock detected
postgres[2737]: [4-2] DETAIL: Process 2737 waits for AccessExclusiveLock on
relation 33337 of database 24577; blocked by process 2720.
postgres[2737]: [4-3] Process 2720 waits for AccessShareLock on
relation 33344 of database 24577; blocked by process 2737.
postgres[2737]: [4-4] Process 2737: DROP SCHEMA public CASCADE;
postgres[2737]: [4-5] Process 2720: SELECT sequence_name,
start_value, increment_by, CASE WHEN increment_by > 0 AND max_value =
9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1
THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0
AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value =
-9223372036854775807 THEN NULL ELSE min_value END AS min_value,
cache_value, is_cycled FROM zonepreprovisioningrules_id_seq
postgres[2737]: [4-6] HINT: See server log for query details.
postgres[2737]: [4-7] STATEMENT: DROP SCHEMA public CASCADE;
I suspect competition between process that bringing up PostgreSQL and
process drop schema by pg_restore. So my question is how to guarantee (e.g
by inquiring some parameters from system tables? ) PostgreSQL is totally
start up to accept drop schema via pg_restore?
"pg_ctl -D <data-directory> status" command will let you know if the PostgreSQL cluster is up and running.
Are you sure, you do not have any other processes running while pg_restore process is running ? Whats the background of the process 2720 ?
Regards,
Venkata B N
Fujitsu Australia
On 11/16/2015 08:24 PM, zh1029 wrote: > Hi, > > While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL > bringing up. I encounter pg_restore failure because of deadlock detected. > > postgres[2737]: [3-1] LOG: process 2737 detected deadlock while waiting for > AccessExclusiveLock on relation 33337 of database 24577 after 1000.070 ms > postgres[2737]: [3-2] STATEMENT: DROP SCHEMA public CASCADE; > postgres[2737]: [4-1] err-1: deadlock detected > postgres[2737]: [4-2] DETAIL: Process 2737 waits for AccessExclusiveLock on > relation 33337 of database 24577; blocked by process 2720. > postgres[2737]: [4-3] Process 2720 waits for AccessShareLock on > relation 33344 of database 24577; blocked by process 2737. > postgres[2737]: [4-4] Process 2737: DROP SCHEMA public CASCADE; > postgres[2737]: [4-5] Process 2720: SELECT sequence_name, > start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = > 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 > THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 > AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = > -9223372036854775807 THEN NULL ELSE min_value END AS min_value, > cache_value, is_cycled FROM zonepreprovisioningrules_id_seq > postgres[2737]: [4-6] HINT: See server log for query details. > postgres[2737]: [4-7] STATEMENT: DROP SCHEMA public CASCADE; > > I suspect competition between process that bringing up PostgreSQL and > process drop schema by pg_restore. So my question is how to guarantee (e.g > by inquiring some parameters from system tables? ) PostgreSQL is totally > start up to accept drop schema via pg_restore? To me this: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL ELSE min_value END AS min_value, cache_value, is_cycled FROM zonepreprovisioningrules_id_seq looks strange. Can you look in the dump file and see where that is coming from? > > Brs. > > > > -- > View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > -- Adrian Klaver adrian.klaver@aklaver.com
Hi, pg_restore is executed by our own process actually, before that, our process connects the data base via ODBC to access database template1 see if PostgreSQL server is up. But pg_restore still failed after connect database I understand the result is same as pg_ctrl output, is that true? Thanks! -- View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146p5874157.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Unfortunately what's the process 2720 is unknown as no any else log to indicate it. It might because the debug level of PostgreSQL had been set too lower to show more. -- View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146p5874159.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi, Per my understanding, From the query which resulted in the deadlock “SELECT sequence_name, start_value, increment_by***********”, it appears that the query which holds the lock is related to sequence numbers. From our understanding it appears that, whenever there is a serial type of data member, PostgreSQL internally needs to keep track of sequence numbers across the dump and restore. Is it possible select query is triggered by PostgreSQL bringing up(to bring up internal database) instead of dump file? because from the log, it looks failed while doing DROP SCHEMA public CASCADE. -- View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146p5874160.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 11/16/2015 10:55 PM, zh1029 wrote:
> Unfortunately what's the process 2720 is unknown as no any else log to
> indicate it. It might because the debug level of PostgreSQL had been set too
> lower to show more.
if its a linux system, try...
ps uww -p 2720
logged on as root, and it should show you the command
--
john r pierce, recycling bits in santa cruz
zh1029 wrote: > While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL > bringing up. I encounter pg_restore failure because of deadlock detected. pg_restore will not deadlock with itself or system startup. Set log_connections to "on", then you will see who connected and caused the deadlock. Yours, Laurenz Albe
zh1029 <zh1029@sina.com> writes:
> Unfortunately what's the process 2720 is unknown as no any else log to
> indicate it. It might because the debug level of PostgreSQL had been set too
> lower to show more.
The query you quoted for 2720 looks exactly like one that pg_dump will
issue, cf dumpSequence(). What I think is going on here is that you've
got some cron job that aggressively connects to the database and runs a
pg_dump the moment you start it. It's unsurprising that that would
deadlock against a pg_restore -C.
regards, tom lane