How to Reduce Excessive PostgreSQL Log File Growth in Production Environments

PostgreSQL logging is essential for troubleshooting, performance monitoring, auditing, and operational support. However, incorrectly configured logging can quickly consume large amounts of disk space and create unnecessary operational risk.

In a recent production scenario, a PostgreSQL 15 environment was generating more than 20 GB of logs per day, with approximately 150 GB of disk space consumed in less than a week.

The database was also configured with PostgreSQL streaming replication, which meant any configuration change on the primary needed to be assessed carefully to avoid affecting availability or replication.

This article explains how to identify the root cause of excessive PostgreSQL logging, safely reduce log volume, improve rotation and retention, and understand the impact of these changes in a streaming replication environment.

The Problem: PostgreSQL Logs Growing by More Than 20 GB Per Day

The PostgreSQL log directory contained files similar to:

postgresql-Wed.log    ~26 GB
postgresql-Tue.log    ~26 GB
postgresql-Thu.log    ~17 GB
postgresql-Sun.log    ~23 GB
postgresql-Sat.log    ~24 GB
postgresql-Mon.log    ~24 GB
postgresql-Fri.log    ~24 GB

The total log footprint exceeded 150 GB.

This level of log generation is not typically expected unless the database is processing an extremely large workload or PostgreSQL has been configured to record excessive diagnostic information.

The first step was therefore to review the active PostgreSQL logging configuration.

Step 1: Review PostgreSQL Logging Settings

The following query can be used to check the most important logging parameters:

SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
    'logging_collector',
    'log_destination',
    'log_directory',
    'log_filename',
    'log_rotation_age',
    'log_rotation_size',
    'log_truncate_on_rotation',
    'log_min_messages',
    'log_min_error_statement',
    'log_min_duration_statement',
    'log_statement',
    'log_duration',
    'log_connections',
    'log_disconnections',
    'log_lock_waits',
    'log_temp_files',
    'log_autovacuum_min_duration'
)
ORDER BY name;

In this environment, the relevant configuration was:

log_autovacuum_min_duration = 600000
log_connections             = off
log_destination             = stderr
log_directory               = log
log_disconnections          = off
log_duration                = off
log_filename                = postgresql-%a.log
logging_collector           = on
log_lock_waits              = off
log_min_duration_statement  = 1000
log_min_error_statement     = error
log_min_messages            = warning
log_rotation_age            = 1440
log_rotation_size           = 0
log_statement               = all
log_temp_files              = -1
log_truncate_on_rotation    = on

The key problem was immediately visible:

log_statement = all

Why log_statement = all Can Generate Massive Logs

The PostgreSQL parameter:

log_statement = all

causes PostgreSQL to log every SQL statement executed by the database.

That includes:

  • SELECT statements

  • INSERT statements

  • UPDATE statements

  • DELETE statements

  • DDL commands

  • application-generated SQL

  • background workload activity

On a busy application database, thousands or millions of SQL statements may be executed every hour.

Even if each log entry is relatively small, the cumulative volume can become enormous.

For high-transaction systems, this configuration can easily produce tens of gigabytes of logs per day.

Step 2: Identify Where the Configuration Is Defined

Before changing the setting, it is useful to determine exactly where PostgreSQL is reading it from.

Run:

SELECT
    name,
    setting,
    source,
    sourcefile,
    sourceline
FROM pg_settings
WHERE name IN (
    'log_statement',
    'log_min_duration_statement',
    'log_filename',
    'log_rotation_age',
    'log_rotation_size'
);

An example result may show:

log_filename               postgresql-%a.log
log_min_duration_statement 1000
log_rotation_age           1440
log_rotation_size          0
log_statement              all

with the settings sourced from:

/data/pgsql/15/data/postgresql.conf

This confirms that the change should be made directly in the PostgreSQL configuration file.

Step 3: Disable Full SQL Statement Logging

The most important change is:

log_statement = none

Instead of:

log_statement = all

This prevents PostgreSQL from recording every SQL statement.

The existing slow-query logging can remain enabled:

log_min_duration_statement = 1000

This means PostgreSQL continues logging queries taking one second or longer.

The resulting behaviour becomes:

Fast SQL statements        Not logged
SQL >= 1 second            Logged
Warnings                    Logged
Errors                      Logged
Fatal errors                Logged
Every SQL statement         Not logged

This provides a far better balance between troubleshooting capability and log volume.

Recommended Production Logging Configuration

A production PostgreSQL configuration may look similar to:

logging_collector = on
log_destination = 'stderr'

log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_rotation_age = 1440
log_rotation_size = 512000

log_min_messages = warning
log_min_error_statement = error

log_statement = none
log_duration = off
log_min_duration_statement = 1000

log_connections = off
log_disconnections = off

log_lock_waits = on
log_temp_files = -1

log_autovacuum_min_duration = 600000

These settings help provide useful diagnostic information without creating unnecessary log volume.

Why Log Rotation Size Matters

The original environment used:

log_rotation_size = 0

This disables size-based log rotation.

As a result, PostgreSQL could continue writing into the same log file for the entire day regardless of file size.

For a high-volume database, this can result in individual log files of 20 GB, 30 GB, or more.

A safer setting could be:

log_rotation_size = 512000

which represents approximately 500 MB.

This allows PostgreSQL to create a new log file once the current file reaches the configured size.

Improve the Log Filename Format

The original configuration used:

log_filename = 'postgresql-%a.log'

This creates seven weekday-based files:

postgresql-Mon.log
postgresql-Tue.log
postgresql-Wed.log
postgresql-Thu.log
postgresql-Fri.log
postgresql-Sat.log
postgresql-Sun.log

This is a valid PostgreSQL configuration and can be useful for simple seven-day cyclic retention.

However, timestamp-based filenames provide better control in enterprise environments:

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

For example:

postgresql-2026-09-04_000000.log
postgresql-2026-09-04_082334.log
postgresql-2026-09-04_154501.log

This makes it easier to:

  • archive logs

  • compress completed log files

  • forward them to Splunk or another SIEM platform

  • troubleshoot incidents by timestamp

  • automate retention

  • avoid overwriting historical files

PostgreSQL Does Not Automatically Provide Full Retention Management

PostgreSQL supports log rotation, but administrators should also implement a retention strategy.

For example:

0–1 days     Active local logs
1–7 days     Compressed local logs
>7 days      Deleted locally
Long-term    Retained in Splunk or SIEM

Completed logs can be compressed using:

find /data/pgsql/15/data/log \
-type f \
-name 'postgresql-*.log' \
-mtime +1 \
-exec gzip {} \;

Compressed logs older than seven days could then be removed:

find /data/pgsql/15/data/log \
-type f \
-name 'postgresql-*.log.gz' \
-mtime +7 \
-delete

Retention should always align with organisational, regulatory, audit, and security requirements.

If logs are forwarded to Splunk, Microsoft Sentinel, Elastic, or another central logging platform, local retention may be significantly shorter.

How to Recover Disk Space Safely

When PostgreSQL log files are consuming large amounts of storage, administrators should avoid deleting the current active log file directly.

First identify the active PostgreSQL log:

SELECT pg_current_logfile();

Then force PostgreSQL to rotate the log:

SELECT pg_rotate_logfile();

Confirm the new active file:

SELECT pg_current_logfile();

Inactive log files can then be compressed or archived.

For example:

gzip postgresql-Mon.log
gzip postgresql-Tue.log
gzip postgresql-Wed.log

Always avoid compressing or deleting the file PostgreSQL is actively writing to.

Why You Should Not Simply Delete an Active PostgreSQL Log

On Linux, deleting an open file does not necessarily release the disk space immediately.

PostgreSQL may continue writing through an open file descriptor even though the filename no longer appears in the directory.

You may therefore observe:

df -h

showing that the disk is still full even after deleting the log.

Deleted-but-open files can be checked using:

lsof +L1 | grep postgres

The safer approach is always:

  1. rotate the PostgreSQL log

  2. confirm the new active file

  3. archive or compress inactive logs

  4. delete only when appropriate

What About Streaming Replication?

One of the main concerns in this scenario was that the PostgreSQL primary server was participating in streaming replication.

The good news is that changing logging parameters such as:

log_statement
log_min_duration_statement
log_filename
log_rotation_age
log_rotation_size
log_lock_waits

does not normally interfere with PostgreSQL streaming replication.

These parameters control local logging behaviour. They do not change the WAL stream sent to the standby.

They also do not modify replication parameters such as:

wal_level
max_wal_senders
max_replication_slots
wal_keep_size
primary_conninfo
primary_slot_name
hot_standby

Changing logging configuration is therefore generally low risk from a replication perspective.

Important: Configuration Changes Are Local to Each Server

A configuration file change made on the primary is not automatically replicated to the standby.

For example, changing:

log_statement = none

on the primary does not update the standby's postgresql.conf.

If consistent logging is required across both servers, the standby configuration should be reviewed and changed separately.

This is an important distinction between:

  • database changes that are replicated through WAL

  • operating system and PostgreSQL configuration changes that are local to each host

Apply the Change Without Restarting PostgreSQL

Many logging parameters can be reloaded without restarting the PostgreSQL instance.

After changing:

log_statement = none

run:

SELECT pg_reload_conf();

Then verify:

SHOW log_statement;

The expected result is:

none

A reload avoids unnecessary service interruption and is preferable to a restart when the parameter supports dynamic reload.

Always Verify Replication After a Production Change

Even when a configuration change should not affect replication, production changes should always be validated.

Run:

SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn
FROM pg_stat_replication;

The standby should remain:

state = streaming

Replication lag can also be reviewed:

SELECT
    application_name,
    state,
    sync_state,
    pg_size_pretty(
        pg_wal_lsn_diff(sent_lsn, replay_lsn)
    ) AS replay_lag_bytes
FROM pg_stat_replication;

This provides confirmation that the standby remains healthy following the configuration change.

Check for Other Sources of Excessive Logging

If log growth remains high after disabling log_statement = all, further investigation is required.

Potential causes include:

  • repeated application errors

  • application retry loops

  • authentication failures

  • slow queries

  • connection storms

  • PostgreSQL auditing

  • pgAudit

  • auto_explain

  • lock waits

  • long-running transactions

  • excessive temporary file usage

Check installed PostgreSQL extensions:

SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

Also review:

SHOW shared_preload_libraries;

If pgaudit is enabled, review its logging configuration separately.

Consider pg_stat_statements for SQL Performance Monitoring

If the reason for enabling:

log_statement = all

was to understand SQL performance, a better option may be pg_stat_statements.

pg_stat_statements collects aggregated execution statistics for SQL queries and allows administrators to identify:

  • frequently executed statements

  • high total execution time queries

  • slow SQL

  • resource-heavy statements

  • inefficient workloads

This usually provides far more useful performance information than writing every SQL statement into text logs.

For example:

SELECT
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

This can identify the highest-impact SQL without generating tens of gigabytes of log data.

Operational Risk: Audit and Compliance Requirements

Before disabling full SQL logging, organisations should determine why it was originally enabled.

If log_statement = all is being used for:

  • regulatory auditing

  • security investigations

  • privileged user monitoring

  • application audit requirements

  • compliance reporting

then disabling it may create an audit gap.

In such environments, consider dedicated database auditing tools rather than using PostgreSQL's general-purpose statement log as a full audit repository.

The logging strategy should balance:

  • performance

  • disk consumption

  • security

  • auditability

  • operational troubleshooting

  • regulatory requirements

Recommended Change Process for Production

For a PostgreSQL production server with streaming replication, a controlled rollout could be:

1. Back up postgresql.conf
2. Confirm the current replication status
3. Change only log_statement = none
4. Reload PostgreSQL configuration
5. Verify the setting
6. Confirm standby remains streaming
7. Monitor log growth
8. Review slow-query logging
9. Implement improved rotation
10. Implement compression and retention
11. Verify Splunk or SIEM ingestion

Before editing the configuration:

cp /data/pgsql/15/data/postgresql.conf \
/data/pgsql/15/data/postgresql.conf.$(date +%Y%m%d_%H%M%S).bak

Then update:

log_statement = none

Reload:

SELECT pg_reload_conf();

Verify:

SHOW log_statement;

Key Takeaways

Excessive PostgreSQL log growth is often caused by overly verbose logging rather than a database fault.

The most significant setting to review is:

log_statement = all

For most production environments, a better configuration is:

log_statement = none
log_min_duration_statement = 1000

This preserves slow-query visibility while preventing every SQL statement from being written to disk.

Administrators should also consider:

  • size-based log rotation

  • timestamp-based filenames

  • log compression

  • defined retention policies

  • centralised logging platforms

  • pg_stat_statements

  • audit requirements

  • replication validation after changes

A well-designed PostgreSQL logging strategy provides enough information to troubleshoot incidents without allowing diagnostic data to become an operational problem itself.

Need Help Managing PostgreSQL?

PostgreSQL performance, availability, monitoring, backup, replication, and logging configuration can become increasingly complex as database environments grow.

Onsys Technologies provides database consulting and managed database services for organisations that need specialist support across PostgreSQL, SQL Server, Oracle, MySQL, MariaDB, Azure database platforms, and other enterprise database technologies.

Our database services include:

  • PostgreSQL health checks
  • performance tuning
  • streaming replication and high availability
  • backup and point-in-time recovery
  • database monitoring
  • capacity and storage management
  • database migration and upgrades
  • incident troubleshooting
  • database security reviews
  • managed DBA services
  • on-call DBA support

If your PostgreSQL environment is experiencing excessive log growth, replication issues, performance problems, backup failures, or storage pressure, Onsys can help assess the environment and implement a sustainable database management strategy.