Re: [HACKERS] Pglogical questions and problems

[Petr Jelinek]

On 14/04/16 20:46, Robert Haas wrote:

I believe
logical replication is a fundamental database technology that should
be considered just as much within the score of the core product as
physical replication, parallel query, or UPSERT.


Agreed, I believed we need this for very long time as well (pglogical is 
not my first or even second logical replication project).





At present, I don't understand why we would do sharding via FDWs, i.e. an
out-of-core solution and yet replication as an in-core solution. Sharding
desires/requires a single system image, so tight coupling is sensible (for
example, defining a distribution key column on a distributed table). For
replication between disparate loosely coupled systems, tight coupling is
exactly what you do not want. So doing it that way round would give an an
out-of-core solution for something that is best done in-core and an in-core
solution for something best done out-of-core.


First, I think that replication can be either loosely-coupled or
tightly-coupled.  There are interesting cases with intermittently
connected networks where you really don't want too much coupling, and
then there are cases where you are doing load-balancing across a
cluster and tight coupling is fine, even desirable.   Similarly,
although I agree that a sharding solution intrinsically requires
fairly tight coupling, I think that one of the strengths of FDWs is
that they do not.  I'm not very interested in seeing a sharding
solution in PostgreSQL that limits what you can do to a particular
network topology and enforces tight coupling whether you want it or
not.  I'm more interested in seeing how we can build something that
*permits* a tightly-coupled system but also lets people build other
kinds of systems if they wish.


I think we need both tightly and loosely coupled and I think we should 
aim to solution that will make it reasonably possible to do both, ie 
without reimplementing whole thing again to get the other variant. In 
pglogical I was more concerned with loosely-coupled use-cases btw. 
Mainly because I've seen more of them in the wild and also they are more 
interesting for me personally (partially because many of the tightly 
coupled use-cases can be solved by physical replication). I don't think 
there is anything there that would fundamentally prevent us building 
something that can work for both scenarios though.


About doing things with SQL. I think some stuff would be better done 
using SQL personally, like the nodes table, since I dislike the fact 
that it's not shared catalog currently. But many other things I'd prefer 
to manage using functions at least in the first iteration. I think it's 
preferable to have simple function interface and make things work 
correctly and then maybe try to replace it with DDL where it makes sense 
because DDL just needs much more boiler plate but otherwise is mostly 
mechanical. One thing that worries me with SQL is that we do have some 
history of bike-shedding any SQL syntax that's not part of standard to 
death halting the actual feature development for prolonged time periods 
as a result.


Finally a side note about sharding - I have strong believe that sharding 
needs to be tightly coupled to be effective and maintainable in production.


--
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Petr Jelinek]

On 14/04/16 20:14, Robert Haas wrote:

On Wed, Apr 13, 2016 at 8:54 PM, Craig Ringer  wrote:

On 14 April 2016 at 00:48, Robert Haas  wrote:

I think this would be a good topic to discuss at PGCon.


I'm not going to be able to be at PGCon, and I don't think Petr is either.
That's unfortunate in terms of planning for this.


That's a bummer.  I suppose you won't be at NYC either?



No NYC either.

--
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Craig Ringer]

On 15 April 2016 at 02:14, Robert Haas  wrote:


> That's a bummer.  I suppose you won't be at NYC either?
>

I won't. I'm not sure about Petr.

It's over 24h travel, and costly, from Perth. So conference attendance is
unfortunately something I have to pick and choose about.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Pglogical questions and problems

[Robert Haas]

On Thu, Apr 14, 2016 at 11:26 AM, Simon Riggs  wrote:
> 1) "more deeply into core"
> I'm open to doing that for some parts of the code, if there is benefit. At
> present, an extension has exactly the same attributes as an in-core
> solution, so I don't currently see any benefit in doing so. Could you
> explain what you see?
>
> 2) "SQL syntax"
> I'm not sure what SQL syntax would give us. I know what we would lose, which
> is the ability to implement new and interesting features as extensions
> before putting them into core. That doesn't strike me as a benefit, so
> please explain.

Lots of things start out as extensions but then we decide that they
are important enough that they should be part of the core product.
For example, text search started out in contrib, but then we moved it
to core.  When things are in core, they can have their own DDL, which
I think is an ease-of-use benefit.  Also, they become accessible as
infrastructure for other code that gets written later.  If there were
no benefits of putting features in core, we wouldn't put anything in
core, but of course there are such benefits.

It is absolutely wrong to say that you would "lose the ability to
implement new and interesting features as extensions before putting
them into core".  To the contrary, as we add things to core, it
becomes possible to write more and more interesting extensions.  For
example, the availability of background workers has opened up all
kinds of interesting possibilities for extensions that didn't exist
before; in fact, that's why Alvaro created the feature.  Similarly, a
lot of the code that I and others wrote for parallel query has been
used by other people to do interesting things - and it was one of the
goals of the project to make that sort of thing possible.  I believe
logical replication is a fundamental database technology that should
be considered just as much within the score of the core product as
physical replication, parallel query, or UPSERT.  I held and publicly
expressed that belief on my blog before anyone at 2ndQuadrant began
working in this area, and I still hold it today.

> At present, I don't understand why we would do sharding via FDWs, i.e. an
> out-of-core solution and yet replication as an in-core solution. Sharding
> desires/requires a single system image, so tight coupling is sensible (for
> example, defining a distribution key column on a distributed table). For
> replication between disparate loosely coupled systems, tight coupling is
> exactly what you do not want. So doing it that way round would give an an
> out-of-core solution for something that is best done in-core and an in-core
> solution for something best done out-of-core.

First, I think that replication can be either loosely-coupled or
tightly-coupled.  There are interesting cases with intermittently
connected networks where you really don't want too much coupling, and
then there are cases where you are doing load-balancing across a
cluster and tight coupling is fine, even desirable.   Similarly,
although I agree that a sharding solution intrinsically requires
fairly tight coupling, I think that one of the strengths of FDWs is
that they do not.  I'm not very interested in seeing a sharding
solution in PostgreSQL that limits what you can do to a particular
network topology and enforces tight coupling whether you want it or
not.  I'm more interested in seeing how we can build something that
*permits* a tightly-coupled system but also lets people build other
kinds of systems if they wish.

Second, I don't think that whether the system is tightly-coupled or
loosely-coupled has much to do with whether the code lives in
src/backend or contrib and, to be clear, I don't care all that much
about that, either.  If we end up with a great logical replication
solution and it so happens that it loads pglogical.so under the hood,
fine.  However, I do care about ease of use.  In terms of ease of use,
again, I think DDL would be a better interface than one based on
functions.  SQL is clunky at times, but being able to say CREATE TABLE
blah (a int, b text) instead of SELECT pg_create_table('blah',
ARRAY['a', 'b'], ARRAY['int'::regtype, 'text'::regtype]) surely has
something to recommend it.  Of course, there is a lot to ease of use
other than DDL, and if we end up with a design that relies strictly on
contrib, perhaps that is OK.  But it needs to be just as easy to set
up a replicated PostgreSQL cluster as it is to do the equivalent task
in some competing product, or we are missing the boat.

>> I think this would be a good topic to discuss at PGCon.
>
> I'll be at PgCon to discuss this.

Great.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Robert Haas]

On Wed, Apr 13, 2016 at 8:54 PM, Craig Ringer  wrote:
> On 14 April 2016 at 00:48, Robert Haas  wrote:
>> I think this would be a good topic to discuss at PGCon.
>
> I'm not going to be able to be at PGCon, and I don't think Petr is either.
> That's unfortunate in terms of planning for this.

That's a bummer.  I suppose you won't be at NYC either?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Joshua D. Drake]

On 04/14/2016 08:57 AM, Simon Riggs wrote:

On 14 April 2016 at 16:52, Andres Freund mailto:and...@anarazel.de>> wrote:

> If we aren't going to talk about changes in advance, then multiple
> commit rights is a recipe for conflict, not a better way at all.

Obviously we should discuss design bits, I don't see how that's a
conflict.


If everything is going to be discussed here, that's great, no conflict.

I've got lots of changes I would personally like to make, but we need to
get something in first before we start doing more.


I don't agree with that at all. At least from what I am reading, a lot 
of what Andres is talking about would work just fine as a separate 
extension project.


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Joshua D. Drake]

On 04/14/2016 08:52 AM, Andres Freund wrote:


I think there's quite a bit of work needed all around. The output
plugin's protocol needs some work, the whole catalog infrastructure and
management in pglogical itself needs a lot of work.


+1 and let's not forget a need for comprehensive documentation.

Sincerely,

Joshua D. Drake

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Joshua D. Drake]

On 04/14/2016 08:26 AM, Simon Riggs wrote:

On 13 April 2016 at 17:48, Robert Haas mailto:robertmh...@gmail.com>> wrote:

On Wed, Apr 13, 2016 at 4:38 AM, Simon Riggs mailto:si...@2ndquadrant.com>> wrote:
> Anyway, who agrees with the overall design of pglogical and who does not?

I haven't spent very much time on it yet.  I tend to prefer the idea
of integrating it more deeply into core and adding SQL syntax around
it, but I'm not going to fight tooth and nail for that if a contrary
consensus emerges.


1) "more deeply into core"
I'm open to doing that for some parts of the code, if there is benefit.
At present, an extension has exactly the same attributes as an in-core
solution, so I don't currently see any benefit in doing so. Could you
explain what you see?


From my perspective, grammar.



2) "SQL syntax"
I'm not sure what SQL syntax would give us. I know what we would lose,
which is the ability to implement new and interesting features as
extensions before putting them into core. That doesn't strike me as a
benefit, so please explain.


If by SQL syntax we mean things like "ALTER TABLE ENABLE REPLICATION" 
then it is an absolute user benefit.


Sincerely,

Joshua D. Drake
--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Andres Freund]

Hi,

On 2016-04-14 08:52:00 -0700, Andres Freund wrote:
> > Petr is more than capable for managing bug fixes and I would like to see
> > him have his chance to demonstrate his skill and get a shot at being a
> > committer, just as you did. Having one person manage a feature seems like
> > the best way.
> 
> That's what we tried for 9.5/9.6, and it didn't work.

Just to make sure this isn't misunderstood: I'm not saying that Petr
isn't good enough or anything like that. But that his time and energy
have human limits, and he's got a fair bit on his plate. And that the
feature is too big for one person.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Simon Riggs]

On 14 April 2016 at 16:52, Andres Freund  wrote:


> > If we aren't going to talk about changes in advance, then multiple
> > commit rights is a recipe for conflict, not a better way at all.
>
> Obviously we should discuss design bits, I don't see how that's a
> conflict.
>

If everything is going to be discussed here, that's great, no conflict.

I've got lots of changes I would personally like to make, but we need to
get something in first before we start doing more.

I'll look back at the review comments, thanks.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Pglogical questions and problems

[Andres Freund]

Hi,

On 2016-04-14 16:42:10 +0100, Simon Riggs wrote:
> What intense work is required? pglogical already works, it just requires
> review.

I've done a round of reviews in January, still not all points are
addressed of yet (due to time limits afaik).  At that pace it's
unrealistic to get anything into 9.7.  As of late most major patches
have been developed in a collaborative manner, across companies in many
cases. We're simply at the point where an indidivudal cannot easily
write a major piece on their own.

I think there's quite a bit of work needed all around. The output
plugin's protocol needs some work, the whole catalog infrastructure and
management in pglogical itself needs a lot of work.


> If you already have opinions about things that need changing, or
> that require additional work, please say what they are on-list in the
> normal way.

I've done so?


> I'm happy to collaborate, I just don't see why that requires multiple
> commit rights to a single repository. It may do, once we know what changes
> you're thinking about.

Because otherwise we end up waiting months for things that are agreed
upon; and which are easier to do than to hash out in minute detail.


> If we aren't going to talk about changes in advance, then multiple
> commit rights is a recipe for conflict, not a better way at all.

Obviously we should discuss design bits, I don't see how that's a
conflict.



> Petr is more than capable for managing bug fixes and I would like to see
> him have his chance to demonstrate his skill and get a shot at being a
> committer, just as you did. Having one person manage a feature seems like
> the best way.

That's what we tried for 9.5/9.6, and it didn't work.

Btw, Robert and I did work together on a shared repository for
significant chunks of the logical decoding work. That obviously doesn't
preclude becoming a committer; nor does it prevent proper attribution.

Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Simon Riggs]

On 14 April 2016 at 02:05, Andres Freund  wrote:

> Hi,
>
> On 2016-04-13 09:38:39 +0100, Simon Riggs wrote:
> > If we want this in 9.7
>
> I desperately want logical replication for 9.7. And I'm planning to put
> in a good chunk of work to make that happen in some way.


Good, thanks. I'm happy to collaborate.


> > we'll need lots of people's support, design commentary and assistance
> > with bug fixing.
>
> I think realistically that requires interested parties to collaborate on
> one repository, otherwise the latencies are going to be too big. Us
> being in different timezones and part of separate companies with
> different schedules it's very hard to arrange having time to intensely
> work together on a project.
>

What intense work is required? pglogical already works, it just requires
review. If you already have opinions about things that need changing, or
that require additional work, please say what they are on-list in the
normal way.

I'm happy to collaborate, I just don't see why that requires multiple
commit rights to a single repository. It may do, once we know what changes
you're thinking about. If we aren't going to talk about changes in advance,
then multiple commit rights is a recipe for conflict, not a better way at
all.

Petr is more than capable for managing bug fixes and I would like to see
him have his chance to demonstrate his skill and get a shot at being a
committer, just as you did. Having one person manage a feature seems like
the best way.

Logical decoding itself has many additional feature requirements as well,
so there is more than enough work to do for all.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Pglogical questions and problems

[Simon Riggs]

On 13 April 2016 at 17:48, Robert Haas  wrote:

> On Wed, Apr 13, 2016 at 4:38 AM, Simon Riggs 
> wrote:
> > Anyway, who agrees with the overall design of pglogical and who does not?
>
> I haven't spent very much time on it yet.  I tend to prefer the idea
> of integrating it more deeply into core and adding SQL syntax around
> it, but I'm not going to fight tooth and nail for that if a contrary
> consensus emerges.
>

1) "more deeply into core"
I'm open to doing that for some parts of the code, if there is benefit. At
present, an extension has exactly the same attributes as an in-core
solution, so I don't currently see any benefit in doing so. Could you
explain what you see?

2) "SQL syntax"
I'm not sure what SQL syntax would give us. I know what we would lose,
which is the ability to implement new and interesting features as
extensions before putting them into core. That doesn't strike me as a
benefit, so please explain.

At present, I don't understand why we would do sharding via FDWs, i.e. an
out-of-core solution and yet replication as an in-core solution. Sharding
desires/requires a single system image, so tight coupling is sensible (for
example, defining a distribution key column on a distributed table). For
replication between disparate loosely coupled systems, tight coupling is
exactly what you do not want. So doing it that way round would give an an
out-of-core solution for something that is best done in-core and an in-core
solution for something best done out-of-core.


> I think this would be a good topic to discuss at PGCon.
>

I'll be at PgCon to discuss this.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [HACKERS] Pglogical questions and problems

[Joshua D. Drake]

On 04/13/2016 06:05 PM, Andres Freund wrote:

Hi,

On 2016-04-13 09:38:39 +0100, Simon Riggs wrote:

If we want this in 9.7


I desperately want logical replication for 9.7. And I'm planning to put
in a good chunk of work to make that happen in some way.



I don't see a need for it to be in -core but I certainly see a need for 
a solid replacement to something like Slony.






we'll need lots of people's support, design commentary and assistance
with bug fixing.


I think realistically that requires interested parties to collaborate on
one repository, otherwise the latencies are going to be too big. Us
being in different timezones and part of separate companies with
different schedules it's very hard to arrange having time to intensely
work together on a project.



Absolutely. If people (who are not 2Q) are going to put energy into 
this, it should be in an open, collaborative fashion just like any other 
open source project.


The only reason I currently bothered with what I did is because I needed 
to update a presentation. Had that not been the case, I wouldn't have 
touched it because it (AFAICT) is not developed in the proper open 
source way. That may or may not be intentional but I know it affects who 
is willing to do things like test and provide feedback.


Sincerely,

JD


--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Andres Freund]

Hi,

On 2016-04-13 09:38:39 +0100, Simon Riggs wrote:
> If we want this in 9.7

I desperately want logical replication for 9.7. And I'm planning to put
in a good chunk of work to make that happen in some way.


> we'll need lots of people's support, design commentary and assistance
> with bug fixing.

I think realistically that requires interested parties to collaborate on
one repository, otherwise the latencies are going to be too big. Us
being in different timezones and part of separate companies with
different schedules it's very hard to arrange having time to intensely
work together on a project.


Greetings,

Andres Freund


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Craig Ringer]

On 14 April 2016 at 00:48, Robert Haas  wrote:


>
> I think this would be a good topic to discuss at PGCon.
>
>
I'm not going to be able to be at PGCon, and I don't think Petr is either.
That's unfortunate in terms of planning for this.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] Pglogical questions and problems

[Robert Haas]

On Wed, Apr 13, 2016 at 4:38 AM, Simon Riggs  wrote:
> Anyway, who agrees with the overall design of pglogical and who does not?

I haven't spent very much time on it yet.  I tend to prefer the idea
of integrating it more deeply into core and adding SQL syntax around
it, but I'm not going to fight tooth and nail for that if a contrary
consensus emerges.

I think this would be a good topic to discuss at PGCon.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Abhijit Menon-Sen]

At 2016-04-13 03:46:21 -0700, j...@commandprompt.com wrote:
>
> ALTER DATABASE ADD NODE;
> ALTER SCHEMA SUBSCRIBE ALL;
> CREATE REPLICATION SET;
> 
> But I am unaware if that is possible within the constraints of the
> extensions API.

It is not possible.

-- Abhijit


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Joshua D. Drake]

On 04/13/2016 01:38 AM, Simon Riggs wrote:

On 12 April 2016 at 23:46, Joshua D. Drake mailto:j...@commandprompt.com>> wrote:

So what am I missing?

Thanks for the bug report, but unfortunately the CF is now over and
pglogical is not in 9.6. So its a little late for reviews and bug reports.


I wasn't clear where I should be sending information about pgLogical or 
if this was indeed a bug report or user error.




I'm not sure of the exact status of pglogical now. It's has a good
reception outside of Hackers, but not much has been said here, so its
unclear whether it will be in 9.7. Obviously if its not got support and
will eventually be rejected for an alternate design, fixing the bugs
won't be worthwhile discussion on Hackers.


This may be true. Is there another mailing list where this discussion is 
more appropriate?




If we want this in 9.7, we'll need lots of people's support, design
commentary and assistance with bug fixing. So some exploration of
whether this is a pglogical issue, an origin issue or decoding issue
would help a lot, if you can provide that?


I am certainly happy to provide any logs etc... This is just a test 
instance that I was scaffolding for a presentation.



Obviously, investigating
every case is difficult but we should be able to build up a library of
test cases over time to assist with that.



Sure but having a better understanding through documentation of what is 
supposed to work and what isn't would be helpful too. As I mentioned in 
my OP, I wasn't even sure if it was something I was missing? I could 
have just tried to make it do something it wasn't meant to do.




If we agree this is worth further work, I'll take responsibility for it
in 9.7.



I think that is kind of up to 2Q? Are you saying that if .Org doesn't 
want pgLogical in core then it isn't going to be supported or developed 
or are you trying to determine if you need to start building an 
individual community around it?



Anyway, who agrees with the overall design of pglogical and who does not?



From a usability standpoint I find the interface a little rough. I 
would much prefer to be able to do things like:


ALTER DATABASE ADD NODE;
ALTER SCHEMA SUBSCRIBE ALL;
CREATE REPLICATION SET;

But I am unaware if that is possible within the constraints of the 
extensions API.


Sincerely,

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Pglogical questions and problems

[Simon Riggs]

On 12 April 2016 at 23:46, Joshua D. Drake  wrote:


> So what am I missing?
>

Thanks for the bug report, but unfortunately the CF is now over and
pglogical is not in 9.6. So its a little late for reviews and bug reports.

I'm not sure of the exact status of pglogical now. It's has a good
reception outside of Hackers, but not much has been said here, so its
unclear whether it will be in 9.7. Obviously if its not got support and
will eventually be rejected for an alternate design, fixing the bugs won't
be worthwhile discussion on Hackers.

If we want this in 9.7, we'll need lots of people's support, design
commentary and assistance with bug fixing. So some exploration of whether
this is a pglogical issue, an origin issue or decoding issue would help a
lot, if you can provide that? Obviously, investigating every case is
difficult but we should be able to build up a library of test cases over
time to assist with that.

If we agree this is worth further work, I'll take responsibility for it in
9.7.

Anyway, who agrees with the overall design of pglogical and who does not?

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

[HACKERS] Pglogical questions and problems

[Joshua D. Drake]

Hello,

Alright -- here we go PostgreSQL 9.5.2, Ubuntu Trusty, All packages from 
apt.postgresql.org except PgLogical which is from 2Q:


I have the following setup:

Origin0->Replica0
 Table: logical_test(id bigserial primary key)

Origin0:

SELECT pglogical.create_node(
  node_name := 'origin',
  dsn := 'host=192.168.1.65 port=5432 dbname=logical'
);

CREATE TABLE logical_test (id bigserial primary key);

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

Replica0:

SELECT pglogical.create_node(
  node_name := 'replica0',
  dsn := 'host=192.168.1.66 port=5432 dbname=logical'
);

SELECT pglogical.create_subscription(
  subscription_name := 'replica0relations',
  provider_dsn := 'host=192.168.1.65 port=5432 dbname=logical',
  synchronize_structure := TRUE,
  synchronize_data := TRUE
);

Replicating from Origin0->Replica0 works. I then added Replica1:

SELECT pglogical.create_node(
  node_name := 'replica2',
  dsn := 'host=192.168.1.67 port=5432 dbname=logical'
);

SELECT pglogical.create_subscription(
  subscription_name := 'logical_subscriber2',
  provider_dsn := 'host=192.168.1.66 port=5432 dbname=logical',
  synchronize_data := TRUE,
  synchronize_structure := TRUE
);

The initial sync works, I end up with the table and all rows. However if 
I perform an update and add or modify rows, only the origin and replica0 
update. Replica1 provides the following:


2016-04-12 15:38:10 PDT [25712-2] ERROR:  cache lookup failed for 
replication origin 'pgl_logical_origin_replica084e3989'
2016-04-12 15:38:10 PDT [1192-89105] LOG:  worker process: pglogical 
apply 16384:1108649370 (PID 25712) exited with exit code 1


And continues to provide this rather non-useful message continuously in 
a loop.


I tried dropping the subscription:

logical=# select pglogical.drop_subscription(subscription_name := 
'logical_subscriber2');

 drop_subscription
---
 1

And dropping the tables:

logical=# drop table logical_test;
DROP TABLE

logical=# \d
No relations found.

Then add the subscription:

SELECT pglogical.create_subscription(
  subscription_name := 'logical_subscriber2',
  provider_dsn := 'host=192.168.1.66 port=5432 dbname=logical',
  synchronize_data := TRUE,
  synchronize_structure := TRUE
);

logical=# \d
 List of relations
 Schema |Name |   Type   |  Owner
+-+--+--
 public | logical_test| table| postgres
 public | logical_test_id_seq | sequence | postgres
(2 rows)

logical=# select count(*) from logical_test;
 count
---
  1100
(1 row)

That is accurate but if I try to add rows from the Origin:

logical=# truncate logical_test;
TRUNCATE TABLE
logical=# select count(*) from logical_test;
 count
---
 0
(1 row)

Replica0:

logical=# select count(*) from logical_test;
 count
---
 0
(1 row)

Replica2:

logical=# select count(*) from logical_test;
 count
---
  1100
(1 row)

Replica2 log:

2016-04-12 15:43:39 PDT [4881-1] LOG:  starting apply for subscription 
logical_subscriber2
2016-04-12 15:43:39 PDT [4881-2] ERROR:  cache lookup failed for 
replication origin 'pgl_logical_origin_replica084e3989'
2016-04-12 15:43:39 PDT [1192-100644] LOG:  worker process: pglogical 
apply 16384:1108649370 (PID 4881) exited with exit code 1


So what am I missing?

Origin pg_hba.conf:

hostssl  replicationpostgres192.168.1.66/32 md5
hostssl  logicalpostgres192.168.1.66/32 md5

Replica0 pg_hba.conf:

hostssl  logical postgres127.0.0.1/32   md5
hostssl  logical postgres192.168.1.66/32md5
hostssl  logical postgres192.168.1.67/32md5
hostssl  replication postgres192.168.1.67/32md5

Replica2 pg_hba.conf:

hostssl  logical postgres127.0.0.1/32   md5
hostssl  logical postgres192.168.1.66/32md5
hostssl  logical postgres192.168.1.67/32md5
hostssl  replication postgres192.168.1.67/32md5

All auth is done via .pgpass.

Sincerely,

JD

















--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers