Re: [HACKERS] Early locking option to parallel backup

2017-11-08 Thread Lucas B
On 11/06/2017 12:30 PM, Stephen Frost wrote: * Lucas (luca...@gmail.com) wrote: pg_dump was taking more than 24 hours to complete in one of my databases. I begin to research alternatives. Parallel backup reduced the backup time to little less than a hour, but it failed almost every time because

Re: [HACKERS] Early locking option to parallel backup

2017-11-06 Thread Lucas B
Em 05/11/2017 21:09, Andres Freund escreveu: On 2017-11-05 17:38:39 -0500, Robert Haas wrote: On Sun, Nov 5, 2017 at 5:17 AM, Lucas wrote: The patch creates a "--lock-early" option which will make pg_dump to issue shared locks on all tables on the backup TOC on each

Re: [HACKERS] Early locking option to parallel backup

2017-11-06 Thread Stephen Frost
Lucas, * Lucas (luca...@gmail.com) wrote: > pg_dump was taking more than 24 hours to complete in one of my databases. I > begin to research alternatives. Parallel backup reduced the backup time to > little less than a hour, but it failed almost every time because of > concurrent queries that

Re: [HACKERS] Early locking option to parallel backup

2017-11-06 Thread Andres Freund
On 2017-11-05 22:43:34 -0500, Tom Lane wrote: > > IIUC the problem here is that even though a lock is already > > held by the main backend an independent locker's request will prevent > > the on-demand lock by the dump worker from being granted. It seems to > > me the correct fix here would be to

Re: [HACKERS] Early locking option to parallel backup

2017-11-06 Thread Robert Haas
On Mon, Nov 6, 2017 at 4:43 AM, Tom Lane wrote: > I wonder if we couldn't somehow repurpose the work that was done for > parallel workers' locks. Lots of security-type issues to be handled > if we're to open that up to clients, but maybe it's solvable. For > instance, maybe

Re: [HACKERS] Early locking option to parallel backup

2017-11-05 Thread Tom Lane
Andres Freund writes: > Well, the current approach afaics requires #relations * 2 locks, whereas > acquiring them in every worker would scale that with the number of > workers. Yeah, that's gonna be a problem with this proposal. > IIUC the problem here is that even though a

Re: [HACKERS] Early locking option to parallel backup

2017-11-05 Thread Andres Freund
On 2017-11-05 17:38:39 -0500, Robert Haas wrote: > On Sun, Nov 5, 2017 at 5:17 AM, Lucas wrote: > > The patch creates a "--lock-early" option which will make pg_dump to issue > > shared locks on all tables on the backup TOC on each parallel worker start. > > That way, the

Re: [HACKERS] Early locking option to parallel backup

2017-11-05 Thread Robert Haas
On Sun, Nov 5, 2017 at 5:17 AM, Lucas wrote: > The patch creates a "--lock-early" option which will make pg_dump to issue > shared locks on all tables on the backup TOC on each parallel worker start. > That way, the backup has a very small chance of failing. When it does, >

Re: [HACKERS] Early locking option to parallel backup

2017-11-05 Thread Michael Paquier
On Sun, Nov 5, 2017 at 7:17 PM, Lucas wrote: > The patch creates a "--lock-early" option which will make pg_dump to issue > shared locks on all tables on the backup TOC on each parallel worker start. > That way, the backup has a very small chance of failing. When it does, >