How to delete replication slot in postgres 9.4

Question

I have replication slot which I want to delete but when I do delete I got an error that I can't delete from view. Any ideas?

postgres=# SELECT * FROM pg_replication_slots ;
  slot_name   |    plugin    | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn
--------------+--------------+-----------+--------+----------+--------+------+--------------+-------------
 bottledwater | bottledwater | logical   |  12141 | postgres | t      |      |       374036 | E/FE8D9010
(1 row)

postgres=# delete from pg_replication_slots;
ERROR:  cannot delete from view "pg_replication_slots"
DETAIL:  Views that do not select from a single table or view are not automatically updatable.
HINT:  To enable deleting from the view, provide an INSTEAD OF DELETE trigger or an unconditional ON DELETE DO INSTEAD rule.
postgres=#

I found command pg_drop_replication_slot(slot_name name) http://www.postgresql.org/docs/9.4/static/functions-admin.html#FUNCTIONS-REPLICATION but when I do postgres=# select * from pg_drop_replication_slot('bottledwater'); I got ERROR: replication slot "bottledwater" is already active — Igor Barinov, Jun 15 '15 at 21:18

score 109 · Accepted Answer · edited Dec 06 '17 at 15:50

109

Use pg_drop_replication_slot:

select pg_drop_replication_slot('bottledwater');

See the docs and this blog.

The replication slot must be inactive, i.e. no active connections. So if there's a streaming replica using the slot you must stop the streaming replica. Or you can change its recovery.conf so it doesn't use a slot anymore and restart it.

edited Dec 06 '17 at 15:50

mss

1,804
1
17
18

answered Jun 16 '15 at 00:27

Craig Ringer

307,061
76
688
778

humm... the link to the blog appears to be no more. – neouser99 Mar 09 '17 at 20:03
The blog post can now be found here: http://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-replication-phydical-slots/ – Drew Bloechl May 22 '17 at 19:16
I cant drop on the primary too... the error is that ERROR: replication slot does not exist select * from pg_replication_slots returns nothing. – Luciano Andress Martini Oct 10 '18 at 14:32

Yann Vo · Answer 2 · 2019-08-06T12:45:06.833

31

As a complement to the accepted answer, I'd like to mention that following command will not fail in case the slot does not exist (this was useful for me because I scripted that).

select pg_drop_replication_slot(slot_name) from pg_replication_slots where slot_name = 'bottledwater';

edited Aug 06 '19 at 12:45

answered Jun 14 '19 at 14:36

Yann Vo

1,793
1
15
10

How to delete replication slot in postgres 9.4

2 Answers2