1

strong textI have a strange case in some PostgreSQL instances that I monitor. First the software versions and setup of a specific one:

  • Windows Server 2012 Foundation
  • PostgreSQL 10.10 x64
  • archive_command = 'copy% p "D: \\ backup \\ wal \\% f" / y'

This setup worked very well for a long time, but since we upgraded PostgreSQL to newer versions (10.x), sometimes it just skips archiving some wal files. This seems to happen mainly when the server is shut down. There's no messages about errors in the logs, and also no corresponding .done file in archive_status, it just goes to the next wal file.

I would like to know if any of you had or has a problem like this, and if you had, how you solved it.

Edit for some new information on this.

Some days after skipping the file, the PostgreSQL instance seems to finally archive the "skipped" WAL file. So it's not really skipping the archiving of the file, but is delaying the archiving for a good deal of time. This potentilly creates a problem, because without that file the backup isn't complete. A failure in between the "skipping" and the actual archiving of file would make an unnaceptable amount of data to be lost. Why this particular Postgres instance do this, I don't really know.

Norba
  • 11
  • 4
  • I have a problem with WAL files still being open. 7z used in `archive_command` complains when used without [-ssw](https://sevenzip.osdn.jp/chm/cmdline/switches/shared.htm) switch to archive files open for writing. AFAIK PG flushes xlog before creating `.ready` but does not actually close the file just yet. I traced it with the debugger. I'm not sure if that is your case whether COPY works okay on files open for writing and whether it fails without proper exit code. I'm not yet convinced whether `timeout /t 60 /nobreak >nul && copy ....` would help. – mlt Jul 25 '20 at 22:26

0 Answers0