queries timeout during backup postgres database

akp geek <akpgeek@gmail.com> writes:

Hi All -

? ? ? ? ? ? ? ? During the backup of the database, we experience
timeout on queries. any one has experienced the same? Is there any
parameters in the postgressql.conf that I have to pay attention to?
Can you please help

What backup method and what statement is running if any when canceled?

Regards

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 305.321.1144

I am using pg_dump to back up each schema in the database and there 6
schemas in the database.

thanks

On Mon, Jan 9, 2012 at 1:26 PM, Jerry Sievers <gsievers19@comcast.net>wrote:

On Mon, Jan 9, 2012 at 11:05 AM, akp geek <akpgeek@gmail.com> wrote:

Hi All -

                During the backup of the database, we experience timeout on
queries. any one has experienced the same? Is there any parameters in the
postgressql.conf that I have to pay attention to? Can you please help

statement_timeout is the likely culprit.

On Monday, January 09, 2012 11:16:46 am akp geek wrote:

I am using pg_dump to back up each schema in the database and there 6
schemas in the database.

Do you have replication set up and if so are running the above against the
master or the standby?

thanks

--
Adrian Klaver
adrian.klaver@gmail.com

I have statement_timeout as commented in the postgresql.conf. I just
checked it..

Thanks

On Mon, Jan 9, 2012 at 2:31 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:

I have the replication set up and I am running the pg_dump against the
master

Thanks

On Mon, Jan 9, 2012 at 2:33 PM, Adrian Klaver <adrian.klaver@gmail.com>wrote:

On Mon, Jan 9, 2012 at 12:37 PM, akp geek <akpgeek@gmail.com> wrote:

I have  statement_timeout  as commented in the postgresql.conf. I just
checked it..

statement timeout, like so many things, can be set per user or per
database. Connect as the backup use and issue this statement:

show statement_timeout;

On Mon, Jan 9, 2012 at 1:06 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

On Mon, Jan 9, 2012 at 12:37 PM, akp geek <akpgeek@gmail.com> wrote:

I have  statement_timeout  as commented in the postgresql.conf. I just
checked it..

statement timeout, like so many things, can be set per user or per
database.  Connect as the backup use and issue this statement:

show statement_timeout;

The other possibility is that you're getting a network timeout while
waiting for a response to a select statement. Most network timeouts
are ~2 hours, but if you've got a firewall look at timeouts. Any
timeouts should be disabled to troubleshoot.

vacuum_cost_delay = 2ms ,
I set it as 20ms. last night I did not see any timeouts during the back up.
But I don't understand that. Thanks a lot for the support

On Mon, Jan 9, 2012 at 7:17 PM, Scott Marlowe <scott.marlowe@gmail.com>wrote:

Did you connect as the backup user to the db and check what the
statement_timeout is from that perspective? It's a locally settable
var by db and by user, so it's still possible you'll get bit by this
again if it's set for the backup user or just for that db.

On Tue, Jan 10, 2012 at 1:13 PM, akp geek <akpgeek@gmail.com> wrote:

vacuum_cost_delay = 2ms ,
I set it as 20ms. last night I did not see any timeouts during the back up.
But I don't understand that. Thanks a lot for the support

On Mon, Jan 9, 2012 at 7:17 PM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Mon, Jan 9, 2012 at 1:06 PM, Scott Marlowe <scott.marlowe@gmail.com>
wrote:

On Mon, Jan 9, 2012 at 12:37 PM, akp geek <akpgeek@gmail.com> wrote:

I have  statement_timeout  as commented in the postgresql.conf. I just
checked it..

statement timeout, like so many things, can be set per user or per
database.  Connect as the backup use and issue this statement:

show statement_timeout;

The other possibility is that you're getting a network timeout while
waiting for a response to a select statement.  Most network timeouts
are ~2 hours, but if you've got a firewall look at timeouts.  Any
timeouts should be disabled to troubleshoot.

--
To understand recursion, one must first understand recursion.