Re: Error after Streaming Replication

[Walters Che Ndoh <chendohw@xxxxxxxxx>]

Just to add...all these errors are appearing on the replica

On Thu, Feb 18, 2021 at 7:32 AM Walters Che Ndoh <chendohw@xxxxxxxxx> wrote:

Hello All!

Please i need help with the below error...if someone has come across it before. 

For this one  ERROR:  requested WAL segment 000000010000089700000019 has already been removed i have come across it before and solved it by increasing to wal_keep_segments to reasonably high value and it worked for me. But this time i did same but still see the error.. so wondering what i am doing wrong.

My database is running on postgres 12 and i am wondering why i am seeing  ERROR:  could not open directory "pg_xlog/archive_status": No such file or directory and ERROR:  function pg_last_xlog_receive_location() does not exist at character 18

other infos:
database size = 213 gb

pg_wal directory size = 200gb plus

wal_keep_segments = increased from 512 to 2000. but didn't resolve error
Wal_level = replica

2021-02-18 07:01:37 MST [38376]: [2-1] LOG:  connection authorized: user=postgres database=postgres application_name=psql
2021-02-18 07:01:37 MST [38376]: [3-1] ERROR:  could not open directory "pg_xlog/archive_status": No such file or directory
2021-02-18 07:01:37 MST [38376]: [4-1] STATEMENT:  BEGIN;SET statement_timeout=30000;COMMIT;SELECT count(*) AS count FROM pg_ls_dir('pg_xlog/archive_status') WHERE pg_ls_dir ~ E'^[0-9A-F]{24}.ready$'

2021-02-18 07:03:21 MST [39035]: [2-1] LOG:  replication connection authorized: user=replicator application_name=data
2021-02-18 07:03:21 MST [39035]: [3-1] ERROR:  requested WAL segment 000000010000089700000019 has already been removed
2021-02-18 07:03:21 MST [39035]: [4-1] LOG:  disconnection: session time: 0:00:00.289 user=replicator database= host=10.112.30.12 port=58992
2021-02-18 07:03:28 MST [39131]: [1-1] LOG:  connection received: host=[local]
2021-02-18 07:03:28 MST [39131]: [2-1] LOG:  connection authorized: user=postgres database=syncmanager
2021-02-18 07:03:28 MST [39131]: [3-1] ERROR:  function pg_last_xlog_receive_location() does not exist at character 18
2021-02-18 07:03:28 MST [39131]: [4-1] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2021-02-18 07:03:28 MST [39131]: [5-1] STATEMENT:  SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay;

2021-02-18 06:58:46 MST [37849]: [3-1] ERROR:  requested WAL segment 000000010000089700000019 has already been removed
2021-02-18 06:58:46 MST [37849]: [4-1] LOG:  disconnection: session time: 0:00:00.276 user=replicator database= host=10.112.30.12 port=58900
2021-02-18 06:58:51 MST [37861]: [1-1] LOG:  connection received: host=10.112.30.4 port=58900
2021-02-18 06:58:51 MST [37861]: [2-1] LOG:  replication connection authorized: user=replicator application_name=data
2021-02-18 06:58:51 MST [37861]: [3-1] ERROR:  requested WAL segment 000000010000089700000019 has already been removed
2021-02-18 06:58:51 MST [37861]: [4-1] LOG:  disconnection: session time: 0:00:00.244 user=replicator database= host=10.112.30.12 port=58900
2021-02-18 06:58:56 MST [37873]: [1-1] LOG:  connection received: host=10.112.30.12 port=58962
2021-02-18 06:58:56 MST [37873]: [2-1] LOG:  replication connection authorized: user=replicator application_name=data
2021-02-18 06:58:56 MST [37873]: [3-1] ERROR:  requested WAL segment 000000010000089700000019 has already been removed
2021-02-18 06:58:56 MST [37873]: [4-1] LOG:  disconnection: session time: 0:00:00.227 user=replicator database= host=10.112.30.12 port=58900
2021-02-18 06:59:01 MST [37886]: [1-1] LOG:  connection received: host=10.112.30.12 port=58900
2021-02-18 06:59:01 MST [37886]: [2-1] LOG:  replication connection authorized: user=replicator application_name=data
2021-02-18 06:59:01 MST [37886]: [3-1] ERROR:  requested WAL segment 000000010000089700000019 has already been removed

Any help will be appreciated

Walters

--