Providing password for "psql" in shell scripts

IMHO, PostgreSQL > MySQL, but it has its own quirks.

For example, running SQL statements using psql is a bit complicated if you do not want to be prompted for a password. Research on the topic is made a bit difficult by the fact that the answers are provided in the "libpq" section of the PostgreSQL manual, rather than the manpage for psql.

The upside is that I think it works with createdb, createuser and other commands (did not test that though).

There are a few recommended ways of doing this.

• One is to set "trust" in "pg_hba.conf" for your type of connection, which usually is good in a situation where you can trust the machines and users that are connecting to the database.
• Another is to store passwords in ~/.pgpass, as described here http://www.postgresql.org/docs/current/static/libpq-pgpass.html

However it can also be done using environmental variable in a shell script:

PGPASSWORD="your_password" psql -U postgres -c "select 1;"

Such approach is carries a security risk of someone seeing the password, but can be quite useful in a situation where you need to run a list of commands and do not care about security.

Information on PGPASSWORD and other environmental variables is found here: http://www.postgresql.org/docs/current/static/libpq-envars.html

Enable logging in PostgreSQL 8.4

To get rotated file logging working on PostgreSQL 8.4 edit "postgresql.conf" on your system. On Gentoo the file is located "/var/lib/postgresql/x.x/" where x.x is your PostgreSQL major and minor version (i.e 8.3, 8.4). Mine is currently 8.4.

Before we start though, you might want to make sure that the log files are written to a directory that can be read by other users, but still writable by the user that run PostgreSQL server. That would make tailing them much easier.

So on the Gentoo system I suggest you use /var/log and create a nested folder to reflect the PostgreSQL version. Also to make sure that is owned by both "postgres" user and "postgres" group.

cd  /var/log/
sudo mkdir -p postgresql/8.4
sudo chown -R postgres:postgres postgresql/8.4

Now modify the /var/lib/postgresql/8.4/postgresql.conf

log_destination = 'stderr'
logging_collector = on
log_directory = '/var/lib/postgresql/8.4/'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = off
log_rotation_age = 1d
log_rotation_size = 0
log_statement = 'mod'

Verbosity depends on the "log_statement" setting as mentioned here:
http://www.postgresql.org/docs/8.4/static/runtime-config-logging.html

Here, logs are:

• written to "/var/log/postgresql/8.4" directory
• log files are time stamped
• log rotation is daily which is achieved by setting "log_rotation_age" to 1 day and log_rotation_size is disabled by setting it to 0

IMPORTANT NOTE:

The confusing part is that "logging_collector" used to be called redirect_stderr in versions before 8.3. (http://www.postgresql.org/docs/8.3/static/release-8-3.html)