Stop streaming replication

Question

I want to make my PostgreSQL master / slave streaming replication setup into a single master slave setup without replication and without HA.

How is it possible to tell master that it no longer has slave and it should not replicate its data to the slave ?

Of course it should also not keep WALs to be sent to the slave as is done when the slave is temporarily down.

Answer 1

Depending on the version of Postgres you're using, it's possible your replicatoin is done using "replication slots". If you don't have a client streaming from a replication slot anymore, you can drop the slot. Here are some useful queries:

Get disk usage per replication slot (for Postgres 9.6):

SELECT
    redo_location,
    slot_name,restart_lsn,
    round((redo_location-restart_lsn) / 1024 / 1024 / 1024, 2) AS GB_behind
FROM
    pg_control_checkpoint(),
    pg_replication_slots;

Get disk usage per replication slot (for Postgres 10+):

SELECT redo_lsn,
    slot_name,
    restart_lsn,
    round((redo_lsn-restart_lsn) / 1024 / 1024 / 1024, 2) AS GB_behind
FROM pg_control_checkpoint(),
    pg_replication_slots;

Drop replication slot:

select pg_drop_replication_slot('slot_name');

References:

• https://severalnines.com/database-blog/using-postgresql-replication-slots :

Answer 2

Here is what I did:

• on both master / slave, edit pg_hba.conf, remove:

  host replication replicator x.x.x.x/32 md5

• in master, reload config, via:

  select pg_reload_conf()

• in slave's data dir, remove file: standby.signal
• restart slave,

Then slave will keep the data, but don't replica from master any more, and slave is writable.