Re: How to run a task continuously in the background

On 2019-07-17 12:34:41 +0100, Rory Campbell-Lange wrote: > We make extensive use of postgresql 'contacting' an external process, > but our use case involves many databases in a cluster rather than many > schemas. Also we don't have to deal with cancelling the external > process. We chose this archi

Re: pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)?

Thanks. Question closed. :) Virus-free. www.avast.com

Re: Rearchitecting for storage

On 2019-07-19 11:37:52 -0400, Matthew Pounsett wrote: > On Fri, 19 Jul 2019 at 11:25, Peter J. Holzer wrote: > > On 2019-07-19 10:41:31 -0400, Matthew Pounsett wrote: > > Okay.  So I guess the short answer is no, nobody really knows how to > > judge how much space is required for an u

Re: pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)?

On Fri, 2019-07-19 at 21:15 +0300, Alexandru Lazarev wrote: > I receive locking failure on pg_advisory_lock, I do deadlock condition and > receive following: > - - - > ERROR: deadlock detected > SQL state: 40P01 > Detail: Process 240828 waits for ExclusiveLock on advisory lock > [1167570,1682092

pg_advisory_lock lock FAILURE / What does those numbers mean (process 240828 waits for ExclusiveLock on advisory lock [1167570,16820923,3422556162,1];)?

Hi Community, I receive locking failure on pg_advisory_lock, I do deadlock condition and receive following: - - - ERROR: deadlock detected SQL state: 40P01 Detail: Process 240828 waits for ExclusiveLock on advisory lock [ *1167570,16820923,3422556162,1*]; blocked by process 243637. Process 243637 w

Re: Rearchitecting for storage

Matthew Pounsett writes: > [...] Is there any rule of thumb for making sure one has enough space > available for the upgrade? No, because it depends greatly on which version you are upgrading from and which version you are upgrading to etc. Perhaps you could carve out a slice of data, e.g. 1 GB

Re: Rearchitecting for storage

Matthew Pounsett writes: > On Thu, 18 Jul 2019 at 19:53, Rob Sargent wrote: > > Can you afford to drop and re-create those 6 indices? > > Technically, yes. I don't see any reason we'd be prevented from doing that. > But, rebuilding them will take a long time. That's a lot of downtime to incur

Re: Rearchitecting for storage

On Fri, 19 Jul 2019 at 11:25, Peter J. Holzer wrote: > On 2019-07-19 10:41:31 -0400, Matthew Pounsett wrote: > > Okay. So I guess the short answer is no, nobody really knows how to > > judge how much space is required for an upgrade? :) > > As I understand it, a pg_upgrade --link uses only negl

Re: Rearchitecting for storage

On 2019-07-19 10:41:31 -0400, Matthew Pounsett wrote: > Okay.  So I guess the short answer is no, nobody really knows how to > judge how much space is required for an upgrade?  :) As I understand it, a pg_upgrade --link uses only negligible extra space. It duplicates a bit of householding informat

Re: Rearchitecting for storage

On Thu, 18 Jul 2019 at 09:44, Matthew Pounsett wrote: > > I've recently inherited a database that is dangerously close to outgrowing > the available storage on its existing hardware. I'm looking for (pointers > to) advice on scaling the storage in a financially constrained > not-for-profit. > T

Re: Rearchitecting for storage

Hi Matt, On Fri, Jul 19, 2019 at 10:41:31AM -0400, Matthew Pounsett wrote: > On Fri, 19 Jul 2019 at 04:21, Luca Ferrari wrote: > > > > > This could be trivial, but any chance you can partition the table > > and/or archive unused records (at least temporarly)? A 18 TB table > > quite frankly soun

Re: Rearchitecting for storage

On Fri, 19 Jul 2019 at 04:21, Luca Ferrari wrote: > > This could be trivial, but any chance you can partition the table > and/or archive unused records (at least temporarly)? A 18 TB table > quite frankly sounds a good candidate to contain records no one is > interested in the near future. > Par

Re: Rearchitecting for storage

On Thu, 18 Jul 2019 at 21:59, Andy Colson wrote: > > > > Now might be a good time to consider splitting the database onto multiple > computers. Might be simpler with a mid-range database, then your plan for > the future is "add more computers". > Hmm... yes. Range partitioning seems like a pos

Re: Rearchitecting for storage

On Thu, 18 Jul 2019 at 19:53, Rob Sargent wrote: > > > > > That would likely keep the extra storage requirements small, but still > non-zero. Presumably the upgrade would be unnecessary if it could be done > without rewriting files. Is there any rule of thumb for making sure one > has enough sp

very high replay_lag on 3-node cluster

Hello, In my previous post[1] on this list I brought up an issue with long running checkpoints. I reduced checkpoint_timeout to a more reasonable value (15m down from 60m) and forced/immediate checkpoints now complete mostly in under a minute. This thread and another one[2] on the Clusterlabs mail

Re: Rearchitecting for storage

On Thu, Jul 18, 2019 at 10:09 PM Matthew Pounsett wrote: > That would likely keep the extra storage requirements small, but still > non-zero. Presumably the upgrade would be unnecessary if it could be done > without rewriting files. Is there any rule of thumb for making sure one has > enough