Andrew Perrin wrote:
>- Do a formal backup more often
>
>
>
For what it's worth, the FreeBSD "port"
(http://cvsweb.freebsd.org/ports/databases/postgresql7/) for PostgreSQL
contains a most excellent daily maintenance script (as written it's a
FreeBSD "periodic" script, but it's more or less suitable to be a cron
job). It does a pg_dump of all the databases (with blobs, as a -Fc) and
vacuums all the tables.
The one thing it doesn't do is a pg_dumpall -g (to save the users and
groups), but I told the author this and he promises to include it in the
next version.
On a less topical note, there was much rejoicing concerning the ability
(in 7.2) to use ident authentication on unix domain socket connections.
I have a machine with untrusted (unix) users on it. This made backing up
painful, because a password was needed. But now, pg_hba.conf simply has
local all ident admin
as the last line, and pg_ident.conf has
admin pgsql pgsql
Thus, unix user pgsql can connect to any database as db user pgsql
without a password. Before I upgraded, I had to use
local all password pg_suser
with a pg_suser file with pgsql and an encrypted password, then set
PGPASSWORD in my backup scripts (not the most secure thing to do).