Home • Explore • Blog • CI
☰



postgresql
45th chunk of `doc/src/sgml/config.sgml`
e8b267b11a036f62c863969b5ca6d29ab18f0d06114365490000000100000fa4
 the information logged at all lower
        levels.  This parameter can only be set at server start.
       </para>
       <para>
        The <literal>minimal</literal> level generates the least WAL
        volume.  It logs no row information for permanent relations
        in transactions that create or
        rewrite them.  This can make operations much faster (see
        <xref linkend="populate-pitr"/>).  Operations that initiate this
        optimization include:
        <simplelist>
         <member><command>ALTER ... SET TABLESPACE</command></member>
         <member><command>CLUSTER</command></member>
         <member><command>CREATE TABLE</command></member>
         <member><command>REFRESH MATERIALIZED VIEW</command>
         (without <option>CONCURRENTLY</option>)</member>
         <member><command>REINDEX</command></member>
         <member><command>TRUNCATE</command></member>
        </simplelist>
        However, minimal WAL does not contain sufficient information for
        point-in-time recovery, so <literal>replica</literal> or
        higher must be used to enable continuous archiving
        (<xref linkend="guc-archive-mode"/>) and streaming binary replication.
        In fact, the server will not even start in this mode if
        <varname>max_wal_senders</varname> is non-zero.
        Note that changing <varname>wal_level</varname> to
        <literal>minimal</literal> makes previous base backups unusable
        for point-in-time recovery and standby servers.
       </para>
       <para>
        In <literal>logical</literal> level, the same information is logged as
        with <literal>replica</literal>, plus information needed to
        extract logical change sets from the WAL. Using a level of
        <literal>logical</literal> will increase the WAL volume, particularly if many
        tables are configured for <literal>REPLICA IDENTITY FULL</literal> and
        many <command>UPDATE</command> and <command>DELETE</command> statements are
        executed.
       </para>
       <para>
        In releases prior to 9.6, this parameter also allowed the
        values <literal>archive</literal> and <literal>hot_standby</literal>.
        These are still accepted but mapped to <literal>replica</literal>.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry id="guc-fsync" xreflabel="fsync">
      <term><varname>fsync</varname> (<type>boolean</type>)
      <indexterm>
       <primary><varname>fsync</varname> configuration parameter</primary>
      </indexterm>
      </term>
      <listitem>
       <para>
        If this parameter is on, the <productname>PostgreSQL</productname> server
        will try to make sure that updates are physically written to
        disk, by issuing <function>fsync()</function> system calls or various
        equivalent methods (see <xref linkend="guc-wal-sync-method"/>).
        This ensures that the database cluster can recover to a
        consistent state after an operating system or hardware crash.
       </para>

       <para>
        While turning off <varname>fsync</varname> is often a performance
        benefit, this can result in unrecoverable data corruption in
        the event of a power failure or system crash.  Thus it
        is only advisable to turn off <varname>fsync</varname> if
        you can easily recreate your entire database from external
        data.
       </para>

       <para>
        Examples of safe circumstances for turning off
        <varname>fsync</varname> include the initial loading of a new
        database cluster from a backup file, using a database cluster
        for processing a batch of data after which the database
        will be thrown away and recreated,
        or for a read-only database clone which
        gets recreated frequently and is not used for failover.  High
        quality hardware alone is not a sufficient justification for
        turning off <varname>fsync</varname>.
       </para>

       <para>
Title: WAL Levels, fsync, and Data Integrity
Summary
This section elaborates on `wal_level`, detailing the implications of each setting ('minimal', 'replica', and 'logical') on WAL volume and recovery capabilities. 'minimal' WAL omits row information for certain operations, speeding them up but preventing point-in-time recovery without 'replica' or higher. 'logical' WAL includes information for logical decoding, increasing WAL volume, particularly with `REPLICA IDENTITY FULL`. The section also covers the `fsync` parameter, which ensures data is physically written to disk using `fsync()` system calls. Disabling `fsync` improves performance but risks unrecoverable data corruption in crashes. It's advisable only when the database can be easily recreated, such as during initial loading, batch processing, or for read-only clones.