Thread: Pglogical questions and problems

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=5432dbname=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=5432dbname=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=5432dbname=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  replication    postgres    192.168.1.66/32    md5
hostssl     logical    postgres    192.168.1.66/32    md5

Replica0 pg_hba.conf:

hostssl     logical     postgres     127.0.0.1/32        md5
hostssl  logical     postgres     192.168.1.66/32    md5
hostssl  logical     postgres     192.168.1.67/32    md5
hostssl     replication     postgres     192.168.1.67/32    md5

Replica2 pg_hba.conf:

hostssl     logical     postgres     127.0.0.1/32        md5
hostssl     logical     postgres     192.168.1.66/32    md5
hostssl  logical     postgres     192.168.1.67/32    md5
hostssl  replication     postgres        192.168.1.67/32        md5

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.

On 04/13/2016 01:38 AM, Simon Riggs wrote:
> On 12 April 2016 at 23:46, Joshua D. Drake <jd@commandprompt.com
> <mailto:jd@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.

At 2016-04-13 03:46:21 -0700, jd@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

On Wed, Apr 13, 2016 at 4:38 AM, Simon Riggs <simon@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.

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

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

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

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.

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

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

On 04/14/2016 08:26 AM, Simon Riggs wrote:
> On 13 April 2016 at 17:48, Robert Haas <robertmhaas@gmail.com
> <mailto:robertmhaas@gmail.com>> wrote:
>
>     On Wed, Apr 13, 2016 at 4:38 AM, Simon Riggs <simon@2ndquadrant.com
>     <mailto:simon@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.

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.

On 04/14/2016 08:57 AM, Simon Riggs wrote:
> On 14 April 2016 at 16:52, Andres Freund <andres@anarazel.de
> <mailto:andres@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.

On Wed, Apr 13, 2016 at 8:54 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 14 April 2016 at 00:48, Robert Haas <robertmhaas@gmail.com> 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

On Thu, Apr 14, 2016 at 11:26 AM, Simon Riggs <simon@2ndquadrant.com> 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

On 14/04/16 20:14, Robert Haas wrote:
> On Wed, Apr 13, 2016 at 8:54 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
>> On 14 April 2016 at 00:48, Robert Haas <robertmhaas@gmail.com> 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

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