Re: pglogical in postgres 9.6

[Jeremy Finzel]

On Tue, Feb 13, 2018 at 1:46 PM greigwise  wrote:

> No need!  I figured it out.
>
> Had to put this "synchronize_data := false" on the create_subscription
> call.
> Weird that there seem to be redundant parameters for this; one on the
> replication set add and one on the create subscription.  Maybe I'm not
> quite
> understanding the usage on those or something.   If anyone knows the
> difference, I'd be interested to hear.
>
> Greig
>
>
>
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
> Yes. When you add a subscription with sync = true, then all tables in the
replication set(s) will be synced. But suppose you want to add a table
later to the replication set. Sync = true will sync only that one table. So
the latter is more granular.

Jeremy

Re: pglogical in postgres 9.6

[greigwise]

No need!  I figured it out.

Had to put this "synchronize_data := false" on the create_subscription call. 
Weird that there seem to be redundant parameters for this; one on the
replication set add and one on the create subscription.  Maybe I'm not quite
understanding the usage on those or something.   If anyone knows the
difference, I'd be interested to hear.

Greig



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pglogical in postgres 9.6

[Adrian Klaver]

On 02/12/2018 06:09 PM, greigwise wrote:

hireology_tmp=# \dx
 List of installed extensions
Name   | Version |  Schema  |
Description
--+-+--+
  pglogical| 2.0.0   | pglogical| PostgreSQL Logical
Replication
  pglogical_origin | 1.0.0   | pglogical_origin | Dummy extension for
compatibility when upgrading from Postgres 9.4


To get an answer you might to file an issue here:

https://github.com/2ndQuadrant/pglogical/issues





--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pglogical in postgres 9.6

[greigwise]

hireology_tmp=# \dx
List of installed extensions
   Name   | Version |  Schema  |   
Description
--+-+--+
 pglogical| 2.0.0   | pglogical| PostgreSQL Logical
Replication
 pglogical_origin | 1.0.0   | pglogical_origin | Dummy extension for
compatibility when upgrading from Postgres 9.4



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pglogical in postgres 9.6

[Adrian Klaver]

On 02/12/2018 04:46 PM, greigwise wrote:

I downloaded from git and compiled from source.  Based on the
pglogical.control file, it appears to be 2.0.0.  Is there a better way to
determine the version?


Should have added that you need run the \dx in the database you 
installed pglogical into.




Thanks,
Greig



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pglogical in postgres 9.6

[Adrian Klaver]

On 02/12/2018 04:46 PM, greigwise wrote:

I downloaded from git and compiled from source.  Based on the
pglogical.control file, it appears to be 2.0.0.  Is there a better way to
determine the version?


In psql do:

\dx



Thanks,
Greig



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: pglogical in postgres 9.6

[greigwise]

I downloaded from git and compiled from source.  Based on the
pglogical.control file, it appears to be 2.0.0.  Is there a better way to
determine the version?

Thanks,
Greig



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pglogical in postgres 9.6

[greigwise]

I downloaded it from git and compiled from source just today.

Looks like it's 2.0.0 based on the pglogical.control file that was in the
source.  I'm not sure how else I'd know.

Greig



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

pglogical in postgres 9.6

[greigwise]

Hello.  I'm trying to get pglogical setup between 2 postgres 9.6 instances. 
I can get everything replicating over fine, my problem is that I can't seem
to get it to work in the mode where it does not try to copy all the data
over initially.  On the source side, I'm doing this:

  psql -U hireology -d $SRCDB -c "SELECT pglogical.create_node( node_name :=
'${SRCNODE}', dsn := 'host=localhost port=${SRCPORT} dbname=${SRCDB}
user=logical_replication')"
  
  psql -U hireology -d $SRCDB -c "select
pglogical.create_replication_set('dw_repl', true, true, true, true)"
  
  psql -U hireology -d $SRCDB -c "select pglogical.replication_set_add_table
('dw_repl', 'accounts', false)"
  psql -U hireology -d $SRCDB -c "select pglogical.replication_set_add_table
('dw_repl', 'organizations', false)"
  psql -U hireology -d $SRCDB -c "select
pglogical.replication_set_add_sequence ('dw_repl', 'accounts_id_seq',
false)"
  psql -U hireology -d $SRCDB -c "select
pglogical.replication_set_add_sequence ('dw_repl', 'organizations_id_seq',
false)"

Note on the replication_set_add_table command, I'm sending false as the
third parameter which should tell it to not copy over the initial data.  But
when I create the subscription on the target side, I can see in the logs
where it is issuing the copy command.  And since I have the data already
there, I get duplicate key errors and it bombs out.  Any suggestions?

Thanks,
Greig Wise




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html