pglogical in postgres 9.6

On 02/12/2018 02:11 PM, greigwise wrote:

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?

I don't know what is going on, but for those that might the pglogical
version you are using might be helpful.

Thanks,
Greig Wise

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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

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.klaver@aklaver.com

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.klaver@aklaver.com

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

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.klaver@aklaver.com

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

On Tue, Feb 13, 2018 at 1:46 PM greigwise <greigwise@comcast.net> 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