7

I'm using ArchLinux and PostgreSQL 9.4.4 and have enabled the logging in the config file:

$ sudo egrep -v "^[[:blank:]]*($|#|//|/\*| \*|\*/)" /var/lib/postgres/data/postgresql.conf

max_connections = 1024          # (change requires restart)
shared_buffers = 128MB          # min 128kB
dynamic_shared_memory_type = posix  # the default is the first option
logging_collector = on      # Enable capturing of stderr and csvlog
log_directory = '/tmp'      # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_file_mode = 0644            # creation mode for log files,
log_error_verbosity = verbose       # terse, default, or verbose messages
log_statement = 'all'           # none, ddl, mod, all
log_timezone = 'Asia/Jakarta'
datestyle = 'iso, dmy'
timezone = 'Asia/Jakarta'
lc_messages = 'en_CA.UTF-8'         # locale for system error message
lc_monetary = 'en_CA.UTF-8'         # locale for monetary formatting
lc_numeric = 'en_CA.UTF-8'          # locale for number formatting
lc_time = 'en_CA.UTF-8'             # locale for time formatting
default_text_search_config = 'pg_catalog.english'

then restart or reload the service:

sudo systemctl restart postgresql
sudo systemctl reload postgresql

the loaded settings:

postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE '%log%';
            name             |            setting             
-----------------------------+--------------------------------
 log_autovacuum_min_duration | -1
 log_checkpoints             | off
 log_connections             | off
 log_destination             | stderr
 log_directory               | /tmp
 log_disconnections          | off
 log_duration                | off
 log_error_verbosity         | verbose
 log_executor_stats          | off
 log_file_mode               | 0644
 log_filename                | postgresql-%Y-%m-%d_%H%M%S.log
 log_hostname                | off
 log_line_prefix             | 
 log_lock_waits              | off
 log_min_duration_statement  | -1
 log_min_error_statement     | error
 log_min_messages            | warning
 log_parser_stats            | off
 log_planner_stats           | off
 log_rotation_age            | 1440
 log_rotation_size           | 10240
 log_statement               | all
 log_statement_stats         | off
 log_temp_files              | -1
 log_timezone                | Asia/Jakarta
 log_truncate_on_rotation    | off
 logging_collector           | on
 syslog_facility             | local0
 syslog_ident                | postgres
 wal_log_hints               | off
(30 rows)

the /tmp permission:

$ ll /tmp/ 
total 11656
drwxrwxrwt 22 root root       580 Jun 29 10:05 ./
drwxr-xr-x 17 root root      4096 Jun 10 08:04 ../
...

the /tmp usage:

$ df | grep /tmp
tmpfs            8159996    31204   8128792   1% /tmp

doing some query on psql, but the /tmp/postgresql-* still doesn't show up. What can I do to find which part that prevent log from being created?

EDIT 1

the result of journalctl -u postgresql

Oct 08 09:13:53 xxx postgres[19861]: LOG:  00000: redirecting log output to logging collector process
Oct 08 09:13:53 xxx postgres[19861]: HINT:  Future log output will appear in directory "/tmp".
Oct 08 09:13:53 xxx postgres[19861]: LOCATION:  SysLogger_Start, syslogger.c:645
Oct 08 09:13:55 xxx systemd[1]: Started PostgreSQL database server.
  • Why would you log to /tmp anyway? I don't see why it wouldn't work with your configuration as shown, though. – Craig Ringer Jun 29 '15 at 3:43
  • just for development/debugging, so it would be cleared every reboot – Kokizzu Jun 29 '15 at 5:52
  • If you check your PostgreSQL related processes, do you see a postgres: logger process? – dezso Jun 29 '15 at 10:05
  • @dezso yes postgres: logger process exists on ps ax output – Kokizzu Jun 29 '15 at 11:04
  • Just to be sure, can you verify the loaded settings: SELECT name, settings FROM pg_settings WHERE name LIKE 'log%'? – MatheusOl Jul 1 '15 at 12:27
1

The logging collector tries to capture stderr and write it to a file. But it looks like systemd wins the redirecting war, and sends the output to the journal. You can check the systemd journal with:

journalctl -u postgresql

The systemd configuration is in usr/lib/systemd/system/postgresql.service. I've tried various settings for StandardError, but none seems to allow the regular Postgres logging to do its work.

  • With the exact same permissions, the postgres user can write to /tmp/ without a problem on our hosts. – dezso Jun 29 '15 at 10:14
  • @dezso: That is unlikely... did you test with sudo -u postgres echo hi > /tmp/test.txt? That runs echo as postgres but the redirect as root. The correct syntax is sudo -u postgres bash -c 'echo hi > /tmp/test.txt' and it should show a "permission denied" error. – Andomar Jun 29 '15 at 10:25
  • No, I am logged in as postgres, no need to sudo. Aren't the DB processes running basically the same way? How does opening a new shell come into this picture? (Note that I am on Ubuntu, but that should not make a big difference, AFAIK.) – dezso Jun 29 '15 at 10:34
  • 3
    When permissions on /tmp are drwxrwxrwt as shown, that implies that every user can write into it (rightmost w corresponding to others), which is the normal situation. There's nothing wrong here. – Daniel Vérité Jun 29 '15 at 10:58
  • @Andomar $ sudo -u postgres echo hi > /tmp/test.txt works fine cat /tmp/test.txt gives hi | here's the journalctl output: http://pastie.org/10264195 – Kokizzu Jun 29 '15 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.