Pivotal Knowledge Base

Follow

pg_dump times out because of statement_timeout setting

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x
Others  

Purpose

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

Cause

When running pg_dump for a very large database (VLDB), the pg_dump might time out if a particular statement runs more that 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

Procedure

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.

Comments

Powered by Zendesk