Error after Streaming Replication

From: Walters Che Ndoh <chendohw(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Error after Streaming Replication
Date: 2021-02-18 14:32:04
Message-ID: CAJ=HdqJjRq9XiNDEjDxcro1rvQi=AbJdUB0wWP53qkC7s_S+Kw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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=psql2021-02-18 07:01:37
MST [38376]: [3-1] ERROR: could not open directory
"pg_xlog/archive_status": No such file or directory2021-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=data2021-02-18 07:03:21 MST
[39035]: [3-1] ERROR: requested WAL segment 000000010000089700000019 has
already been removed2021-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=589922021-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=syncmanager2021-02-18
07:03:28 MST [39131]: [3-1] ERROR: function
pg_last_xlog_receive_location() does not exist at character 182021-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 removed2021-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=589002021-02-18 06:58:51
MST [37861]: [1-1] LOG: connection received: host=10.112.30.4
port=589002021-02-18 06:58:51 MST [37861]: [2-1] LOG: replication
connection authorized: user=replicator application_name=data2021-02-18
06:58:51 MST [37861]: [3-1] ERROR: requested WAL segment
000000010000089700000019 has already been removed2021-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=589002021-02-18 06:58:56
MST [37873]: [1-1] LOG: connection received: host=10.112.30.12
port=589622021-02-18 06:58:56 MST [37873]: [2-1] LOG: replication
connection authorized: user=replicator application_name=data2021-02-18
06:58:56 MST [37873]: [3-1] ERROR: requested WAL segment
000000010000089700000019 has already been removed2021-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=589002021-02-18 06:59:01
MST [37886]: [1-1] LOG: connection received: host=10.112.30.12
port=589002021-02-18 06:59:01 MST [37886]: [2-1] LOG: replication
connection authorized: user=replicator application_name=data2021-02-18
06:59:01 MST [37886]: [3-1] ERROR: requested WAL segment
000000010000089700000019 has already been removed*

Any help will be appreciated

Walters
*--*

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Walters Che Ndoh 2021-02-18 15:16:41 Re: Error after Streaming Replication
Previous Message Prince Pathria 2021-02-18 06:17:20 Re: Aurora PostgreSQL Support