Scaling the Wall of Text: Best Practices for Logging in PostgreSQL

Slide 1

Slide 1 text

Slide 2

Slide 2 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 2 ● Software Developer/Support Engineer @ EDB since 2015 ● Using Postgres to back Golang and Python apps ● Perl web developer before getting thrust into a DBA role ● Been working with PostgreSQL since 2002 About Me

Slide 3

Slide 3 text

Slide 4

Slide 4 text

Slide 5

Slide 5 text

© EnterpriseDB Corporation 2024 - All Rights Reserved Bad Stuff Happens • Who caused it? • What happened? • When did it happen? • Why did it happen? • How did it happen? • How can we prevent it from happening again? 5

Slide 6

Slide 6 text

Slide 7

Slide 7 text

Slide 8

Slide 8 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 8 ● Query performance is poor ● The database crashed ● Replication is broken ● Some tool or extension is not working ● Database is failing to upgrade ● and more... Situations Encountered in Support

Slide 9

Slide 9 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 9 ● Query timing statistics ● Data corruption ● Query errors ● Maintenance activity ● Performance bottlenecks ● Access attempts ● Cause of a crash ● And more! What the log can tell you

Slide 10

Slide 10 text

Slide 11

Slide 11 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 11 ● log_destination (stderr, syslog, csvlog, eventlog) ● logging_collector (on/off) ● log_directory # use a custom partition ● log_filename # postgresql-%Y-%m-%d_%H%M%S.log ● log_file_mode (0600) ● log_rotation_age (1 day) ● log_rotation_size (10 MB) ● log_truncate_on_rotation (on/off) Where to Log

Slide 12

Slide 12 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 12 ● log_min_messages ● log_min_error_statement ● log_min_duration_statement ● Logs queries that are considered "slow" in your application ● log_min_duration_sample ● log_statement_sample_rate ● log_transaction_sample_rate What to Log

Slide 13

Slide 13 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 13 ● log_min_messages ● log_min_error_statement ● DEBUG{5,4,3,2,1} ● INFO ● NOTICE ● WARNING ● ERROR ● LOG ● FATAL ● PANIC When to Log 2024-05-23 09:51:51.779 PDT [223363] someuser@my_database FATAL: role "someuser" does not exist 2024-05-23 09:54:50.804 PDT [223395] postgres@postgres WARNING: there is no transaction in progress

Slide 14

Slide 14 text

Slide 15

Slide 15 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 15 ● log_min_duration_statement ● logs the statement and duration AFTER the query has finished ● does not log parameters ● log_statement ● logs the statement BEFORE the query is executed ● logs parameters log_min_duration_statement v. log_statement

Slide 16

Slide 16 text

Slide 17

Slide 17 text

Slide 18

Slide 18 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 18 ● As of v.10, defaults to %m [%p] (timestamp + backend PID) ● Before v.10, default is blank ● ~40% of users keep default ● Don't neglect this parameter! log_line_prefix

Slide 19

Slide 19 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 19 2024-02-29 00:11:15.337 UTC [658] LOG: statement: drop table pgbench_accounts; 2024-02-29 00:37:42.796 UTC [5193]: [2] [txid=0] user=bad_user,db=my_database,app[unknown],client=172.17.0.3(44698) LOG: connection authorized: user=bad_user database=my_database 2024-02-29 00:37:42.797 UTC [5193]: [3] [txid=0] user=bad_user,db=my_database,apppsql,client=172.17.0.3(44698) LOG: statement: drop table pgbench_accounts log_line_prefix

Slide 20

Slide 20 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 20 ● Logging Levels ● Filter by FATAL/WARN/ERROR/ etc. ● Hints ● Often related to Autovacuum performance issues ● Patterns ● Unix tools (grep/sed/awk/etc) ● Application Bugs ● Broken Replication ● Replication Slots ● wal_keep_segments ● Autovacuum ● "dead but not yet removable" ● Temp Files ● work_mem ● Lock Waits & Deadlocks ● pg_stat_activity What to Look For

Slide 21

Slide 21 text

© EnterpriseDB Corporation 2024 - All Rights Reserved 21 ● Be sure to set your log_line_prefix ● '%m [%p]: [%l] [txid=%x] user=%u,db=%d,app%a,client=%r ' ● Log as much as you can, and use a separate partition if you have to ● Monitor for spikes in FATAL/ERROR/WARNING messages ● Pay attention to patterns in your logs ● Tools like pgbadger and auto_explain can be helpful Summary