Hello All,
I am currently using PostgreSQL Community version 10.3 and then using this instance, I am doing logical replication(using PGLOGICAL 2.2 plugin) and it is working perfectly fine as per my use-case based on PostgreSQL to PostgreSQL replication.
The use-case here is I want to utilize the plugin to send data to my different application utilizing this data via this replication methodology. The reason for this is the replication_sets concepts that is available which has tables attached to the slots. If I understand correctly, the replication slot will be decoding everything from the WALs. but the slot will not be storing all the transactions that it decodes, but instead only the tables in the replication sets for this slot, which is what I am looking for.
I did look at the following code base:
And the presentation:
But when I try to do peek_changes on my own slot, I do not see the actual transactions as mentioned here, but I just see BEGIN COMMT and LSN position changing and not the actual transactions.
Additionally, by changing the log_min_duration_statement to 0, I see that, when I create the subscriber, I see that following SQLs are executed on the Publisher end:
----------------------------------------------------------------------------------------------
2018-06-14 07:03:06.499 UTC [16696] DEBUG: received replication command: CREATE_REPLICATION_SLOT "pgl_postgres_publisher_subscriptionter" LOGICAL pglogical_output
2018-06-14 07:03:06.509 UTC [16696] DEBUG: snapshot of 0+0 running transaction ids (lsn 3D1/FF49B928 oldest xid 121331 latest complete 121330 next xid 121331)
*****************************************
2018-06-14 07:11:23.902 UTC [18490] DEBUG: received replication command: IDENTIFY_SYSTEM
2018-06-14 07:11:23.903 UTC [18490] DEBUG: received replication command: START_REPLICATION SLOT "pgl_postgres_publisher_subscriptionter" LOGICAL 3D1/FF4A2F28 (expected_encoding 'UTF8', min_proto_version '1', max_proto_version '1', startup_params_format '1', "binary.want_internal_basetypes" '1', "binary.want_binary_basetypes" '1', "binary.basetypes_major_version" '1000', "binary.sizeof_datum" '8', "binary.sizeof_int" '4', "binary.sizeof_long" '8', "binary.bigendian" '0', "binary.float4_byval" '1', "binary.float8_byval" '1', "binary.integer_datetimes" '0', "hooks.setup_function" 'pglogical.pglogical_hooks_setup', "pglogical.forward_origins" '"all"', "pglogical.replication_set_names" 'sets', "relmeta_cache_size" '-1', pg_version '100003', pglogical_version '2.2.0', pglogical_version_num '20200', pglogical_apply_pid '16759')
----------------------------------------------------------------------------------------------
The reason I am mentioning this is because in the above command, I see the replication sets also attached to the slots when replication is initiated. But when I try to execute the same on my end, I see this error:
-------------------------------------------------------------
postgres=# START_REPLICATION SLOT "demo_slot" LOGICAL 3D1/FF5F51C0 (expected_encoding 'UTF8', min_proto_version _format '1', "binary.want_internal_basetypes" '1', "binary.want_binary_basetypes" '1', "binary.basetypes_major_v "binary.sizeof_int" '4', "binary.sizeof_long" '8', "binary.bigendian" '0', "binary.float4_byval" '1', "binary.fs" '0', "hooks.setup_function" 'pglogical.pglogical_hooks_setup', "pglogical.forward_origins" '"all"', "pglogicacache_size" '-1', pg_version '100003', pglogical_version '2.2.0', pglogical_version_num '20200', pglogical_apply
unexpected PQresultStatus: 8
-------------------------------------------------------------
Hence, if someone can please guide me here as to how can I achieve my goal of:
=> Using pglogical and replication sets for sending data to other applications
=> Using pglogical and replication sets for sending data to other applications
=> Why peek changes is not showing the transactions? Same thing can bee seen for pgoutput as well.
Please review and share your comments.
Thanks and Regards,
Abhinav Singh
