2

I'm testing pg_logical extension for Postgresql because we need to replicate some clusters with Postgresql 9.6 before upgrading them to newer version, where logical replication is a core feature.

I've followed Depesz fabulous article. Master/publisher is CentOS 7, Slave/subscriber is Ubuntu 18.04. I've installed pg_logical package in both machines. After that, I've created a simple publication on master:

SELECT pglogical.create_node( node_name := 'proveedor1', dsn := 'host=192.168.1.65 port=5433 dbname=mydb user=replicador' );
SELECT pglogical.replication_set_add_table('default', 'public.table');
SELECT pglogical.replication_set_add_sequence('default', 'public.table_column_seq', synchronize_data := true);

I've pg_dumpall --globals-only master's globals and applied them on slave. After that, I've pg_dump --schema-only and applied on slave. Then, on slave I've created subscription:

SELECT pglogical.create_node( node_name := 'suscriptor_ubuntu', dsn := 'host=192.168.1.60 port=5432 dbname=mydb user=replicador' );
SELECT pglogical.create_subscription( subscription_name := 'suscripcion1', provider_dsn := 'host=192.168.1.65 port=5433 dbname=mydb user=replicador', replication_sets := '{default}'::text[] );

I've waited for a while (a couple of minutes) to let synchronization end. When I check table I can see data perfectly synchronized, but when checking sequence it's not synchronized. On master:

trazabilidad=# select * from public.table_column_seq;
           sequence_name           | **last_value** | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
-----------------------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 table_column_seq                  |          **8** |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | t
(1 fila)

On slave:

trazabilidad=# select * from table_column_seq ;
           sequence_name           | **last_value** | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called 
-----------------------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 table_column_seq                  |          **1** |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
(1 fila)

I've waited several minutes more but no effect: sequence remains no syncronized. I've tried with select pglogical.synchronize_sequence('table_column_seq'); but it makes slave sequence last_value = 1008 instead of 8 as it has to be. I've read somewhere that this is the expected behaviour as this method sets sequence value to some future (or something better explained that now I don't know how to translate).

So, how can I make slave sequence's last_value match masters'?

We'd like to have a separate instance for reading data in order to make reports, querys or something similar, without updating data. But I'm testing also to create a "real" development environment like this article explains.

2
  • postgresql.org/docs/10/logical-replication-restrictions.html read this one, you will know where it is the limitaion for logical replication Nov 10, 2021 at 9:06
  • I'm using pg_logical extension, not logical replication functionality provided with postgresql. If I remember things right, logical replication is not supported until v10 and the version used for this test case is 9.6 so not sure if postgresql logical replication restrictions applies to this case.
    – EAmez
    Nov 11, 2021 at 11:56

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.