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 .
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
