search

2ndQuadrant / pglogical

Logical Replication extension for PostgreSQL 17, 16, 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
http://2ndquadrant.com/en/resources/pglogical/
Other
1.01k stars 153 forks source link

Questions: upgrading from 9.4 to 9.5 - data sync not performing #41

Closed Envek closed 7 years ago

Envek commented 8 years ago

Hello! Thank you for pglogical. I'm trying to do an upgrade from 9.4 to new server with fresh install of PostgreSQL 9.5. Doing setup according to docs but data sync isn't working.

Expected result

Streaming is working and data sync is performed.

Actual result

Streaming is working but data sync is NOT performed (no signs of data sync: no network or disk activity on both servers, in PostgreSQL logs no new messages appearing after executing pglogical.alter_subscription_synchronize('smp_test_replication')).

Setup

Producer

I have a PostgreSQL 9.4 server which I want to upgrade.

  1. I'm installing latest postgresql-9.4-pglogical package (1.1.2-2trusty).

  2. Adding settings to postgresql.conf:

    wal_level = 'logical'
max_worker_processes = 10
max_replication_slots = 10 
max_wal_senders = 10
shared_preload_libraries = 'pglogical'

  3. Adding this and consumer's IPs to pg_hba.conf:

    host    replication postgres    127.0.0.0/8     md5
host    replication postgres    172.1.1.1/32    md5
host    replication postgres    172.1.1.2/32    md5
  4. restarting…
  5. Adding pglogical and pglogical_origin (as 9.4 requires it) extensions to database.

  6. Executing:

    SELECT pglogical.create_node('test-db-0', 'host=172.1.1.1 port=5432 dbname=smp user=postgres password=secure');
SELECT pglogical.replication_set_add_all_tables('default', schema_names := ARRAY['public'], synchronize_data := true);
SELECT pglogical.replication_set_add_all_sequences('default', schema_names:= ARRAY['public'], synchronize_data := true);

  7. Doing a dump of DB:

    pg_dump -U smp -F custom smp > file.dump

    Subscriber

A brand new server with fresh PostgreSQL 9.5 installed.

Steps 1 to 4 are same (with 9.5 in package name).

  1. Adding pglogical extension to DB.

  2. Restoring schema-only dump from producer db:

    pg_restore -U smp -d smp -F custom --clean --schema-only --schema public file.dump

  3. Executing:

    SELECT pglogical.create_node('test-db-1', 'host=172.1.1.2 port=5432 dbname=smp user=postgres password=secure');

create_node
-------------
 2826448934
(1 row)

SELECT pglogical.create_subscription(
 subscription_name     := 'smp_test_replication',
 provider_dsn          := 'host=172.1.1.1 port=5432 dbname=smp user=postgres password=secure',
 synchronize_structure := false,
 synchronize_data      := true
);

create_subscription
---------------------
         3501287532
(1 row)

SELECT pglogical.alter_subscription_synchronize('smp_test_replication');
alter_subscription_synchronize
--------------------------------
t
(1 row)

  4. After that:

    SELECT * FROM pglogical.show_subscription_status();

 subscription_name   |   status    | provider_node |                           provider_dsn                            |             slot_name             |           replication_sets            | forward_origins
----------------------+-------------+---------------+-------------------------------------------------------------------+-----------------------------------+---------------------------------------+-----------------
smp_test_replication | replicating | test-db-0     | host=172.1.1.1 port=5432 dbname=smp user=postgres password=secure | pgl_smp_test_db_0_smp_bsktd0b1686 | {default,default_insert_only,ddl_sql} | {all}

Looks like everything is good, and streaming is working: If I create some new row in table at producer it will appear at consumer.

But there are no signs of data sync: no network or disk activity on both servers, in PostgreSQL logs no new messages appearing after executing pglogical.alter_subscription_synchronize('smp_test_replication').

Questions

  1. May be I missing something in my setup?
  2. Can I see data sync progress for whole DB, node or subscription?

Thank you in advance.

With best regards, Andrey Novikov.

ringerc commented 8 years ago

You specify synchronize_data := true so you should already have the initial data from subscription setup.

What's the purpose of calling alter_subscription_synchronize here?

Can you please show the contents of the pglogical.local_sync_status table on the subscriber side?

ringerc commented 8 years ago

Also, do you see the same issue if you use pglogical 1.2?

raivil commented 7 years ago

I've had the same issue here. I've followed the example documentation example.

postgresql-9.4-pglogical (1.2.1-2jessie)

Provider (PG 9.4.9):

CREATE EXTENSION pglogical_origin;
CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
    node_name := 'master_provider',
    dsn := 'host=providerhost port=5432 dbname=mydb user=postgres password=mysecretpassword'
);
SELECT pglogical.replication_set_add_all_tables('default', schema_names:= ARRAY['public'], synchronize_data := true);
# here I've found out a few tables without PK and fixed them.
SELECT pglogical.replication_set_add_all_sequences('default', schema_names:= ARRAY['public'], synchronize_data := true);

Subscriber (Postgres 9.6.0):

CREATE EXTENSION pglogical;
SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=myhost port=5432 dbname=mydb user=postgres password=mysecretpassword'
);
SELECT pglogical.create_subscription(
    subscription_name := 'subscription1',
    provider_dsn := 'host=provider_host port=5432 dbname=mydb user=postgres password=mysecretpassword',
    synchronize_structure := true,
    synchronize_data := true
);

Existing data was not copied. New data inserted after subscription was copied. Existing data only synced after I called pglogical.alter_subscription_resynchronize_table individually for each table.

Am I missing something here?

mydb=# select * from pg_stat_replication;    
 pid  | usesysid |   usename   | application_name |  client_addr   | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state 
------+----------+-------------+------------------+----------------+-----------------+-------------+-------------------------------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
  103 |    16384 | replication | walreceiver      | someip |                 |       53200 | 2016-10-25 15:09:28.920009+00 |         2119 | streaming | 0/984C6920    | 0/984C6920     | 0/984C6920     | 0/984C6920      |             0 | async
 1010 |       10 | postgres    | subscription1    | otherip   |                 |       34546 | 2016-10-25 16:31:13.278184+00 |              | streaming | 0/984C6920    | 0/984C6920     | 0/984C6920     | 0/984C6920      |             0 | async
# SELECT * FROM pglogical.show_subscription_status();
 subscription_name |   status    |  provider_node  |                                      provider_dsn                                      |                   slot_name                   |           replication_sets            | forward_origins 
-------------------+-------------+-----------------+----------------------------------------------------------------------------------------+-----------------------------------------------+---------------------------------------+-----------------
 subscription1     | replicating | master_provider | host=provider_host port=5432 dbname=mydb user=postgres password=mysecretpassword | pgl_mydb_master_provider_subscription1 | {default,default_insert_only,ddl_sql} | {all}
ringerc commented 7 years ago

Odd. Can you show logs from the time of the subscription for both upstream and downstream please?​

raivil commented 7 years ago

Postgres 9.4

database_master_1     | PostgreSQL init process complete; ready for start up.
database_master_1     | 
database_master_1     | LOG:  registering background worker "pglogical supervisor"
database_master_1     | LOG:  database system was shut down at 2016-10-26 15:54:02 GMT
database_master_1     | LOG:  MultiXact member wraparound protections are now enabled
database_master_1     | LOG:  starting background worker process "pglogical supervisor"
database_master_1     | LOG:  database system is ready to accept connections
database_master_1     | LOG:  starting pglogical supervisor
database_master_1     | LOG:  autovacuum launcher started
database_master_1     | LOG:  registering background worker "pglogical manager 1"
database_master_1     | LOG:  starting background worker process "pglogical manager 1"
database_master_1     | LOG:  manager worker [90] at slot 0 generation 1 detaching cleanly
database_master_1     | LOG:  worker process: pglogical manager 1 (PID 90) exited with exit code 0
database_master_1     | LOG:  unregistering background worker "pglogical manager 1"
database_master_1     | LOG:  registering background worker "pglogical manager 12141"
database_master_1     | LOG:  starting background worker process "pglogical manager 12141"
database_master_1     | LOG:  manager worker [91] at slot 0 generation 2 detaching cleanly
database_master_1     | LOG:  worker process: pglogical manager 12141 (PID 91) exited with exit code 0
database_master_1     | LOG:  unregistering background worker "pglogical manager 12141"
database_master_1     | LOG:  registering background worker "pglogical manager 16388"
database_master_1     | LOG:  starting background worker process "pglogical manager 16388"
database_master_1     | LOG:  manager worker [92] at slot 0 generation 3 detaching cleanly
database_master_1     | LOG:  worker process: pglogical manager 16388 (PID 92) exited with exit code 0
database_master_1     | LOG:  unregistering background worker "pglogical manager 16388"
database_master_1     | LOG:  registering background worker "pglogical manager 16385"
database_master_1     | LOG:  starting background worker process "pglogical manager 16385"
database_master_1     | LOG:  manager worker [93] at slot 0 generation 4 detaching cleanly
database_master_1     | LOG:  worker process: pglogical manager 16385 (PID 93) exited with exit code 0
database_master_1     | LOG:  unregistering background worker "pglogical manager 16385"

# LOADED A DUMP WITH PG RESTORE

mydb=# CREATE EXTENSION pglogical_origin;
ERROR:  extension "pglogical_origin" already exists
mydb=# CREATE EXTENSION pglogical;
ERROR:  extension "pglogical" already exists
mydb=# SELECT pglogical.create_node(
mydb(#     node_name := 'master_provider',
mydb(#     dsn := 'host=provider_ip port=5432 dbname=mydb user=postgres password=mysecretpassword'
mydb(# );
 create_node 
-------------
  1825523624
(1 record)

mydb=# SELECT pglogical.replication_set_add_all_tables('default', schema_names:= ARRAY['public'], synchronize_data := true);
 replication_set_add_all_tables 
--------------------------------
 t
(1 record)

mydb=# SELECT pglogical.replication_set_add_all_sequences('default', schema_names:= ARRAY['public'], synchronize_data := true);
 replication_set_add_all_sequences 
-----------------------------------
 t
(1 record)
atabase_master_1     | LOG:  registering background worker "pglogical manager 1"
database_master_1     | LOG:  starting background worker process "pglogical manager 1"
database_master_1     | LOG:  manager worker [426] at slot 0 generation 10 detaching cleanly
database_master_1     | LOG:  worker process: pglogical manager 1 (PID 426) exited with exit code 0
database_master_1     | LOG:  unregistering background worker "pglogical manager 1"
database_master_1     | LOG:  registering background worker "pglogical manager 12141"
database_master_1     | LOG:  starting background worker process "pglogical manager 12141"
database_master_1     | LOG:  manager worker [427] at slot 0 generation 11 detaching cleanly
database_master_1     | LOG:  worker process: pglogical manager 12141 (PID 427) exited with exit code 0
database_master_1     | LOG:  unregistering background worker "pglogical manager 12141"
database_master_1     | LOG:  registering background worker "pglogical manager 16388"
database_master_1     | LOG:  starting background worker process "pglogical manager 16388"
database_master_1     | LOG:  manager worker [428] at slot 0 generation 12 detaching cleanly
database_master_1     | LOG:  worker process: pglogical manager 16388 (PID 428) exited with exit code 0
database_master_1     | LOG:  unregistering background worker "pglogical manager 16388"
database_master_1     | LOG:  registering background worker "pglogical manager 16385"
database_master_1     | LOG:  starting background worker process "pglogical manager 16385"
database_master_1     | LOG:  manager worker [429] at slot 0 generation 13 detaching cleanly
database_master_1     | LOG:  worker process: pglogical manager 16385 (PID 429) exited with exit code 0

Postgres 9.6

database_master_96_1  | PostgreSQL init process complete; ready for start up.
database_master_96_1  | 
database_master_96_1  | LOG:  database system was shut down at 2016-10-26 15:49:44 GMT
database_master_96_1  | LOG:  MultiXact member wraparound protections are now enabled
database_master_96_1  | LOG:  database system is ready to accept connections
database_master_96_1  | LOG:  starting pglogical supervisor
database_master_96_1  | LOG:  autovacuum launcher started
database_master_96_1  | LOG:  manager worker [64] at slot 0 generation 1 detaching cleanly
database_master_96_1  | LOG:  manager worker [65] at slot 0 generation 2 detaching cleanly
database_master_96_1  | LOG:  manager worker [66] at slot 0 generation 3 detaching cleanly
database_master_96_1  | LOG:  manager worker [67] at slot 0 generation 4 detaching cleanly
mydb=# select version();
                                         version                                          
------------------------------------------------------------------------------------------
 PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
(1 record)

mydb=# CREATE EXTENSION pglogical;
CREATE EXTENSION
mydb=# SELECT pglogical.create_node(
mydb(#     node_name := 'subscriber1',
mydb(#     dsn := 'host=subscriber_ip port=5432 dbname=mydb user=postgres password=mysecretpassword'
mydb(# );
 create_node 
-------------
   330520249
(1 record)

mydb=# SELECT pglogical.create_subscription(
mydb(#     subscription_name := 'subscription1',
mydb(#     provider_dsn := 'host=provider_ip port=5432 dbname=mydb user=postgres password=mysecretpassword',
mydb(#     synchronize_structure := true,
mydb(#     synchronize_data := true
mydb(# );
 create_subscription 
---------------------
          1763399739
(1 record)

mydb=# select count(1) from activity_logs;
 count 
-------
     0
(1 record)

database_master_96_1  | LOG:  manager worker [70] at slot 0 generation 5 detaching cleanly
database_master_96_1  | LOG:  manager worker [71] at slot 0 generation 6 detaching cleanly
database_master_96_1  | LOG:  manager worker [72] at slot 0 generation 7 detaching cleanly
database_master_96_1  | LOG:  manager worker [73] at slot 0 generation 8 detaching cleanly
database_master_96_1  | LOG:  starting pglogical database manager for database mydb
database_master_96_1  | LOG:  starting apply for subscription subscription1
database_master_96_1  | LOG:  manager worker [87] at slot 2 generation 1 detaching cleanly
database_master_96_1  | LOG:  manager worker [88] at slot 2 generation 2 detaching cleanly
database_master_96_1  | LOG:  manager worker [89] at slot 2 generation 3 detaching cleanly
database_master_96_1  | LOG:  manager worker [90] at slot 2 generation 4 detaching cleanly

Provider log after subscription

database_master_1     | LOG:  unregistering background worker "pglogical manager 16385"
database_master_1     | LOG:  registering background worker "pglogical manager 16408"
database_master_1     | LOG:  starting background worker process "pglogical manager 16408"
database_master_1     | LOG:  starting pglogical database manager for database caremessage
database_master_1     | LOG:  logical decoding found consistent point at 0/969430D8
database_master_1     | DETAIL:  There are no running transactions.
database_master_1     | LOG:  exported logical decoding snapshot: "00000775-1" with 0 transaction IDs
database_master_1     | LOG:  unexpected EOF on client connection with an open transaction
database_master_1     | LOG:  logical decoding found consistent point at 0/9694B250
database_master_1     | DETAIL:  There are no running transactions.
database_master_1     | LOG:  exported logical decoding snapshot: "00000776-1" with 0 transaction IDs
database_master_1     | LOG:  starting logical decoding for slot "pgl_caremessage_master_provider_subscription1"
database_master_1     | DETAIL:  streaming transactions committing after 0/9694B288, reading WAL from 0/9694B250
database_master_1     | LOG:  logical decoding found consistent point at 0/9694B250
database_master_1     | DETAIL:  There are no running transactions.

Manually calling alter_subscription_resynchronize_table

# select pglogical.alter_subscription_resynchronize_table('subscription1', 'activity_logs');
 alter_subscription_resynchronize_table 
----------------------------------------
 t
database_master_96_1  | LOG:  starting sync of table public.activity_logs for subscriber subscription1
database_master_96_1  | LOG:  finished sync of table public.activity_logs for subscriber subscription1
database_master_96_1  | LOG:  sync worker [138] at slot 2 generation 5 detaching cleanly
database_master_1     | LOG:  logical decoding found consistent point at 0/9694B4E0
database_master_1     | DETAIL:  There are no running transactions.
database_master_1     | LOG:  exported logical decoding snapshot: "00000777-1" with 0 transaction IDs
PJMODOS commented 7 years ago

I would like to see the contents of pglogical.local_sync_status after the create_subscription when the existing data was not copied.

raivil commented 7 years ago

Here it is.

# select * from pglogical.local_sync_status;
 sync_kind | sync_subid | sync_nspname | sync_relname | sync_status
-----------+------------+--------------+--------------+-------------
 f         | 1763399739 |              |              | r

and here after executing # select pglogical.alter_subscription_resynchronize_table('subscription1', 'activity_logs');;

# select * from pglogical.local_sync_status;
 sync_kind | sync_subid | sync_nspname | sync_relname  | sync_status
-----------+------------+--------------+---------------+-------------
 f         | 1763399739 |              |               | r
 d         | 1763399739 | public       | activity_logs | r
jfinzel commented 7 years ago

FWIW, I have the exact same issue on all pglogical installs (1.2.1) on versions 9.4 and 9.5 The only function that actually synchronizes tables is pglogical.alter_subscription_resynchronize_table. Selecting synchronize_data := true upon subscription creation does nothing but starts replication from that point forward, and does no copies of source tables.

PJMODOS commented 7 years ago

I wonder if this issue has same root cause as what was fixed by 31456307d. Can you try to REINDEX pglogical.local_node; after creating the node on provider and see if it helps?

PJMODOS commented 7 years ago

Or just upgrade to 1.2.2

Envek commented 7 years ago

Hi everyone, sorry for the late reply.

Tried one more time on fresh install of PostgreSQL 9.5.5 and pglogical 1.2.2 on Ubuntu 14.04 (on both producer and subscriber), got another error.

Producer:

CREATE EXTENSION pglogical;
SELECT pglogical.create_node('test-1', 'host=192.168.57.2 port=5432 dbname=smp user=postgres password=qwerty$4');
SELECT pglogical.replication_set_add_all_tables('default', schema_names := ARRAY['public'], synchronize_data := true);
SELECT pglogical.replication_set_add_all_sequences('default', schema_names:= ARRAY['public'], synchronize_data := true);

Subscriber:

CREATE EXTENSION pglogical;
SELECT pglogical.create_node('test-2', 'host=192.168.57.3 port=5432 dbname=smp user=postgres password=qwerty$4');
SELECT pglogical.create_subscription(
  subscription_name     := 'test_replication',
  provider_dsn          := 'host=192.168.57.2 port=5432 dbname=smp user=postgres password=qwerty$4',
  synchronize_structure := false,
  synchronize_data      := true
);

After that immediately next messages appeared in logs:

Producer:

2017-01-10 18:44:29 MSK [1097-1] LOG:  database system was shut down at 2017-01-10 18:43:40 MSK
2017-01-10 18:44:29 MSK [1097-2] LOG:  MultiXact member wraparound protections are now enabled
2017-01-10 18:44:29 MSK [1096-1] LOG:  database system is ready to accept connections
2017-01-10 18:44:29 MSK [1103-1] LOG:  starting pglogical supervisor
2017-01-10 18:44:29 MSK [1101-1] LOG:  autovacuum launcher started
2017-01-10 18:44:29 MSK [1104-1] LOG:  manager worker [1104] at slot 0 generation 1 detaching cleanly
2017-01-10 18:44:29 MSK [1111-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-01-10 18:44:30 MSK [1115-1] LOG:  manager worker [1115] at slot 0 generation 2 detaching cleanly
2017-01-10 18:44:31 MSK [1163-1] LOG:  manager worker [1163] at slot 0 generation 3 detaching cleanly
2017-01-10 18:44:32 MSK [1198-1] LOG:  manager worker [1198] at slot 0 generation 4 detaching cleanly
2017-01-10 18:49:33 MSK [1423-1] LOG:  manager worker [1423] at slot 0 generation 5 detaching cleanly
2017-01-10 18:49:34 MSK [1424-1] LOG:  manager worker [1424] at slot 0 generation 6 detaching cleanly
2017-01-10 18:49:35 MSK [1425-1] LOG:  starting pglogical database manager for database smp
2017-01-10 18:49:36 MSK [1426-1] LOG:  manager worker [1426] at slot 1 generation 1 detaching cleanly
2017-01-10 18:52:48 MSK [1443-1] postgres@smp LOG:  logical decoding found consistent point at 0/5976618
2017-01-10 18:52:48 MSK [1443-2] postgres@smp DETAIL:  There are no running transactions.
2017-01-10 18:52:48 MSK [1443-3] postgres@smp LOG:  exported logical decoding snapshot: "000023B5-1" with 0 transaction IDs
2017-01-10 18:52:48 MSK [1444-1] postgres@smp ERROR:  SET TRANSACTION SNAPSHOT must be called before any query
2017-01-10 18:52:48 MSK [1444-2] postgres@smp STATEMENT:  BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;
        SET DATESTYLE = ISO;
        SET INTERVALSTYLE = POSTGRES;
        SET extra_float_digits TO 3;
        SET statement_timeout = 0;
        SET lock_timeout = 0;
        SET TRANSACTION SNAPSHOT '000023B5-1';

2017-01-10 18:52:48 MSK [1444-3] postgres@smp LOG:  could not receive data from client: Connection reset by peer
2017-01-10 18:52:48 MSK [1443-4] postgres@smp LOG:  could not receive data from client: Connection reset by peer
2017-01-10 18:52:48 MSK [1443-5] postgres@smp LOG:  unexpected EOF on client connection with an open transaction

Consumer:

2017-01-10 18:46:50 MSK [1094-1] LOG:  database system was shut down at 2017-01-10 18:46:35 MSK
2017-01-10 18:46:50 MSK [1094-2] LOG:  MultiXact member wraparound protections are now enabled
2017-01-10 18:46:50 MSK [1076-1] LOG:  database system is ready to accept connections
2017-01-10 18:46:50 MSK [1100-1] LOG:  starting pglogical supervisor
2017-01-10 18:46:50 MSK [1098-1] LOG:  autovacuum launcher started
2017-01-10 18:46:50 MSK [1101-1] LOG:  manager worker [1101] at slot 0 generation 1 detaching cleanly
2017-01-10 18:46:50 MSK [1105-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-01-10 18:46:51 MSK [1115-1] LOG:  manager worker [1115] at slot 0 generation 2 detaching cleanly
2017-01-10 18:46:52 MSK [1181-1] LOG:  manager worker [1181] at slot 0 generation 3 detaching cleanly
2017-01-10 18:46:53 MSK [1198-1] LOG:  manager worker [1198] at slot 0 generation 4 detaching cleanly
2017-01-10 18:51:11 MSK [1644-1] LOG:  manager worker [1644] at slot 0 generation 5 detaching cleanly
2017-01-10 18:51:12 MSK [1645-1] LOG:  manager worker [1645] at slot 0 generation 6 detaching cleanly
2017-01-10 18:51:13 MSK [1646-1] LOG:  starting pglogical database manager for database smp
2017-01-10 18:51:14 MSK [1647-1] LOG:  manager worker [1647] at slot 1 generation 1 detaching cleanly
2017-01-10 18:52:48 MSK [1656-1] LOG:  starting apply for subscription test_replication
2017-01-10 18:52:48 MSK [1656-2] ERROR:  BEGIN on origin node failed: ERROR:  SET TRANSACTION SNAPSHOT must be called before any query

2017-01-10 18:52:48 MSK [1656-3] LOG:  apply worker [1656] at slot 1 generation 2 crashed
2017-01-10 18:52:48 MSK [1076-2] LOG:  worker process: pglogical apply 28111:1284107943 (PID 1656) exited with exit code 1
2017-01-10 18:52:54 MSK [1658-1] LOG:  starting apply for subscription test_replication
2017-01-10 18:52:54 MSK [1658-2] ERROR:  subscriber test_replication initialization failed during nonrecoverable step (d), please try the setup again
2017-01-10 18:52:54 MSK [1658-3] LOG:  apply worker [1658] at slot 1 generation 3 crashed
2017-01-10 18:52:54 MSK [1076-3] LOG:  worker process: pglogical apply 28111:1284107943 (PID 1658) exited with exit code 1

Output of select * from pg_stat_replication; on consumer is empty.

Output of SELECT * FROM pglogical.show_subscription_status(); on consumer:

-[ RECORD 1 ]-----+-----------------------------------------------------------------------
subscription_name | test_replication
status            | down
provider_node     | test-1
provider_dsn      | host=192.168.57.2 port=5432 dbname=smp user=postgres password=qwerty$4
slot_name         | pgl_smp_test_1_test_replication
replication_sets  | {default,default_insert_only,ddl_sql}
forward_origins   | {all}

Output of select * from pglogical.local_sync_status; on consumer:

-[ RECORD 1 ]+-----------
sync_kind    | d
sync_subid   | 1284107943
sync_nspname |
sync_relname |
sync_status  | d

I think I did something wrong this time but not sure what exactly.

PJMODOS commented 7 years ago

Hmm, do you have any other extension or modules installed? Or are you using some kind of connection pooler in front of the db?

Envek commented 7 years ago

Yes, I have latest pg_pathman installed and enabled for one table in this database. No connection pooler.

PJMODOS commented 7 years ago

Hmm, it might be possible that pathman does some initialization that prevents SET TRANSACTION SNAPSHOT from working, however I could not reproduce this even with pg_pathman installed.

raivil commented 7 years ago

I've retested with version 1.2.2 and it's working. Data was synced write after creating a subscription and on any other inserts. I have a proof of concept using docker containers available on https://github.com/raivil/pgpool_postgres_replication

PJMODOS commented 7 years ago

Ah, so this is same issue as #56. Closing then