Re: pg_logical_slot_get_changes - Mailing list pgsql-general

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    .