Starting with PostgreSQL 15, the server log can be written as JSON instead of plain text. The format is configured through the usual settings mechanism, either by editing postgresql.conf or by using ALTER SYSTEM to write into postgresql.auto.conf.

Switching the log format

A small set of parameters is all that separates you from JSON output:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

#------------------------------------------------------------------------------

# REPORTING AND LOGGING

#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'jsonlog' # Valid values are combinations of

# stderr, csvlog, jsonlog, syslog, and

# eventlog, depending on platform.

# csvlog and jsonlog require

# logging_collector to be on.

# This is used when logging to stderr:

logging_collector = on # Enable capturing of stderr, jsonlog,

# and csvlog into log files. Required

# to be on for csvlogs and jsonlogs.

# (change requires restart)

# These are only used if logging_collector is on:

log_directory = 'log' # directory where log files are written,

# can be absolute or relative to PGDATA

log_filename = 'postgresql-%a.log' # log file name pattern,

# can include strftime() escapes

The decisive change is log_destination, which must be set to jsonlog; its default, stderr, keeps the traditional text log. Writing to a file requires the logging collector, so logging_collector is enabled in most setups. In the example, log_filename is set to postgresql-%a.log, which produces one log file per weekday for rotation purposes.

A restart applies the settings. Strictly speaking, jsonlog alone does not need one — turning on the logging collector does. Since the collector is normally enabled when the server is deployed, later activation is the exception rather than the rule, and the restart rarely costs extra downtime.

What ends up on disk

After the restart the server writes two files:

1

2

3

4

[hs@hansmacbook log]$ ls -l

total 16

-rw------- 1 hs staff 2039 Nov 4 08:50 postgresql-Fri.json

-rw------- 1 hs staff 169 Nov 4 08:50 postgresql-Fri.log

Investigating the pair explains the split:

1

2

3

[hs@hansmacbook log]$ cat postgresql-Fri.log

2022-11-04 08:50:59.000 CET [32183] LOG: ending log output to stderr

2022-11-04 08:50:59.000 CET [32183] HINT: Future log output will go to log destination 'jsonlog'.

The .log file exists before the JSON machinery comes into play and contains no more than two lines. Everything else goes to the .json file:

1

2

3

4

5

6

7

8

9

10

11

[hs@hansmacbook log]$ head postgresql-Fri.json

{'timestamp':'2022-11-04 08:50:59.000

CET','pid':32183,'session_id':'6364c462.7db7','line_num':1,'session_start':'2022-11-04 08:50:58

CET','txid':0,'error_severity':'LOG','message':'ending log output to stderr','hint':'Future log

output will go to log destination 'jsonlog'.','backend_type':'postmaster','query_id':0}

{'timestamp':'2022-11-04 08:50:59.000

CET','pid':32183,'session_id':'6364c462.7db7','line_num':2,'session_start':'2022-11-04 08:50:58

CET','txid':0,'error_severity':'LOG','message':'starting PostgreSQL 15.0 on x86_64-apple-

darwin21.6.0, compiled by Apple clang version 13.1.6 (clang-1316.0.21.2.5),

64-bit','backend_type':'postmaster','query_id':0}

...

Reading the output with jq

A densely packed file of millions of JSON documents is not meant to be read by eye. A tool such as jq makes the stream readable and easier to process:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

[hs@hansmacbook log]$ tail -f postgresql-Fri.json | jq

{

'timestamp': '2022-11-04 08:50:59.000 CET',

'pid': 32183,

'session_id': '6364c462.7db7',

'line_num': 1,

'session_start': '2022-11-04 08:50:58 CET',

'txid': 0,

'error_severity': 'LOG',

'message': 'ending log output to stderr',

'hint': 'Future log output will go to log destination 'jsonlog'.',

'backend_type': 'postmaster',

'query_id': 0

}

{

'timestamp': '2022-11-04 08:50:59.000 CET',

'pid': 32183,

'session_id': '6364c462.7db7',

'line_num': 2,

'session_start': '2022-11-04 08:50:58 CET',

'txid': 0,

'error_severity': 'LOG',

'message': 'starting PostgreSQL 15.0 on x86_64-apple-darwin21.6.0, compiled by Apple

clang version 13.1.6 (clang-1316.0.21.2.5), 64-bit',

'backend_type': 'postmaster',

'query_id': 0

}

{

'timestamp': '2022-11-04 08:50:59.006 CET',

'pid': 32183,

'session_id': '6364c462.7db7',

'line_num': 3,

'session_start': '2022-11-04 08:50:58 CET',

'txid': 0,

'error_severity': 'LOG',

'message': 'listening on IPv6 address '::1', port 5432',

'backend_type': 'postmaster',

'query_id': 0

}

...

Fields are not fixed

A JSON line does not always carry the same field set. System messages expose different information than query-related entries, which keeps the output efficient. Compare a checkpoint entry with a message that also carries the database, the user and much more:

1

2

3

test=# SELECT 1 / 0;

ERROR: division by zero

test=# q

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

[hs@hansmacbook log]$ tail -n2 postgresql-Fri.json | jq

{

'timestamp': '2022-11-04 08:55:59.017 CET',

'pid': 32185,

'session_id': '6364c463.7db9',

'line_num': 2,

'session_start': '2022-11-04 08:50:59 CET',

'txid': 0,

'error_severity': 'LOG',

'message': 'checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed,

0 recycled; write=0.002 s, sync=0.001 s, total=0.005 s; sync files=2, longest=0.001 s,

average=0.001 s; distance=0 kB, estimate=0 kB',

'backend_type': 'checkpointer',

'query_id': 0

}

{

'timestamp': '2022-11-04 09:05:53.346 CET',

'user': 'hs',

'dbname': 'test',

'pid': 32266,

'remote_host': '[local]',

'session_id': '6364c7dd.7e0a',

'line_num': 1,

'ps': 'SELECT',

'session_start': '2022-11-04 09:05:49 CET',

'vxid': '3/60',

'txid': 0,

'error_severity': 'ERROR',

'state_code': '22012',

'message': 'division by zero',

'statement': 'SELECT 1 / 0;',

'application_name': 'psql',

'backend_type': 'client backend',

'query_id': 0

}

Consumers of these documents have to account for that variability.

Verbosity has a price

JSON output is considerably more verbose than the standard text log. Deployments that only record system events and errors will hardly notice. Logging every single query is a different story: millions of lines consume both performance and disk space.