Thread: statement_timeout and pg_dump

Hello,

I've recently set up statement_timeout setting in postgresql.conf to
terminate long running queries from web clients. When I run long
maintance queries (that should not be terminated) like VACUUM FULL in
psql client, I can specify per session setting like `SET
statement_timeout TO 0` not to let it fail because of the above
timeout.

But it seems that it's not possible to do long running pg_dump in this
situation because it obviously fails when reaches timeout time. So are
there any ways to increase timeout time for the dump procedure?

Thanks,
Ivan Zolotukhin

Ivan Zolotukhin wrote:
> Hello,
>
> I've recently set up statement_timeout setting in postgresql.conf to
> terminate long running queries from web clients. When I run long
> maintance queries (that should not be terminated) like VACUUM FULL in
> psql client, I can specify per session setting like `SET
> statement_timeout TO 0` not to let it fail because of the above
> timeout.
>
> But it seems that it's not possible to do long running pg_dump in this
> situation because it obviously fails when reaches timeout time. So are
> there any ways to increase timeout time for the dump procedure?

Yes, you can do:

    PGOPTIONS="-c statement_timeout=0" pg_dump test

However, I think you are better using ALTER USER to set
statement_timeout for specific users and allow system operations to run
with the default timeout of zero/disabled.

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +