Обсуждение: pglogical most basic setup for logical replication
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
On 26 January 2016 at 18:14, Sebastien Diemer <sebastien.diemer@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.
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 <craig@2ndquadrant.com>:
On 26 January 2016 at 18:14, Sebastien Diemer <sebastien.diemer@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.--
On 26 January 2016 at 19:18, Sebastien Diemer <sebastien.diemer@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.