Hello,

my database is not very big so I want to adopt this backup strategy:

each 1 hour I want to clone the whole database 'mydatabase' to another
database 'currenttime_mydatabase' in order to have 24 backup a day,
overwriting the yesterday backups by today-same-time backups.

This is good for me because I have all the backups readily available
to be read by my program.

I'm writing a script run by cron each hour to do accomplish the backup
task.

My target is to have the backup operation not affecting the users, so
I want to be able to copy a database even if the database is used by
someone.

Can I use
CREATE DATABASE my_backup_database TEMPLATE current_database?

Is there a better way to get what I need?

Thanks,
Filippo

On Feb 13, 8:42 am, "filippo" <filippo2... (AT) virgilio (DOT) it> wrote:
DBMS: unknow
OS: unknown

ANSWER: use the red one.






How are we supposed to help without enough information? Or did I just
start browsing comp.databases.mindreaders ??

On Feb 13, 8:42 am, "filippo" <filippo2... (AT) virgilio (DOT) it> wrote:
As Ed has stated, you do not mention which DBMS you are using so an
answer is nearly impossible.

However, in most cases copying a file that is actively being updated
by concurrent users is similar to playing:
http://en.wikipedia.org/wiki/Russian_roulette

"greg.fenton" <greg.fenton (AT) gmail (DOT) com> wrote:

The original message subject was tagged "[postgresql]". I
believe that is a DBMS.

Why do people not read the subject line? (I have seen this
before.)

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

filippo wrote:

You cannot use a database as a template while someone is connected to
it. From
http://www.postgresql.org/docs/8.2/i...lease-8-1.html

Make initdb create a new standard database called postgres, and convert
utilities to use postgres rather than template1 for standard lookups
(Dave)

In prior releases, template1 was used both as a default connection for
utilities like createuser, and as a template for new databases. This
caused CREATE DATABASE to sometimes fail, because a new database cannot
be created if anyone else is in the template database. With this
change, the default connection database is now postgres, meaning it is
much less likely someone will be using template1 during CREATE DATABASE

You could try:

createdb b
pg_dump a | psql b
which will copy database a into database b

If you want hourly backups you should also consider PITR.
http://www.postgresql.org/docs/8.2/i...archiving.html


--
John Koller

On 13 Feb, 18:28, "Ed Prochak" <edproc... (AT) gmail (DOT) com> wrote:
actually my subject was referring to postgresql, but it seems flown
away :-). Anyway I'm using postgresql 8.2 on winXP/Linux machines.

I could use pg_dump/pg_restore. pg_dump doesn't have to have exclusive
access to database to perform the operation. My only problem is that
pg_dump create a backup on a file, the best to me whould be to have a
perfect clone (users/ data etc) of original database ready to be used
just after the cloning. Is it possible?

Thanks,

Filippo

On Feb 13, 4:32 pm, Gene Wirchenko <g... (AT) ocis (DOT) net> wrote:
Apparently GOOGLE does not display tags in the subject.
Since you said this I checked the original and that is what's there:
from original> Subject: [postgresql] backup database by cloning itself
incase they hide it in the body too, that
was> Subject: LEFTSQUAREBRACKETpostgresqlRIGHTSQUAREBRAKET backup
database by cloning itself

Why GOOGLE does this I do not know. I will try to be more careful. But
is it really too much to ask to put it plainly in the body of the
post?

And if it was a postgresql, why post here? there are multiple
postgresql groups to get specific answers.

Note: the questions are not targetted at you, Gene. They are more for
the general comp.databases audience.

Ed

On Feb 14, 2:05 am, "filippo" <filippo2... (AT) virgilio (DOT) it> wrote:
check in one of the postgresql groups for more specific answers.
ed

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote:



No, the mauve one, it's got more RAM!


Jeez, I thought everybody knew that!



Paul...



--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 10.2.0.1 (Express Edition)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

On Feb 15, 12:15 pm, Paul <p... (AT) see (DOT) my.sig.com> wrote:
Ed