Thread: pg_basebackup blocking all queries
Greetings, I have a 4 server postgresql-9.1.3 cluster (one master doing streaming replication to 3 hot standby servers). All of them are running Fedora-16-x86_64. Last Friday I upgraded the entire cluster from Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. I'm finding that I cannot runpg_basebackup at all, or it blocks all SQL queries from running until pg_basebackup has completed (and the load on the box just takes off to over 75.00). By "blocks" I mean that any query that is submitted just hangs and does not return at all until pg_basebackup has stopped. I'm assuming that this isn't expected behavior, so I'm rather confused on what is going on. The command that I'm issuing is: pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres Can someone provide some guidance on how to debug this? thanks!
Do the queries here help? http://wiki.postgresql.org/wiki/Lock_Monitoring On Tue, May 22, 2012 at 12:42 PM, Lonni J Friedman <netllama@gmail.com> wrote: > Greetings, > I have a 4 server postgresql-9.1.3 cluster (one master doing streaming > replication to 3 hot standby servers). All of them are running > Fedora-16-x86_64. Last Friday I upgraded the entire cluster from > Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. > > I'm finding that I cannot runpg_basebackup at all, or it blocks all > SQL queries from running until pg_basebackup has completed (and the > load on the box just takes off to over 75.00). By "blocks" I mean > that any query that is submitted just hangs and does not return at all > until pg_basebackup has stopped. I'm assuming that this isn't > expected behavior, so I'm rather confused on what is going on. The > command that I'm issuing is: > pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres > > Can someone provide some guidance on how to debug this? > > thanks! > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- To understand recursion, one must first understand recursion.
Thanks for your reply. Unfortunately, those queries don't shed any light no the problem. The first two return 0 rows, and the third just returns 12 rows all associated with the query itself, rather than anything else. Any other suggestions? On Tue, May 22, 2012 at 2:56 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > Do the queries here help? > > http://wiki.postgresql.org/wiki/Lock_Monitoring > > On Tue, May 22, 2012 at 12:42 PM, Lonni J Friedman <netllama@gmail.com> wrote: >> Greetings, >> I have a 4 server postgresql-9.1.3 cluster (one master doing streaming >> replication to 3 hot standby servers). All of them are running >> Fedora-16-x86_64. Last Friday I upgraded the entire cluster from >> Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. >> >> I'm finding that I cannot runpg_basebackup at all, or it blocks all >> SQL queries from running until pg_basebackup has completed (and the >> load on the box just takes off to over 75.00). By "blocks" I mean >> that any query that is submitted just hangs and does not return at all >> until pg_basebackup has stopped. I'm assuming that this isn't >> expected behavior, so I'm rather confused on what is going on. The >> command that I'm issuing is: >> pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres >> >> Can someone provide some guidance on how to debug this? >> >> thanks!
I'm still plagued by this. Immediately before starting a basebackup the load on my server is 1.00 or less. Within a few minutes of starting the basebackup, the load climbs steadily to 30+ and anything trying to write to the database just sits for minutes at a time, with overall performance on any query (read or write) being horrible (seconds to minutes). As soon as the basebackup completes, perf returns to normal (and the load drops back down to 1.00 or less). How can I debug what's wrong? On Tue, May 22, 2012 at 3:20 PM, Lonni J Friedman <netllama@gmail.com> wrote: > Thanks for your reply. Unfortunately, those queries don't shed any > light no the problem. The first two return 0 rows, and the third just > returns 12 rows all associated with the query itself, rather than > anything else. > > Any other suggestions? > > > On Tue, May 22, 2012 at 2:56 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> Do the queries here help? >> >> http://wiki.postgresql.org/wiki/Lock_Monitoring >> >> On Tue, May 22, 2012 at 12:42 PM, Lonni J Friedman <netllama@gmail.com> wrote: >>> Greetings, >>> I have a 4 server postgresql-9.1.3 cluster (one master doing streaming >>> replication to 3 hot standby servers). All of them are running >>> Fedora-16-x86_64. Last Friday I upgraded the entire cluster from >>> Fedora-15 with postgresql-9.0.6 to Fedora-16 with postgresql-9.1.3. >>> >>> I'm finding that I cannot runpg_basebackup at all, or it blocks all >>> SQL queries from running until pg_basebackup has completed (and the >>> load on the box just takes off to over 75.00). By "blocks" I mean >>> that any query that is submitted just hangs and does not return at all >>> until pg_basebackup has stopped. I'm assuming that this isn't >>> expected behavior, so I'm rather confused on what is going on. The >>> command that I'm issuing is: >>> pg_basebackup -v -D /mnt/backups/backups/tmp0 -x -Ft -U postgres >>> >>> Can someone provide some guidance on how to debug this? >>> >>> thanks!