Re: [GENERAL] Sequences / Replication

[Jonathan Eastgate]

And further to my last post - another post in the forums related to this:

https://devon.so/2015/02/06/as-tale-of-sequences-and-postgresql-replication-9/

Thanks.

*Jonathan J. Eastgate*
Chief Technology Officer | simPRO Software Group
Ph: 1300 139 467+61 7 3147 8777

<http://simprogroup.com/au/email-redirect>

Keep up to date with simPRO at: http://simprogroup.com/blog
The contents of this email are subject to our email disclaimer
<http://simprogroup.com/au/legal/email-confidentiality-notice>.


On Thu, Oct 20, 2016 at 6:03 PM, Jonathan Eastgate <
jonathan.eastg...@simpro.co> wrote:

> Hi everyone.
>
> We're seeing some odd behaviour from a PostgreSQL group - one running as
> primary and the other as a hot slave using streaming replication.
>
> When a failover event occurs and we switch to the hot slave as primary
> sequences in tables jump by 33 - so where the last number allocated in the
> sequence was 100 prior to failover once adding the next entry the sequence
> will produce the number 133.
>
> https://stackoverflow.com/questions/38450394/postgresql-
> sequence-jump-30-or-33-number-with-cache-equals-1
>
> I've found the following post in the forums - but any advice on how to
> resolve or counter this would be appreciated.
>
> Thanks in advance.
>
>
> *Jonathan J. Eastgate*
> Chief Technology Officer | simPRO Software Group
> Ph: 1300 139 467+61 7 3147 8777
>
> <http://simprogroup.com/au/email-redirect>
>
> Keep up to date with simPRO at: http://simprogroup.com/blog
> The contents of this email are subject to our email disclaimer
> <http://simprogroup.com/au/legal/email-confidentiality-notice>.
>
>

-- 
--

[GENERAL] Sequences / Replication

[Jonathan Eastgate]

Hi everyone.

We're seeing some odd behaviour from a PostgreSQL group - one running as
primary and the other as a hot slave using streaming replication.

When a failover event occurs and we switch to the hot slave as primary
sequences in tables jump by 33 - so where the last number allocated in the
sequence was 100 prior to failover once adding the next entry the sequence
will produce the number 133.

https://stackoverflow.com/questions/38450394/postgresql-sequence-jump-30-or-33-number-with-cache-equals-1

I've found the following post in the forums - but any advice on how to
resolve or counter this would be appreciated.

Thanks in advance.


*Jonathan J. Eastgate*
Chief Technology Officer | simPRO Software Group
Ph: 1300 139 467+61 7 3147 8777



Keep up to date with simPRO at: http://simprogroup.com/blog
The contents of this email are subject to our email disclaimer
.

-- 
--

Re: [GENERAL] BDR Cluster vs DB Config

[Jonathan Eastgate]

Thanks guys.

Very helpful - I was thinking we may need to look at moving to schemas
instead of individual db's.

I assume that once BDR is enabled on a database that any additional schemas
added post config are automatically included in BDR replication?

And so you see any issues having potentially 200 schemas within the DB -
performance or replication wise?

Thanks in advance.




*Jonathan J. Eastgate*
Chief Technology Officer | simPRO Software Group
Ph: 1300 139 467+61 7 3147 8777

<http://simprogroup.com/email-signature-promo/>

Keep up to date with simPRO at: http://simprogroup.com/blog
The contents of this email are subject to our email disclaimer
<http://simprogroup.com/au/legal/email-confidentiality-notice>.


On Wed, Jul 20, 2016 at 5:18 PM, Craig Ringer  wrote:

> On 20 July 2016 at 13:22, Jonathan Eastgate 
> wrote:
>
>> Hi everyone.
>>
>> We've been testing BDR on and off for the last 2 years and are keen to
>> start looking at implementing it in production as it seems 0.93 has
>> resolved most of the issues we faced with it in the early days.
>>
>> However there is still one item that makes it a difficult proposition...
>>
>> DSN config per database.
>>
>> Is there any way to configure BDR on a cluster wide basis so that all
>> DB's on a cluster are replicated via BDR instead of having to configure a
>> connection for each DB we want to replicate?
>>
>
> No.
>
> Not only that, but if you're replicating lots of databases between
> PostgreSQL instances you're likely to start facing some performance
> problems around the sheer number of background workers required, the way
> WAL needs to be processed multiple times, etc.
>
> If you're using this for multi-tenancy or similar, see if you can isolate
> by schema not by database.
>
>
>> The problem we have is over 20 clusters with about 200 DB's per cluster
>> and growing constantly so this would make deploying BDR a painful process -
>> if we had to add a connection for each existing DB and then every new DB.
>>
>
> Yeah. That's going to cause you pain even aside from the management of it.
>
>
>
>> Is there a way around this or are there plans to make this type of config
>> available?
>>
>
> There are no plans to automate this configuration. BDR works at a database
> level, with the exception of bdr_init_copy bringing up all BDR-enabled
> databases on the join target node as a one-time operation at setup.
>
> Maybe once we eventually have some kind of answer for how to replicate
> instance-global DDL that affects shared catalogs, like database
> creation/drop, user creation/drop, etc, then it might make sense to extend
> BDR or its successor to do this. But not at the moment.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

-- 
--

[GENERAL] BDR Cluster vs DB Config

[Jonathan Eastgate]

Hi everyone.

We've been testing BDR on and off for the last 2 years and are keen to
start looking at implementing it in production as it seems 0.93 has
resolved most of the issues we faced with it in the early days.

However there is still one item that makes it a difficult proposition...

DSN config per database.

Is there any way to configure BDR on a cluster wide basis so that all DB's
on a cluster are replicated via BDR instead of having to configure a
connection for each DB we want to replicate?

The problem we have is over 20 clusters with about 200 DB's per cluster and
growing constantly so this would make deploying BDR a painful process - if
we had to add a connection for each existing DB and then every new DB.

Is there a way around this or are there plans to make this type of config
available?

Thanks in advance.

*Jonathan J. Eastgate*
Chief Technology Officer | simPRO Software Group
Ph: 1300 139 467+61 7 3147 8777



Keep up to date with simPRO at: http://simprogroup.com/blog
The contents of this email are subject to our email disclaimer
.

-- 
--