Archive for April, 2013

12 April, 2013

Followup to some questions about the Postgres activity log

by gorthx

Answers to three questions that have come up from my various logging & monitoring talks over the years:

1. I recommend putting your activity logs on their own partition, just like you would with the transaction logs and your data directory. Rob W asked “What happens when the logger runs out of space?” The answer: nothing drastic. Postgres won’t write any more log entries, but the database will stay up. Make sure you have appropriate monitoring in place to alert you if that partition fills up.

2. “Can you set multiple log_destinations?” Well, I’ve logged to csv + stderr and csv + eventlog, just to see if it could be done. I don’t know what would happen if you tried csv + syslog.

3. “Can you set the logging GUCs on a per-database level?” Yes, some of them can be set to different values per-database or per-user, even. This can be quite useful to answer, for example, the “I don’t think your application is sending the SQL statements you think it’s sending” type of questions:

— for a specific database:
ALTER DATABASE tedsdb SET log_statement = 'all';
— for a specific user:
ALTER ROLE bill SET log_statement = 'all';

To undo this change:
ALTER ROLE bill SET log_statement = default;
or
ALTER ROLE bill RESET log_statement;

To check the settings for the database, join pg_db_role_setting, pg_database, and pg_authid:
postgres=# SELECT d.datname, COALESCE(r.rolname, 'ALL') AS rolname, s.setconfig
FROM pg_database d
JOIN pg_db_role_setting s
ON s.setdatabase = d.oid
LEFT JOIN pg_authid r
ON s.setrole = r.oid;
datname | rolname | setconfig
---------+---------+---------------------
tedsdb | ALL | {log_statement=all}

To check the settings for a particular user, look in pg_roles:
postgres=# SELECT rolname, rolconfig
FROM pg_roles
ORDER BY rolname;
rolname | rolconfig
----------+---------------------
bill | {log_statement=all}
postgres |

Advertisements
5 April, 2013

Installing PostgreSQL (and friends) from source on Ubuntu 12.04 (Precise Pangolin)

by gorthx

This is an update to this post from a few years back. This time I’m installing Postgres 9.2.3, PostGIS 2.0.1, and pgAdmin 1.17.0 on Ubuntu 12.04.

read more »