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

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.

On 25 Říjen 2013, 3:53, 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.

Hi,

I assume that's something that needs to be done by your script, there's
certainly nothing in PostgreSQL itself to do that.

You may for example run a daily cron script that lists all databases on
the test server, parses the database name and drops those older than 5
days.

Tomas

On Fri, Oct 25, 2013 at 10:53 AM, Andreas <maps.on@gmx.net> 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.
A simple script kicked by a cron job would do the work for you easily...
--
Michael

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

--
Regards
 Shridhar

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

Dear,

  Append following command to crontab:

  D=`date -d -5day +"%Y%m%d"`;echo "DROP DATABASE test_db_$D" | psql

  Maybe you should change the "psql" to  your psql path.

Le vendredi 25 octobre 2013 à 04:50 +0200, Andreas a écrit :
> >
> 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.
>

I assume db_test is created from a dump file? if that's the case, and if
your system allows it,  using logrotate on the dump is very
straithforward; e.g. to rotate an archive everyday, keeping a weekly
archive over 52 two weeks, simply create the
file /etc/logrotate.d/myapp :


#Create rotation for myapp's backups
/var/backups/myapp/myapp.gz {
    weekly
    missingok
    rotate 52
    notifempty
}



--
                                        Salutations, Vincent Veyron

http://marica.fr/site/demonstration
Gestion des contentieux juridiques, des contrats et des sinistres d'assurance