Postgres Alerts
It is cool to be able to stare at colorful graphs to see what Postgres is doing, but sometimes you just want to setup something, walk away, and be informed when there is problem. That is what check_postgres and tail_n_mail are designed to do.
check_postgres is a script designed to be run from cron or a monitoring tool like Nagios. It reports on areas in the database that need administrator attention.tail_n_mail has a similar purpose, but monitors the Postgres log files for important messages.
Few people stare at their mobile phones waiting for something to happen — they configure their phones to notify them when something important happens. Why not do that for your Postgres clusters? That's what check_postgres and tail_n_mail are meant to do.
Dynamic Logging Control
Postgres has many logging options, e.g. log_statement, log_min_duration_statement, and log_line_prefix. These values can be set at various levels, which I have already covered. One level that has particular flexibility is postgresql.conf. Settings in this file affect all running sessions, unless the values are overridden at lower levels.
When debugging, it is often hard to know which log settings to enable. One approach is to enable all settings beforehand, but that can generate lots of log traffic and be hard to interpret. Ideally you can enable just the settings you need at the time you need them — this is where postgresql.conf comes in. With postgresql.conf oralter system, you can change any logging settings you want and then signal a reload by either sending a sighup signal to the server, running "pg_ctl reload", or executing "SELECT pg_reload_conf()". One nice thing is that after a reload, all running sessions receive the new settings when their transactions complete. This allows logging settings to be enabled and disabled as needed.
Use All Your Constraints
Many database administrators use databases as simple data stores. However, relational systems can do much more, with advanced querying, analysis, and transaction control capabilities. Another area that is often overlooked is constraints. Constraints allow new and updated data to be checked against defined constraints and prevent changes if the constraints would be violated.
Constraints are odd in that they don't do anything if the data is consistent — it is more like an insurance policy against invalid data being entered into the database. If constraints are missing, there often are no initial problems, but over time erroneous or unexpected data gets in, causing problems with applications and reporting.
Do yourself a favor the next time you create a table — take the insurance and create useful check, not null, default, unique, primary key, and foreign key constraints. If your tables are already created, you can use alter table to add constraints to existing tables.
Casting to Data Types
There are four ways to cast a value to another data type:
SELECT int4 '123' + 1;
?column?
----------
124
SELECT CAST('123' AS int4);
int4
------
123
SELECT '123'::int4 + 1;
?column?
----------
124
SELECT int4('123') + 1;
?column?
----------
124
The first two are sql standard, the third one is a Postgres-ism that is often convenient, and the final one relies on the existence of named functions to do the conversion. Some of the more complex data type specifications have shortcuts, e.g. timestamp with time zone can use ::timestamptz.
Bruce Momjian is a Senior Database Architect at EnterpriseDB.
These short posts, organized here as a collection, originally appeared individually on Bruce's personal blog.
