Frequently Asked Question

1. PostgreSQL Server Log

• Location: Configured in postgresql.conf under the log_directory setting (e.g., /var/lib/pgsql/data/log/ or /var/log/postgresql/).
• Configuration:
  • Ensure logging is enabled:

    logging_collector = on
    log_directory = '/path/to/log'
    log_filename = 'postgresql-%Y-%m-%d.log'
    log_min_messages = warning
    

  • Restart PostgreSQL after updating the config.

2. Connection Logs

• Location: Same as server logs, based on log_directory.
• Configuration:

  log_connections = on
  log_disconnections = on
  log_line_prefix = '%t [%p]: [%l-1] user=%u db=%d app=%a host=%h '
  

3. Query Logs

• Location: Same as server logs.
• Configuration:

  log_statement = 'all'  # Options: none, ddl, mod, all
  log_duration = on
  log_min_duration_statement = 1000  # Log queries taking more than 1 second
  

4. Error Logs

• Location: Same as server logs.

5. PostgreSQL Configuration Files

• Files:
  • postgresql.conf: Main configuration file.
  • pg_hba.conf: Authentication and access control.
  • pg_ident.conf: User mapping configuration.
• Location: Typically in $PGDATA (e.g., /var/lib/pgsql/data/).
• Purpose: Used to analyze server settings, authentication methods, and access controls.

6. WAL (Write-Ahead Log) Files

• Only neccesary uppon OneClickDBA request
• Location: Configured in postgresql.conf under wal_log_hints and stored in the pg_wal directory.
• Use: May be requested to investigate data corruption or replication issues.

7. System Statistics and Locks

• Commands:
  • Active sessions:

    SELECT * FROM pg_stat_activity;
    

  • Current locks:

    SELECT * FROM pg_locks;
    

  • Long-running queries:

    SELECT pid, now() - pg_stat_activity.query_start AS duration, query
    FROM pg_stat_activity
    WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 minutes';
    

8. Replication Logs (if applicable)

• Only neccesary uppon OneClickDBA request
• Location: Same as server logs.
• Configuration:

  wal_level = replica
  log_replication_commands = on
  

9. pgAudit Logs

• Only neccesary uppon OneClickDBA request
• Configuration:
  

• shared_preload_libraries = 'pgaudit'
  pgaudit.log = 'all'
  

10. Backups and Recovery Logs

• Tools: pg_dump, pg_basebackup, or custom backup scripts.

11. System Logs

• Location:
  • /var/log/messages (Linux)
  • /var/log/syslog (Linux)
  • Event Viewer Logs (Windows)

12. Crash Dumps

• Only neccesary uppon OneClickDBA request
  
• Location: Core dump files may be created if PostgreSQL crashes.
• Configuration:
  • Enable core dumps:

    ulimit -c unlimited
    

  • Location depends on system settings (/var/core/ or /tmp/).

13. Performance Diagnostics

• Only neccesary uppon OneClickDBA request
  
• Tools:
  • pg_stat_statements (track execution statistics):

    CREATE EXTENSION pg_stat_statements;
    SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
    

  • EXPLAIN and EXPLAIN ANALYZE:

    EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;
    

14. Application Logs

If troubleshooting involves an application, logs showing how it interacts with the database (e.g., ORM logs) may be requested.

15. pgBadger (Optional)

• Only neccesary uppon OneClickDBA request
• Usage:

  pgbadger /path/to/postgresql.log -o report.html