Re: Schemas, databases, and backups

Jaime Casanova wrote:

> psql -d template1 -U postgres -c "select datname from pg_databases
> where datname not in ('template1', 'template0', 'postgres');" | while
> read D;
>
> or something like that in a shell script and the simply pg_dump $D...
>

I found the following snippet of code, which roughly matches yours. It
was over in the Redhat mailing lists and was used to vacuum databases.

# su postgres -c 'psql -t -c "select datname from pg_database order by
datname;" template1' | xargs -n 1 echo

template0
template1
test1
test2

After some mucking about, I came up with the following single-line shell
command (suitable for adding to root's crontab).

# su postgres -c 'psql -t -c "select datname from pg_database where not
datistemplate and datallowconn order by datname;" template1' | xargs -n
1 -i pg_dump -Ft -b -U postgres -f /backup/postgresql/pgsql.`date
+%Y%m%d.%H%M`.{}.tar {}

I couldn't figure out how to add in the "not in ('template1',
'postgres', 'template1')" into the single-line shell command. It seemed
to confuse the shell.

Issues with the above command:

1) The date gets reevaluated for each new execution of pg_dump. Which
is not necessarily ideal if you want filenames that group easily.
Converting to a shell script would allow finer control.

2) The output is not compressed. I guess I could switch to using "-Fc"
in conjunction with "-Z 9".

Additional questions and notes:

A) pg_dump takes an argument "--schema=schema", which looks like it
allows me to just dump the contents of a particular schema within a
database. So if I wanted, I could iterate through the list of schemas
and go that route.

B) There's also a "--table=table" argument, which dumps a single table.
The man page for pg_dump warns me that pg_dump will not output any
objects that the table depends on, so it may not be possible to restore.
(The same warning applied to --schema=schema.)

C) I'm not sure whether I can get away with using "where not
datistemplate and datallowconn". For backing up user databases, does it
matter? I can't figure out how to quote the commands properly to keep
bash from getting confused. (Doubled-up quotes? Escaped quotes?)

D) After more mucking, I figured out how to set a static datestamp value
for the entire command and compress the tar files using gzip. I'm not
sure whether I should use "export" or "set" (both worked).

# export DTSTAMP=`date +%Y%m%d.%H%M` ; su postgres -c 'psql -t -c
"select datname from pg_database where not datistemplate and
datallowconn order by datname;" template1' | xargs -n 1 -i bash -c
"pg_dump -Ft -b -U postgres {} | gzip -c >
/backup/postgresql/pgsql.${DTSTAMP}.{}.tgz"

Links:
http://archives.postgresql.org/pgsql-general/2000-01/msg00593.php
http://postgis.refractions.net/pipermail/postgis-users/2005-November/009925.html