Re: backup database by cloning itself

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 12:41, Tom Lane wrote:
> Scott Marlowe <smarlowe@g2switchworks.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.

Maybe his real goal "all the backups readily available to be read by
my program (opening the backup read only)" is to have a historical
record of what certain records looked like in the past.

There are other ways of doing that, though.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF01oIS9HxQb37XmcRAqYQAKDoSNb76asUadv9InNXroshleKZEQCgl6w6
SwWu3841RN4B+GBBkxoa/DQ=
=bdEY
-----END PGP SIGNATURE-----