Skip to main content

Troubleshoot PostgreSQL

This document describes how to resolve issues related to PostgreSQL service.

Expose user password in PostgreSQL log​

When using commands such as CREATE or ALTER, sensitive information such as user passwords may be exposed in plain text in PostgreSQL logs.

Password exposure example
# Use CREATE command.
postgres=> CREATE ROLE test_kc_mapo WITH PASSWORD ‘test0701’;

# Check log and password is exposed as-is.
2025-07-01 12:00:00 UTC [12345] LOG: statement: CREATE ROLE test_kc_mapo WITH PASSWORD 'test0701'

▶️ Resolution: To prevent security risks, it is recommended to use \password, which operates on client side, instead of SQL statements for password-related operations.

  1. Use \password command for user password operations.

    \password usage example
    # 1. Create 'test_kc_mapo' account. Do not grant login privilege to check ALTER log.
    postgres=> CREATE ROLE test_kc_mapo NOLOGIN;CREATE ROLE

    # 2. Change password using `\password` and grant login privilege.
    postgres=> \password test_kc_mapo
    Enter new password:
    Enter it again:
    postgres=> ALTER ROLE test_kc_mapo LOGIN;
    ALTER ROLE
    postgres=>

  2. Check logs for changes.

    Log check example
    # Encryption method may vary by PostgreSQL, but password is generally not exposed in plain text.
    2025-07-01 12:00:29 UTC [12345] LOG:  statement: CREATE ROLE test_kc_mapo NOLOGIN;
    2025-07-01 12:00:48 UTC [12345] LOG:  statement: ALTER USER test_kc_mapo PASSWORD '*******'
    2025-07-01 12:01:06 UTC [12345] LOG:  statement: CREATE ROLE test_kc_mapo LOGIN;

Increase storage usage in PostgreSQL instance group​

When storage usage increases rapidly, main causes and resolutions are as follows.

Failover replication not properly reconfigured​

▶️ Resolution: Check availability state (replication state) after failover

If replication is not properly reconfigured after failover, WAL files may not be cleaned up and continue to accumulate depending on pg_wal-related parameter settings.
Even if failover itself completes successfully, if replication connection is not properly established, WAL files are not deleted, which can lead to rapid increase in storage usage. This may eventually cause instance group or instance to reach Storage-full state, resulting in service disruption.
Therefore, after failover, it is recommended to check replication configuration by verifying resource state along with Availability state.

Abnormal increase in WAL (pg_wal) usage​

▶️ Resolution: Check parameters related to WAL generation and retention policy

In addition to replication connections, WAL may accumulate in PostgreSQL for various reasons. Therefore, it is necessary to check whether usage of WAL (pg_wal) directory is increasing abnormally, along with schema (Table/Index) usage.
If WAL usage increases rapidly, instance group and instance may reach Storage-full state, leading to service disruption. To prevent this, it is recommended to periodically check resource state and review parameters and configuration values related to WAL generation and retention policy (e.g., wal_level, wal_keep_size, max_wal_size, checkpoint_timeout).

Normal usage increase​

▶️ Resolution: Check usage and expand volume

When storage usage increases, instance state changes according to following criteria.

  • Storage-Warning: Log or Data disk usage is 85% or higher, or available capacity is 30GB or less
  • Storage-Error: Log or Data disk usage is 95% or higher, or available capacity is 10GB or less for more than 5 minutes

If instance transitions to Storage-Error state, some features may be restricted to ensure service stability. Therefore, in general cases, it is recommended to check storage usage and expand storage capacity through Expand volume.