Re: Selective backup script

Mike Blackwell, 21.11.2011 17:50:
> I've seen a couple backup scripts that query the metadata to
> determine the list of databases to back up. I like this approach,
> but have a few databases which don't get backed up for various
> reasons, e.g. testing databases which we'd prefer to recreate on the
> off chance we loose them, rather than have using up time/disk for
> backup. Might there be a way to tag those databases somehow so the
> backup script knows to skip them? I'd rather not hard code the list
> in the script.
>
> Thoughts?

What about using the comments on the database to control this?

For those database that you need to backup, run something like:

comment on database postgres is 'do_backup';

Then in the shell script that retrieves the databases to backup, you could do something like this:

select db.datname
from pg_database db
join pg_shdescription dc on dc.objoid = db.oid
where dc.description = 'do_backup';

Or if you have more database to backup than not, then maybe flipping the logic is also an option:

comment on database notimportant is 'no_backup';

select db.datname, dc.description
from pg_database db
left join pg_shdescription dc on dc.objoid = db.oid
where dc.description is distinct from 'no_backup';

Hope this helps.

Thomas