Andrew Perrin wrote:
- Do a formal backup more oftenFor 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).
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
