0

In Microsoft SQL Server, ALTER DATABASE CURRENT SET RECOVERY SIMPLE WITH NO_WAIT; will set the recovery mode for a database to simple, so the transaction log file space can be re-used.

In Postgres, wal_level is available for that purpose.

  1. Is wal_level's minimal equivalent to SQL Server's RECOVERY SIMPLE?
  2. How to set wal_level in Postgres command? - so far, Google Search tells me that wal_level is set in a configuration file.
  3. Is it possible to set wal_level to minimal for a database? If yes, how?

Thank you.

5
  • 1
    So what does this "simple" mean? And as documented in the manual wal_level is an instance (aka "cluster") wide setting. It can't be applied to individual databases.
    – user1822
    Jan 7, 2021 at 12:04
  • @a_horse_with_no_name, "simple" means reusing transaction log file space, disabling the point in time recovery. The most recent restore point is either complete backup or the latest differential backup. Can the wal_level be set in a Postgres command?
    – hunterex
    Jan 7, 2021 at 12:12
  • In Postgres you can only do point-in-time recovery if you enable/implement WAL archiving. Setting the correct wal_level is a prerequisite for that, but it will not activate WAL archiving. If I "minimal" only means that PITR is disabled, then this is the default mode in Postgres and you don't really need to change anything.
    – user1822
    Jan 7, 2021 at 12:29
  • then this is the default mode - default was changed in postgresql 10: postgresql.org/message-id/…
    – Melkij
    Jan 7, 2021 at 13:51
  • @Melkij: the wal_level default might have changed, but it you still need to take extra steps to implement PITR
    – user1822
    Jan 7, 2021 at 14:10

1 Answer 1

0

PostgreSQL works quite differently. Transaction log files will be removed with all wal_levels (as soon as the file is full and archived and a checkpoint has completed).

If you want to live without WAL archives, and all you need is crash recovery, you can set wal_level to minimal.

2
  • how to set wal_level to minimal in a Postgres command?
    – hunterex
    Jan 7, 2021 at 12:18
  • With ALTER SYSTEM. It needs a restart. Jan 7, 2021 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.