Pivotal Knowledge Base


pg_dump Times Out because of statement_timeout Setting


  • Pivotal Greenplum Database (GPDB) 4.3.x
  • Operating System- Red Hat Enterprise Linux 6.x


This article talks about preventing pg_dump from timing out if statement_timeout is set.


When running pg_dump for a very large database (VLDB), the pg_dump might time out if a particular statement runs more than the statement_timeout value set in the postgresql.conf file

[gpadmin@node4 gpseg-1]$ grep -i statement_timeout postgresql.conf
statement_timeout = 3600000 # in milliseconds, 0 is disabled

If we run a pg_dump and a particular statement (say a big table) inside the pg_dump script takes more than one hour it would fail with the below error

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: canceling statement due to statement timeout


Set statement_timeout locally using PGOPTIONS in command-line with the pg_dump command. See example below

PGOPTIONS="-c statement_timeout=0" pg_dump -f /data/backup/dumps gpadmin

PGOPTIONS environment variable can pass parameters to the utility so that parameters can be adjusted for this utility run only. The statement_timeout=0 will never timeout the pg_dump statements and is useful for backups that take a very long time.


Powered by Zendesk