Re: backup database by cloning itself

Scott Marlowe <smarlowe(at)g2switchworks(dot)com> writes:
> On Tue, 2007-02-13 at 07:54, filippo wrote:
>> my database is not very big so I want to adopt this backup strategy:
>> I want to clone my database every 1 hour to another
>> database 'currenttime_mydatabase' in order to have 24 backup a day,
>> overwriting the yesterday backups by today-same-time backups.
>> Can I use
>> CREATE DATABASE my_backup_database TEMPLATE current_database?

> Create database ain't gonna work, cause it needs a database with no
> users connected.

There's a more serious objection, which is that storing a duplicate
database under the same postmaster doesn't give you an independent copy.
If something bad happens to pg_clog or pg_global, *all* your backups may
be rendered useless.

Now if your purpose in making the backups is only to protect against
user errors, and not any sort of hardware failure or Postgres bug,
maybe this isn't an issue. But it's not what I'd call a backup.

regards, tom lane