- 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.