0

This is for PostgreSQL 16 on Windows.

I turned on archive_mode with archive_command = 'copy "%p" "C:\PostgreSQL\16\data\pg_wal\archive_status\%f"' and archive_timeout at 3600

Executed select pg_switch_wal(); a few times (Oracle's ALTER SYSTEM SWITCH LOGFILE??)

Now, I have 16 MB files generated in pg_wal folder. I also have files in pg_wal\archive_status folder. Some of the files are similar in pg_wal and pg_wal\archive_status. Some of the files in pg_wal\archive_status, for example, 00000001000000000000007A with 16MB size and 00000001000000000000007A.done with 0 KB in size.

I'm trying to make sense of it all. Coming from Oracle, I understand the archive logs and redo logs. I figured the files in pg_wal are redologs and files in pg_wal\archive_status is archived logs? And when I execute select pg_switch_wal();, I'm switching redo logs and the old logs are archived?

My question now is

  1. what's 00000001000000000000007A vs 00000001000000000000007A.done in pg_wal\archive_status folder?
  2. Also, when I do run pg_basebackup, will it automatically delete all the archived logs that had been backed up?

Thank you

Improve this question
1
  • 1
    'copy "%p" "C:\PostgreSQL\16\data\pg_wal\archive_status\%f"' Where on earth did you get that? That is definitely not what you should be doing,. You should be copying the files to someplace outside the data directory. And in general you should read the PostgreSQL documentation, not reading the Oracle documentation and making up things you think might be similar in PostgreSQL. Commented Jan 10, 2024 at 1:12

1 Answer 1

Reset to default
0

As mentioned by Jeff, your archive_command is dangerously wrong. You have to copy the WAL segments to a location outside the data directory, on a remote storage device that distinct from your regular database storage.

To your questions:

  • 00000001000000000000007A is a WAL segment, a file that contains redo log information. Different from Oracle, these files are not reused (that is a white lie), but new segments get created and old ones are deleted once they are archived. 00000001000000000000007A.done is a file that the archiver process creates to tell PostgreSQL that it is done archiving the WAL segment, and PostgreSQL is free to remove the segment if it wants to.

  • PostgreSQL doesn't know that there are archived WAL segments, and it does not maintain them at all. How should PostgreSQL know the semantics of what you wrote into archive_command? You have to take care of the archived WAL segments yourself.

Improve this answer
4
  • Thank you both for replying. @jjanes: I was not reading Oracle documentations and applying it to PostgreSQL. I applied Oracle terms / structures as I am learning PostgreSQL where I see similarities. As for the archive_command, I understand it needs to be out of the server, just in case the server crashes. However, I have not yet able to write it to UNC path \\<datafolder>\; and since this is a dev box, I'm temporarily archiving it to a location within pg_wal. Commented Jan 10, 2024 at 20:13
  • As for why I cannot write to UNC path; I think it's because my PostgreSQL service is running as Network Service and not an actual service account that has access to UNC path. Correct me if I'm wrong. Commented Jan 10, 2024 at 20:14
  • I know little about Windows, but that sounds right. Even without being able to write to CIFS shares elsewhere, you must not archive it in the data directory, and certainly not in pg_wal. It is better to set archive_mode = off. Commented Jan 11, 2024 at 6:59
  • Appreciate your answers. Got the archive files up to data domain now. Commented Jan 11, 2024 at 20:59

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.