1. Forum
  2. Usenet
  3. COMP.DATABASES.POSTGRESQL

  • archiver problem

    From Alf Normann Klausen@21:1/5 to All on Thu Sep 27 13:36:33 2018
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Laurenz Albe@21:1/5 to Alf Normann Klausen on Mon Oct 1 16:19:51 2018
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Alf Normann Klausen@21:1/5 to All on Tue Oct 2 03:59:21 2018
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)