Thread: pglogical in postgres 9.6

pglogical in postgres 9.6

From
greigwise
Date:
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


Re: pglogical in postgres 9.6

From
Adrian Klaver
Date:
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


Re: pglogical in postgres 9.6

From
greigwise
Date:
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


Re: pglogical in postgres 9.6

From
greigwise
Date:
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

From
Adrian Klaver
Date:
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


Re: pglogical in postgres 9.6

From
Adrian Klaver
Date:
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


Re: pglogical in postgres 9.6

From
greigwise
Date:
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

From
Adrian Klaver
Date:
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


Re: pglogical in postgres 9.6

From
greigwise
Date:
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

From
Jeremy Finzel
Date:

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