Re: [HACKERS] pglogical most basic setup for logical replication

[Sebastien Diemer]

>
> It sounds like you must be running on PostgreSQL 9.4.

Indeed I am on PostgreSQL 9.4, I omitted this important point.

You'll need to drop the subscriber database and re-create it. Use a new
> node name.

Seems to work but I still do not really understand what was wrong in the
first place though.
Thanks for your help Craig !

*Sébastien DIEMER*
*Polyconseil* | 26 rue de Berri, 75008 Paris

2016-01-26 11:22 GMT+01:00 Craig Ringer :

> On 26 January 2016 at 18:14, Sebastien Diemer <
> sebastien.die...@polyconseil.fr> wrote:
>
>> Hello,
>>
>> I did not manage to make the simplest logical replication scheme work
>> with pglogical.
>> My setup is the following: two postgresql nodes (one provider and one
>> subscriber) with one database and one simple table:
>> `CREATE TABLE t (c1 integer, PRIMARY KEY (c1));`
>>
>> I followed the README provided with pglogical source code without any
>> success.
>> I created the provider and the subscriber nodes without problem but then,
>> after sending the sql query `SELECT
>> pglogical.create_subscription('subscription1', 'host=localhost port=55432
>> dbname=postgres');`  I got:
>>
>> "ERROR:  pglogical_origin extension not found"
>>
>
> It sounds like you must be running on PostgreSQL 9.4.
>
> 9.5 was the primary target for pglogical. Replicating from 9.4 to 9.4 was
> added quite late in the process. It seems we left out some key information
> from the documentation there.
>
>
>> "ERROR:  subscriber subscription1 initialization failed during
>> nonrecoverable step (s), please try the setup again"
>>
>> I tried to replay the setup again without any success.
>>
>
> You'll need to drop the subscriber database and re-create it. Use a new
> node name.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

[HACKERS] pglogical most basic setup for logical replication

[Sebastien Diemer]

Hello,

I did not manage to make the simplest logical replication scheme work with
pglogical.
My setup is the following: two postgresql nodes (one provider and one
subscriber) with one database and one simple table:
`CREATE TABLE t (c1 integer, PRIMARY KEY (c1));`

I followed the README provided with pglogical source code without any
success.
I created the provider and the subscriber nodes without problem but then,
after sending the sql query `SELECT
pglogical.create_subscription('subscription1', 'host=localhost port=55432
dbname=postgres');`  I got:

"ERROR:  pglogical_origin extension not found"

So I `CREATE EXTENSION pglogical_origin;` on the subscriber node (this step
is not mentioned in the README).

But then, I have the following error on the worker running on the
subscriber node:

"ERROR:  subscriber subscription1 initialization failed during
nonrecoverable step (s), please try the setup again"

I tried to replay the setup again without any success.
What am I missing here ?

Thanks for your help


*Sébastien DIEMER*

Re: [HACKERS] pglogical most basic setup for logical replication

[Craig Ringer]

On 26 January 2016 at 18:14, Sebastien Diemer <
sebastien.die...@polyconseil.fr> wrote:

> Hello,
>
> I did not manage to make the simplest logical replication scheme work with
> pglogical.
> My setup is the following: two postgresql nodes (one provider and one
> subscriber) with one database and one simple table:
> `CREATE TABLE t (c1 integer, PRIMARY KEY (c1));`
>
> I followed the README provided with pglogical source code without any
> success.
> I created the provider and the subscriber nodes without problem but then,
> after sending the sql query `SELECT
> pglogical.create_subscription('subscription1', 'host=localhost port=55432
> dbname=postgres');`  I got:
>
> "ERROR:  pglogical_origin extension not found"
>

It sounds like you must be running on PostgreSQL 9.4.

9.5 was the primary target for pglogical. Replicating from 9.4 to 9.4 was
added quite late in the process. It seems we left out some key information
from the documentation there.


> "ERROR:  subscriber subscription1 initialization failed during
> nonrecoverable step (s), please try the setup again"
>
> I tried to replay the setup again without any success.
>

You'll need to drop the subscriber database and re-create it. Use a new
node name.

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

Re: [HACKERS] pglogical most basic setup for logical replication

[Craig Ringer]

On 26 January 2016 at 19:18, Sebastien Diemer <
sebastien.die...@polyconseil.fr> wrote:

> It sounds like you must be running on PostgreSQL 9.4.
>
> Indeed I am on PostgreSQL 9.4, I omitted this important point.
>
>
I'll update the docs to mention the extra step on 9.4.


> You'll need to drop the subscriber database and re-create it. Use a new
>> node name.
>
> Seems to work but I still do not really understand what was wrong in the
> first place though.
> Thanks for your help Craig !
>

It's because internally pglogical relies on pg_dump and pg_restore to set
up the initial database state. If it fails partway through a pg_restore
there's no sensible way to undo those changes, especially since we have to
do it in a number of steps to achieve a partial restore.

A cleaner handling of this really requires the ability to use pg_dump's
functionality over a normal protocol connection from SQL, or at least as a
library.

The error should probably get a HINT to say that an incomplete restore
means the DB may have to be dropped and recreated.

It's all a bit messy, since we support multiple upstreams and support
writes directly into the downstream. So you actually can't always just drop
the downstream. A really good solution to this needs the ability to compare
various objects (type definitions, tables, etc), the ability to extract
just a required subset of dependencies from a database and other
complexities.

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