Re: Need help how to manage a couple of daily DB copies.

Am 25.10.2013 04:15, schrieb Shridhar Daithankar:
> On Friday, October 25, 2013 03:53:14 AM Andreas wrote:
>> Hi,
>>
>> I'd like to set up a DB-Server that keeps copies of our productive db
>> for an external db-assistant.
>> He should prepare chores on the test-server and mail the sql scripts to me.
>> I'll look over those scripts and run them against the productive db myself.
>>
>> So I'd like to have a daily cron job dump the main db, rename the
>> test-db to something with a date in it.
>> Like test_db --> test_db_20131024
>> Create a new test_db and import the dump of the main db.
>>
>> So far no problem but how could I limit the number of test_dbs to 5?
>> I'd like to keep those test_dbs 5 days and then drop them.
> #delete files older than 5 days
> $ find . -mtime +4 -exec rm '{}' \;
>
> Put that in cron too :)
>
well, not quite

We are not talking about files but databases within the db server.

Lets keep 3 copies total

the idea is to start with the database db_test today (2013/10/24)
2013/10/25: rename db_test to db_test_13025 and import the latest
dump into a new db_test
2013/10/26: rename db_test to db_test_13026 ... import
2013/10/27: rename db_test to db_test_13027 ... import
2013/10/28: rename db_test to db_test_13028 ... import
Now we've got db_test and 4 older copies.
Find the oldest copy and drop it. --> drop db_test_131025

or better every day drop every copy but the 3 newest.

and so on

this needs to be done by an external cron script or probaply by a
function within the postgres database or any other administrative database.

The point is to give the assistant a test-db where he could mess things up.
In the event he works longer than a day on a task his work shouldn't be
droped completely when the test-db gets automatically replaced.

Regards
Andreasd