Thread: queries timeout during backup postgres database
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
Regards
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:
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 anyWhat backup method and what statement is running if any when canceled?
> parameters in the postgressql.conf that I have to pay attention to?
> Can you please help
> 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
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:
statement_timeout is the likely culprit.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
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 Monday, January 09, 2012 11:16:46 am akp geek wrote:Do you have replication set up and if so are running the above against the
> I am using pg_dump to back up each schema in the database and there 6
> schemas in the database.
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.
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:
The other possibility is that you're getting a network timeout whileOn 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;
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.