Tag Archives: PostgreSQL vs Oracle

Types of Shutdown in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Types of Shutdown in PostgreSQL

PostgreSQL Shutdown TypeOracleBehavior
Smart (SIGTERM)SHUTDOWN NORMALThe server stops accepting new connections but allows existing sessions to continue running normally. PostgreSQL shuts down only after all active sessions disconnect and their transactions complete successfully.
Fast (SIGINT – Default)SHUTDOWN IMMEDIATEThe server stops accepting new connections and immediately terminates all active sessions. Any uncommitted transactions are rolled back, and PostgreSQL performs a clean shutdown gracefully.
Immediate (SIGQUIT)SHUTDOWN ABORTThe server terminates all processes instantly without performing a proper shutdown. No cleanup or checkpoint occurs, so PostgreSQL performs crash recovery by replaying WAL files during the next startup.
Shutdown TypeSignalCommand ExampleBehavior
SmartSIGTERMpg_ctl stop -D /pgData -m smartStops new connections, waits for existing sessions to finish normally, then shuts down.
Fast (Default)SIGINTpg_ctl stop -D /pgdata -m fastStops new connections, terminates active sessions, rolls back uncommitted transactions, and shuts down cleanly.
ImmediateSIGQUITpg_ctl stop -D /pgdata -m immediateStops immediately without proper shutdown. Recovery from WAL files happens during next startup.

Difference Between Reload and Restart

  • When we change server configuration parameters, PostgreSQL needs to read those changes before they become active.
  • Reload applies the new configuration without stopping or restarting the database service. Existing connections and database activity continue normally.
  • Some configuration parameter changes cannot be applied through reload and will only take effect after a restart.
  • Restart stops the PostgreSQL server gracefully, ends all running activities, releases resources, closes open files, and then starts the server again with the new configuration.

 

Smart Shutdown (SIGTERM):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m smart
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:26:59.741 +08 [3476] LOG:  received smart shutdown request
2026-07-03 14:26:59.752 +08 [3476] LOG:  background worker "logical replication launcher" (PID 3483) exited with exit code 1
2026-07-03 14:26:59.753 +08 [3478] LOG:  shutting down
2026-07-03 14:26:59.755 +08 [3478] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:26:59.765 +08 [3478] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/105465F0, redo lsn=1/105465F0
2026-07-03 14:26:59.771 +08 [3476] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:27:18.394 +08 [3487] LOG:  redirecting log output to logging collector process
2026-07-03 14:27:18.394 +08 [3487] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:27:18.394 +08 [3487] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:27:18.399 +08 [3487] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:27:18.404 +08 [3487] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:27:18.413 +08 [3491] LOG:  database system was shut down at 2026-07-03 14:26:59 +08
2026-07-03 14:27:18.425 +08 [3487] LOG:  database system is ready to accept connections

Fast Shutdown (SIGINT – Default):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m fast
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:28:26.839 +08 [3487] LOG:  received fast shutdown request
2026-07-03 14:28:26.844 +08 [3487] LOG:  aborting any active transactions
2026-07-03 14:28:26.854 +08 [3487] LOG:  background worker "logical replication launcher" (PID 3494) exited with exit code 1
2026-07-03 14:28:26.860 +08 [3489] LOG:  shutting down
2026-07-03 14:28:26.862 +08 [3489] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:28:26.874 +08 [3489] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.005 s, total=0.015 s; sync files=2, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB; lsn=1/105466A0, redo lsn=1/105466A0
2026-07-03 14:28:26.882 +08 [3487] LOG:  database system is shut down

[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:29:00.574 +08 [3505] LOG:  redirecting log output to logging collector process
2026-07-03 14:29:00.574 +08 [3505] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:29:00.575 +08 [3505] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:29:00.579 +08 [3505] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:29:00.580 +08 [3505] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:29:00.582 +08 [3505] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:29:00.587 +08 [3505] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:29:00.595 +08 [3509] LOG:  database system was shut down at 2026-07-03 14:28:26 +08
2026-07-03 14:29:00.607 +08 [3505] LOG:  database system is ready to accept connections

Immediate Shutdown (SIGQUIT):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m immediate
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:29:47.471 +08 [3505] LOG:  received immediate shutdown request
2026-07-03 14:29:47.485 +08 [3505] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:30:17.965 +08 [3520] LOG:  redirecting log output to logging collector process
2026-07-03 14:30:17.965 +08 [3520] HINT:  Future log output will appear in directory "log".
. done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:30:17.966 +08 [3520] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:30:17.970 +08 [3520] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:30:17.974 +08 [3520] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:30:17.983 +08 [3524] LOG:  database system was interrupted; last known up at 2026-07-03 14:29:00 +08
2026-07-03 14:30:19.211 +08 [3524] LOG:  database system was not properly shut down; automatic recovery in progress
2026-07-03 14:30:19.216 +08 [3524] LOG:  redo starts at 1/10546718
2026-07-03 14:30:19.216 +08 [3524] LOG:  invalid record length at 1/10546750: expected at least 24, got 0
2026-07-03 14:30:19.217 +08 [3524] LOG:  redo done at 1/10546718 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-07-03 14:30:19.221 +08 [3522] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-07-03 14:30:19.231 +08 [3522] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.002 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/10546750, redo lsn=1/10546750
2026-07-03 14:30:19.239 +08 [3520] LOG:  database system is ready to accept connections

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

WAL Files

PostgreSQL, , , , , , , , , , , , ,

Understanding WAL Files in PostgreSQL – For Oracle DBAs

What is WAL?

WAL (Write-Ahead Logging) ensures data durability and crash recovery. Every change is first written to WAL before being flushed to data files. 

1. WAL Location: WAL files are stored in $PGDATA/pg_wal/.
2. Fixed Size: Each WAL segment is pre-allocated with a fixed size (typically 16MB), set at cluster initialization (initdb).
3. Writing & Switching: WAL segments are written sequentially. PostgreSQL switches to the next segment when the current one is full or a manual switch is triggered (e.g., via pg_switch_wal()).
4. WAL and Archive File Size: When archived, the exact same 16MB WAL segment file is copied as raw binary to the archive destination. Therefore, archive files always match the WAL segment size.
5. Archiving: If archive_mode = on, PostgreSQL uses the archive_command to copy completed WAL segments to the archive destination (e.g., /pgArch/...). Only full segments are archived.
6. Recycling: If archiving is disabled, PostgreSQL reuses old WAL segments once they’re no longer needed for recovery or replication.

WAL Files = Redo Log Files
WAL Archives = Archive Log Files

PostgreSQL vs Oracle Comparison

AspectOraclePostgreSQL
Redo Log FilesCalled Redo Log FilesCalled WAL Files
Archive Log FilesCalled Archive Log FilesCalled WAL Archives
Redo Log File Size~200MB (default)16MB (default and fixed)
Redo Log Files CountMinimum 2 or more redo log groups
(Best practice: 3)		No fixed number
Controlled by: min_wal_size / wal_segment_size
Example: 80MB / 16MB = 5 WAL files
Archive Log File Size~200MB (same as redo log file size)16MB (same as WAL file size)
Log File StorageRedo log files stored in redo log groupsWAL files stored in pg_wal directory
Archiving MechanismArchive logs created if archiving is enabledArchived via archive_command if archive_mode = on
Overwrite BehaviorRedo logs are overwritten when full (if archive not enabled)WAL segments are recycled/overwritten when full (if archive_mode is off)
Force Switch BehaviorALTER SYSTEM SWITCH LOGFILE;SELECT pg_switch_wal();

PostgreSQL WAL Configuration Parameters

ParameterMeaningDefault
wal_segment_sizeSize of each WAL file16MB
min_wal_sizeMinimum total WAL size retained80MB (5 WAL files)
Common in production: 8GB
max_wal_sizeMax WAL before triggering a checkpoint1GB
Common in production: 16GB

Check Configuration

[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW wal_segment_size;"
wal_segment_size
------------------
16MB  <----- Each redo log file size
(1 row)

[postgres@lxicbpgdsgv01 ~]$


[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW min_wal_size;"
 min_wal_size
--------------
 80MB <---- 80MB/16MB = 5 <--- Total 5 redo log files with each 16MB
(1 row)

[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ psql -c "SHOW max_wal_size;"
 max_wal_size
--------------
 1GB  <---- 1024MB
(1 row)

[postgres@lxicbpgdsgv01 ~]$

View WAL files (Redo log files)

postgres=# SELECT current_setting('data_directory') || '/pg_wal' AS wal_directory;
        wal_directory
-----------------------------
 /pgData/pgsql17/data/pg_wal  <---- Redo log file directory
(1 row)

postgres=# 
[postgres@lxicbpgdsgv01 ~]$ ls -lh /pgData/pgsql17/data/pg_wal | grep -E '^-.* [0-9]+ .* [0-9A-F]{24}$'
-rw-------. 1 postgres postgres 16M Oct 8 01:33 000000010000000000000005
-rw-------. 1 postgres postgres 16M Oct 8 01:28 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct 8 01:28 000000010000000000000007
[postgres@lxicbpgdsgv01 ~]$

min_wal_size = 80MB means PostgreSQL will try to keep at least 5 WAL segment files on disk (since 80MB ÷ 16MB = 5 files).

However, in our case, only 3 WAL files are currently present. This is likely because the system is under low load in the testing environment, so fewer WAL files are needed at the moment.

---- Lets Generate Load

postgres=# -- Create test table
DROP TABLE IF EXISTS wal_test;
CREATE TABLE wal_test (
    id serial PRIMARY KEY,
    data text
);

-- Generate WAL traffic
DO $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..50 LOOP
        -- INSERT: 10,000 rows
        INSERT INTO wal_test (data)
        SELECT repeat('wal_test_data_', 50)
        FROM generate_series(1, 10000);

        -- UPDATE: 5,000 rows using CTE with LIMIT
        WITH to_update AS (
            SELECT id FROM wal_test WHERE id % 2 = 0 LIMIT 5000
        )
        UPDATE wal_test
        SET data = data || '_updated'
        WHERE id IN (SELECT id FROM to_update);

        -- DELETE: 5,000 rows using CTE with LIMIT
        WITH to_delete AS (
            SELECT id FROM wal_test WHERE id % 3 = 0 LIMIT 5000
        )
        DELETE FROM wal_test
        WHERE id IN (SELECT id FROM to_delete);

        -- Commit to flush WAL
        COMMIT;

        -- Optional pause to slow down the loop
        PERFORM pg_sleep(0.5);
    END LOOP;
END$$;
DROP TABLE
CREATE TABLE
DO
postgres=# exit
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ 
ls -lrth /pgData/pgsql17/data/pg_wal
total 752M
drwx------. 2 postgres postgres    6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres  16M Oct  8 16:36 000000010000000000000035
drwx------. 2 postgres postgres 4.0K Oct  8 16:36 archive_status
-rw-------. 1 postgres postgres  16M Oct  8 16:36 xlogtemp.7289
[postgres@lxicbpgdsgv01 ~]$ 

-- Now We can see Many WAL Files (Redo log files) generated, count increased from 3 WAL files to 50+ WAL files

View Archived WAL Files (Archive log files)

postgres=# SHOW archive_command;
        archive_command
-------------------------------
 cp %p /pgArch/pgsql17/arch/%f
(1 row)

postgres=#

[postgres@lxicbpgdsgv01 ~]$ 
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgArch/pgsql17/arch/
total 976M
-rw-------. 1 postgres postgres 16M Oct  7 23:50 000000010000000000000001
-rw-------. 1 postgres postgres 16M Oct  7 23:51 000000010000000000000002
-rw-------. 1 postgres postgres 16M Oct  8 01:28 000000010000000000000003
-rw-------. 1 postgres postgres 16M Oct  8 01:28 000000010000000000000004
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000005
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:24 000000010000000000000007
-rw-------. 1 postgres postgres 16M Oct  8 16:25 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres 16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres 16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres 16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres 16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000035
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000036
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000037
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000038
-rw-------. 1 postgres postgres 16M Oct  8 16:36 000000010000000000000039
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003A
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003B
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003C
-rw-------. 1 postgres postgres 16M Oct  8 16:36 00000001000000000000003D
[postgres@lxicbpgdsgv01 ~]$

-- WAL and Archive File Size: When archived, the exact same 16MB WAL segment file is copied as raw binary to the archive destination. Therefore, archive files always match the WAL segment size (redo log size).

Best Practices

  • Use a dedicated mount point for pg_wal (WAL directory) – eg. /pgWal
  • Set min_wal_size to higher value in production (e.g. 8GB)
  • Keep WAL archive files on a separate mount point for better I/O and space isolation – eg. /pgArch
  • Monitor WAL archive directory regularly to avoid disk full errors

Note:

  • WAL file size is fixed at cluster init (default: 16MB). You cannot change it for an existing cluster.
  • PostgreSQL WAL Size Be Can Increased only at NEW cluster creation time via initdb (64MB)
    /usr/pgsql-17/bin/initdb –wal-segsize=64 -D /pgData/pgsql17/data

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/