Re: Schemas, databases, and backups

>
> # 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.)
>

sure you can just like with pg_database, it's pg_namespace for schemas
and pg_tables for tables...

> 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?)
>

"where not datistemplate and datallowconn" it's better than "datname
not in (values)"

> 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"
>

maybe it's a good idea to put all that in a script... it's getting
bigger and bigger (and uglier and uglier)

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

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)