Postgres Pro
Downloads
Home   >   mailing lists

Thread: Lingering replication slots

Lingering replication slots

From
Lou Tseng
Date:
Hi,

A newbie question: how do I delete lingering replication slots?  When creating Subscription for logical replication, the CREATE SUBSCRIPTION stuck and didn't return. After ctrl-c and aborted the command, the master database have lingering replication slots that I can't delete because the active is true. Do I just kill the pid?

Thanks!


SELECT * FROM pg_replication_slots ;

  slot_name   |  plugin  | slot_type |  datoid  |    database    | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn  | confirmed_flush_lsn 

--------------+----------+-----------+----------+----------------+-----------+--------+------------+------+--------------+--------------+---------------------

 support_sub2 | pgoutput | logical   | 28384483 | dragon_support | f         | t      |      29566 |      |     96562907 | 5108/29C1610 | 

 support_sub  | pgoutput | logical   | 28384483 | dragon_support | f         | t      |      27253 |      |     96562907 | 5108/2858880 | 

Re: Lingering replication slots

From
Adrian Klaver
Date:
On 4/3/19 9:01 AM, Lou Tseng wrote:
> Hi,
> 
> A newbie question: how do I delete lingering replication slots?  When 
> creating Subscription for logical replication, the CREATE SUBSCRIPTION 
> stuck and didn't return. After ctrl-c and aborted the command, the 
> master database have lingering replication slots that I can't delete 
> because the active is true. Do I just kill the pid?
> 
> Thanks!
> 
> 
> SELECT * FROM pg_replication_slots ;
> 
> slot_name |plugin| slot_type |datoid|database| temporary | active | 
> active_pid | xmin | catalog_xmin | restart_lsn| confirmed_flush_lsn
> 
>
--------------+----------+-----------+----------+----------------+-----------+--------+------------+------+--------------+--------------+---------------------
> 
> support_sub2 | pgoutput | logical | 28384483 | dragon_support | f | 
> t|29566 || 96562907 | 5108/29C1610 |
> 
> support_sub| pgoutput | logical | 28384483 | dragon_support | f | 
> t|27253 || 96562907 | 5108/2858880 |
> 


See below(read full description at link):

https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-REPLICATION

pg_drop_replication_slot(slot_name name)


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Lingering replication slots

From
Lou Tseng
Date:
Thank you.  Wasn't able to drop replication slot because it's still active.  Arjun suggested pg_terminate_backend() and it did the trick.

Thanks a lot!

Lou Tseng

ltseng@advancedpricing.com
Advanced Medical Pricing Solutions
35 Technology Parkway South, Suite. 100
Peachtree Corners, GA 30092

From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Wednesday, April 3, 2019 11:27 AM
To: Lou Tseng; pgsql-general@postgresql.org
Subject: Re: Lingering replication slots
 
On 4/3/19 9:01 AM, Lou Tseng wrote:
> Hi,
>
> A newbie question: how do I delete lingering replication slots?  When
> creating Subscription for logical replication, the CREATE SUBSCRIPTION
> stuck and didn't return. After ctrl-c and aborted the command, the
> master database have lingering replication slots that I can't delete
> because the active is true. Do I just kill the pid?
>
> Thanks!
>
>
> SELECT * FROM pg_replication_slots ;
>
> slot_name |plugin| slot_type |datoid|database| temporary | active |
> active_pid | xmin | catalog_xmin | restart_lsn| confirmed_flush_lsn
>
> --------------+----------+-----------+----------+----------------+-----------+--------+------------+------+--------------+--------------+---------------------
>
> support_sub2 | pgoutput | logical | 28384483 | dragon_support | f |
> t|29566 || 96562907 | 5108/29C1610 |
>
> support_sub| pgoutput | logical | 28384483 | dragon_support | f |
> t|27253 || 96562907 | 5108/2858880 |
>


See below(read full description at link):

https://www.postgresql.org/docs/11/functions-admin.html#FUNCTIONS-REPLICATION

pg_drop_replication_slot(slot_name name)


--
Adrian Klaver
adrian.klaver@aklaver.com