Thread: pglogical cross subscribe

Hi all.

I have a localdb and a remotedb.

A) I'd like to send table1 and table2 from localdb to remotedb.
B) I'd like to send table3 from remotedb to localdb.

I have PG9.5 and pglogical setup, and (A) works fine.

Well, worked.  I then tried to set up (B) which seems to work, but now (A) is broken.

remotedb is logging things like:
2016-08-03 20:20:17.086 CDT,: ERROR:  cache lookup failed for replication origin 'pgl_vcstimes_katniss_subscrib73496c2'
2016-08-03 20:20:17.087 CDT,: LOG:  worker process: pglogical apply 23704565:3237263450 (PID 21981) exited with exit
code1 

I did find:
https://www.postgresql.org/message-id/1691408856.31522.1468333259045.JavaMail.zimbra@cobra.ru
which leads to this:
https://github.com/2ndQuadrant/pglogical/issues/23

But I'm not sure if that helps me, or if it does I don't understand what I'd need to do.

On remotedb, I see:
vcstimes=# select * from pglogical.show_subscription_status();
-[ RECORD 1 ]-----+-------------------------------------------------------
subscription_name | subscribe_katniss
status            | down
provider_node     | payroll_provider
provider_dsn      | host=localhost port=9090 dbname=vcstimes user=postgres
slot_name         | pgl_vcstimes_payroll_provider_subscribc0f4b85
replication_sets  | {default,default_insert_only,ddl_sql}
forward_origins   | {all}

I cannot find any description of status = down, and I don't know how to bring it back up.  Any help would be
appreciated.

Thanks,

-Andy

On 8/3/2016 8:27 PM, Andy Colson wrote:
> Hi all.
>
> I have a localdb and a remotedb.
>
> A) I'd like to send table1 and table2 from localdb to remotedb.
> B) I'd like to send table3 from remotedb to localdb.
>
> I have PG9.5 and pglogical setup, and (A) works fine.
>
> Well, worked.  I then tried to set up (B) which seems to work, but now
> (A) is broken.


Since pglogical wont work, I dropped part (B) and went back to just (A).

To implement part (B) I figured I could schedule a perl script to pull
changes.


On remote db I:
select pg_create_logical_replication_slot('custom_slot','decoder_raw');

And wrote myself a perl script to run in a cron job every hour that would:
SELECT * FROM pg_logical_slot_get_changes('custom_slot', null, null);

and send it to localdb.

Super.  Except pg_xlog grows and grows.  pg_replication_slots shows it
as active=false, and restart_lsn never changes.  Even after calling
pg_logical_slot_get_changes many times.

If I: select pg_drop_replication_slot('custom_slot');

pg_xlog shrinks right away.

Does that mean I cannot poll pg_logical_slot_get_changes()?  Do I have
to use something like pg_recvlogical or pglogical?


Thanks for your time,

-Andy

After testing this more, maybe it does work ok just calling
pg_logical_slot_get_changes().

I'm making the assumption that you'd like
pg_replication_slots.restart_lsn to be close to
pg_current_xlog_location(), correct?

The further apart they are, the more pg_xlog you have to store, yes?

When I had a gig of space in pg_xlog, I know I called
pg_logical_slot_get_changes() but it did not make restart_lsn progress.
Could it have gotten stuck some how?  As soon as I dropped that slot
pg_xlog dropped to 80 meg, so I'm pretty sure that was the hang-up.

After dropping the logical slot and making a new one, and testing it
out, restart_lsn is progressing just fine.

Here is a bit of terminal history that shows the problem.  Sorry for the
formatting  (here is a pretty version http://pastebin.com/YgYuiR3U):

andy@katniss:~$ psql vcstimes
psql (9.5.3)
Type "help" for help.

vcstimes=# select * from  pg_replication_slots;
   slot_name  |   plugin    | slot_type |  datoid  | database | active |
active_pid | xmin | catalog_xmin | restart_lsn

-------------+-------------+-----------+----------+----------+--------+------------+------+--------------+--------------
  counts_slot | decoder_raw | logical   | 23704565 | vcstimes | f      |
            |      |     30293428 | 111/A235FEA0
(1 row)



root@katniss:/db/pg95# du1
1009M    ./pg_xlog
528K    ./global
0    ./pg_commit_ts
0    ./pg_dynshmem
8.0K    ./pg_notify
0    ./pg_serial
0    ./pg_snapshots
208K    ./pg_subtrans
0    ./pg_twophase
16K    ./pg_multixact
47G    ./base
4.0K    ./pg_replslot
0    ./pg_tblspc
0    ./pg_stat
152K    ./pg_stat_tmp
22M    ./pg_logical
7.3M    ./pg_clog
49G    .

andy@katniss:~$ psql vcstimes
psql (9.5.3)
Type "help" for help.

vcstimes=# select * from pg_stat_replication;
  pid | usesysid | usename | application_name | client_addr |
client_hostname | client_port | backend_start | backend_xmi

-----+----------+---------+------------------+-------------+-----------------+-------------+---------------+------------
(0 rows)

vcstimes=# select * from  pg_replication_slots ;
slot_name  |   plugin    | slot_type |  datoid  | database | active |
active_pid | xmin | catalog_xmin | restart_lsn

-------------+-------------+-----------+----------+----------+--------+------------+------+--------------+--------------
  counts_slot | decoder_raw | logical   | 23704565 | vcstimes | f      |
            |      |     30293428 | 111/A235FEA0
(1 row)

vcstimes=# select pg_current_xlog_location();
  pg_current_xlog_location
--------------------------
  111/E0D25628
(1 row)

vcstimes=# SELECT * FROM pg_logical_slot_peek_changes('counts_slot',
null, null);
    location   |   xid    |
   data

--------------+----------+----------------------------------------------------------------------------------------------
  111/E0360170 | 30330537 | INSERT INTO public.webcount (custid, type,
numfull, reccount, tblver) VALUES (2, 'W', 1, 501,
(1 row)

vcstimes=# SELECT * FROM pg_logical_slot_get_changes('counts_slot',
null, null);
    location   |   xid    |
   data

--------------+----------+----------------------------------------------------------------------------------------------
  111/E0360170 | 30330537 | INSERT INTO public.webcount (custid, type,
numfull, reccount, tblver) VALUES (2, 'W', 1, 501,
(1 row)

vcstimes=# select * from  pg_replication_slots ;
   slot_name  |   plugin    | slot_type |  datoid  | database | active |
active_pid | xmin | catalog_xmin | restart_lsn

-------------+-------------+-----------+----------+----------+--------+------------+------+--------------+--------------
  counts_slot | decoder_raw | logical   | 23704565 | vcstimes | f      |
            |      |     30293435 | 111/A235FEA0
(1 row)


vcstimes=#  select pg_drop_replication_slot('counts_slot');
  pg_drop_replication_slot
--------------------------

(1 row)

vcstimes=# select * from  pg_replication_slots ;
  slot_name | plugin | slot_type | datoid | database | active |
active_pid | xmin | catalog_xmin | restart_lsn
-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------
(0 rows)


root@katniss:/db/pg95# du1
81M    ./pg_xlog
528K    ./global
0    ./pg_commit_ts
0    ./pg_dynshmem
8.0K    ./pg_notify
0    ./pg_serial
0    ./pg_snapshots
216K    ./pg_subtrans
0    ./pg_twophase
16K    ./pg_multixact
47G    ./base
0    ./pg_replslot
0    ./pg_tblspc
0    ./pg_stat
152K    ./pg_stat_tmp
216K    ./pg_logical
7.3M    ./pg_clog
48G    .