archiver problem

Thread View: comp.databases.postgresql

3 messages

[T] Back to Threads [V] Tree View [G] Group Articles

3 total messages Started by Alf Normann Klau Thu, 27 Sep 2018 13:36

#3385

Author: Alf Normann Klau
Date: Thu, 27 Sep 2018 13:36

20 lines
945 bytes

Hi,

My postgres 9.6 master database is trying to archive a WAL file that does not exist anymore. In the log I find lots of this error:

< 2018-09-27 22:25:23.027 CEST     >WARNING:  archiving transaction log file "0000000300005770000000EB" failed too many times, will try again later
< 2018-09-27 22:25:23.215 CEST     >LOG:  archive command failed with exit code 23
< 2018-09-27 22:25:23.215 CEST     >DETAIL:  The failed archive command was: rsync -a pg_xlog/0000000300005770000000EB barman@192.168.4.52:/mnt/nfs/barman/datavarehus/incoming/0000000300005770000000EB

(I am running streaming replication and also barman, this is happening on my master).

Archive parameters in postgresql.conf

[postgres@datavarehus: ~]$ grep ^arch 9.6/data/postgresql.conf
archive_mode = on
archive_command = 'rsync -a %p barman@192.168.4.52:/mnt/nfs/barman/datavarehus/incoming/%f'
[postgres@datavarehus: ~]$


How can I get out of this?

Kind regards, Alf

Re: archiver problem

#3386

Author: Laurenz Albe
Date: Mon, 01 Oct 2018 16:19

35 lines
1445 bytes

On Thu, 27 Sep 2018 13:36:33 -0700, Alf Normann Klausen wrote:
> My postgres 9.6 master database is trying to archive a WAL file that
> does not exist anymore. In the log I find lots of this error:
>
> < 2018-09-27 22:25:23.027 CEST     >WARNING:  archiving transaction log
> file "0000000300005770000000EB" failed too many times, will try again
> later < 2018-09-27 22:25:23.215 CEST     >LOG:  archive command failed
> with exit code 23 < 2018-09-27 22:25:23.215 CEST     >DETAIL:  The
> failed archive command was: rsync -a pg_xlog/0000000300005770000000EB
> barman@192.168.4.52:/mnt/nfs/barman/datavarehus/incoming/
0000000300005770000000EB
>
> (I am running streaming replication and also barman, this is happening
> on my master).
>
> Archive parameters in postgresql.conf
>
> [postgres@datavarehus: ~]$ grep ^arch 9.6/data/postgresql.conf
> archive_mode = on archive_command = 'rsync -a %p
> barman@192.168.4.52:/mnt/nfs/barman/datavarehus/incoming/%f'
>
> How can I get out of this?

You must have removed that WAL file from "pg_xlog" before it was archived.

To get rid of this error, you can temporarily set

   archive_command = '/bin/true'

Then PostgreSQL will believe that the WAL files are archived correctly,
even though they are not.

Once pg_stat_archiver shows that PostgreSQL has caught up to WAL files
that actually exist, you can restore the original setting.

After that, create a new base backup as soon as possible!

Re: archiver problem

#3387

Author: Alf Normann Klau
Date: Tue, 02 Oct 2018 03:59

31 lines
1220 bytes

mandag 1. oktober 2018 18.19.52 UTC+2 skrev Laurenz Albe følgende:
> 
> You must have removed that WAL file from "pg_xlog" before it was archived..
> 
> To get rid of this error, you can temporarily set
> 
>    archive_command = '/bin/true'
> 
> Then PostgreSQL will believe that the WAL files are archived correctly,
> even though they are not.
> 
> Once pg_stat_archiver shows that PostgreSQL has caught up to WAL files
> that actually exist, you can restore the original setting.
> 
> After that, create a new base backup as soon as possible!

Hi, thanks a lot for this answer. I would have helped me just like I wanted..

And. Yes, you are correct. My disk filled up 100% due to an archiving problem and I was stupid enough to run the pg_archivecleanup command. I later understod that this only should be used on the replica node.

I tried to stop the archiving and start it againg, but it did not help. So in the end I ended up stopping the master, promoting my replica node and change the WAL streaming direction.

Next time (hope there will be no next time) I will set the archive_command to '/bin/true' instead, that is a great solution. Thanks a lot Laurenz!!

Kind regards, Alf

Thread Navigation

This is a paginated view of messages in the thread with full content displayed inline.

Messages are displayed in chronological order, with the original post highlighted in green.

Use pagination controls to navigate through all messages in large threads.

Back to All Threads