From: | Andy Colson <andy(at)squeakycode(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: pg_logical_slot_get_changes |
Date: | 2016-08-11 18:31:12 |
Message-ID: | 8af634c3-0a75-fc48-1ab0-46910a233874@squeakycode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)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(at)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(at)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(at)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 .
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2016-08-11 19:00:48 | Re: Postgres Pain Points: 1 pg_hba conf |
Previous Message | Alvaro Herrera | 2016-08-11 18:29:06 | Re: Postgres Pain Points 2 ruby / node language drivers |