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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

------------------ Original ------------------
From: "Andreas";<maps.on@gmx.net>;
Date: Fri, Oct 25, 2013 09:53 AM
To: "pgsql-general"<pgsql-general@postgresql.org>;

Subject: [GENERAL] 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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general