Обсуждение: pglogical in postgres 9.6
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
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