Thread: Selective backup script
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?
__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
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
On Nov 21, 2011, at 11:50, Mike Blackwell wrote: > Might there be a way to > tag those databases somehow so the backup script knows to skip them? Add a table to each database that can be queried by the back up script to store this additional metadata? Michael Glaesemann grzm seespotcode net
That sounds close, though the comments are already being used for general descriptions. I suppose it wouldn't hurt to add 'no_backup' to the existing comments where appropriate. I was hoping maybe I'd missed a 'user-defined database level metadata' field somewhere. ^_^
Thanks,
__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > 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. Give them a unique connection limit, higher than max_connections, e.g. ALTER DATABASE testdb1 CONNECTION LIMIT 9218; Not as intuitive as a comment, but a lot more visible that something is different about the database. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201111211715 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAk7K0CwACgkQvJuQZxSWSshN5wCgsejmvDEzo3yBz3MBRy9OGYKI BYMAoPLDjEXOJOLh5HSWtCLHhhfNu1Zn =AP/0 -----END PGP SIGNATURE-----