Обсуждение: 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

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

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.

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.