Re: Trying to setup pglogical with no success

On 04/13/2016 03:16 PM, cchee-ob wrote:
> Here's what I have done to try to set up pglogical.  What am I doing wrong?

Not sure. Did you do this?:

http://2ndquadrant.com/en/resources/pglogical/pglogical-docs/

"The pglogical_output extension needs to be installed on both provider and subscriber. No actual CREATE EXTENSION is
required,it must just be present in the PostgreSQL installation." 

"pg_hba.conf has to allow replication connections from localhost, by a user with replication privilege."

Not sure if:

host replication postgres 10.241.0.10/32 trust
host replication postgres 10.241.0.12/32 trust

are being seen as localhost.


>
> 2 nodes
> provider -   10.241.0.10
> subscriber - 10.241.0.12
>
>
> svp2=# select version();
>                                           version
> ------------------------------------------------------------------------------------------
>   PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10)
> 4.9.2, 64-bit
> (1 row)
>
> svp2=# \dx
>                     List of installed extensions
>     Name    | Version |   Schema   |          Description
> -----------+---------+------------+--------------------------------
>   pglogical | 1.0.1   | pglogical  | PostgreSQL Logical Replication
>   plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
> (2 rows)
>
>
> pg_hba.conf
>
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
>
> local   all             postgres                                trust
> local   replication     postgres                                trust
>
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> host        all          all                  127.0.0.1/32            trust
> host        svp2         svc,tms,svcro      0.0.0.0/0               md5
> host        all            all                10.241.0.10/32          trust
> host        replication    postgres               10.241.0.10/32          trust
> host        all            all                   10.241.0.12/32          trust
> host        replication    postgres               10.241.0.12/32          trust
>
> postgresql.conf
>
> # PGLOGICAL
>
> shared_preload_libraries = 'pglogical'
> max_worker_processes = 10
> wal_level = 'logical'
> max_wal_senders = 10
> max_replication_slots = 10
> track_commit_timestamp = on
>
> PROVIDER
>
> select pglogical.create_node(
> node_name := 'provider1',
> dsn := 'host=10.241.0.10 port=5432 dbname=svp2');
>
> select pglogical.replication_set_add_all_tables('default', ARRAY['public']);
>
> SUBSCRIBER
>
> select pglogical.create_node(
> node_name := 'subscriber1',
> dsn := 'host=10.241.0.12 port=5432 dbname=svp2');
>
> select pglogical.create_subscription(
> subscription_name := 'subscription1’,
> provider_dsn := 'host=10.241.0.10 port=5432 dbname=svp2');
>
> I get this error repeats until I drop the subscription.
>
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=DEBUG:  00000: registering
> background worker "pglogical apply 16384:1763399739"
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOCATION:
> BackgroundWorkerStateChange, bgworker.c:347
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=DEBUG:  00000: starting background
> worker process "pglogical apply 16384:1763399739"
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOCATION:  do_start_bgworker,
> postmaster.c:5479
> t=2016-04-12 11:31:24 PDT d= h= p=7723 a=ERROR:  XX000: subscriber
> subscription1 initialization failed during nonrecoverable step (s), please
> try the setup again
> t=2016-04-12 11:31:24 PDT d= h= p=7723 a=LOCATION:
> pglogical_sync_subscription, pglogical_sync.c:514
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOG:  00000: worker process:
> pglogical apply 16384:1763399739 (PID 7723) exited with exit code 1
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOCATION:  LogChildExit,
> postmaster.c:3449
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=DEBUG:  00000: unregistering
> background worker "pglogical apply 16384:1763399739"
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOCATION:  ForgetBackgroundWorker,
> bgworker.c:376
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=DEBUG:  00000: registering
> background worker "pglogical apply 16384:1763399739"
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOCATION:
> BackgroundWorkerStateChange, bgworker.c:347
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=DEBUG:  00000: starting background
> worker process "pglogical apply 16384:1763399739"
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOCATION:  do_start_bgworker,
> postmaster.c:5479
> t=2016-04-12 11:31:24 PDT d= h= p=7724 a=ERROR:  XX000: subscriber
> subscription1 initialization failed during nonrecoverable step (s), please
> try the setup again
> t=2016-04-12 11:31:24 PDT d= h= p=7724 a=LOCATION:
> pglogical_sync_subscription, pglogical_sync.c:514
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOG:  00000: worker process:
> pglogical apply 16384:1763399739 (PID 7724) exited with exit code 1
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOCATION:  LogChildExit,
> postmaster.c:3449
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=DEBUG:  00000: unregistering
> background worker "pglogical apply 16384:1763399739"
> t=2016-04-12 11:31:24 PDT d= h= p=30336 a=LOCATION:  ForgetBackgroundWorker,
> bgworker.c:376
>
>
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/Trying-to-setup-pglogical-with-no-success-tp5898571.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com