X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F3601D1B906 for ; Sat, 6 Mar 2004 12:43:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 41015-08 for ; Sat, 6 Mar 2004 08:43:56 -0400 (AST) Received: from vsmtp1.tin.it (vsmtp1alice.tin.it [212.216.176.141]) by svr1.postgresql.org (Postfix) with ESMTP id 23E00D1B530 for ; Sat, 6 Mar 2004 08:43:49 -0400 (AST) Received: from host88-9.pool80182.interbusiness.it (80.182.9.88) by vsmtp1.tin.it (7.0.019) (authenticated as silvanadimartino) id 4034BCBF00312974; Sat, 6 Mar 2004 13:43:49 +0100 From: Silvana Di Martino To: Chris Ruprecht , pgsql-admin@postgresql.org Subject: Re: How to do fast, reliable backups? Date: Sat, 6 Mar 2004 13:39:28 +0000 User-Agent: KMail/1.5 References: <200403051637.42457.chris@ruprecht.org> In-Reply-To: <200403051637.42457.chris@ruprecht.org> MIME-Version: 1.0 Content-Disposition: inline Message-Id: <200403061329.17799.silvanadimartino@tin.it> Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/90 X-Sequence-Number: 12659 Alle 21:37, venerd=EC 5 marzo 2004, Chris Ruprecht ha scritto: > I am wondering how you guys back up your databases. Say, I have a 20 GB > database, data and indexes. If I run pg_dump on this, it backs up the > schema and the data. When I have to restore this, I whould have to run th= is > through psql which would then re-build the indexes after it has inserted > the records. That's not really a feasable option as it takes too long. When you have large databases and you need consistent backups, an interesti= ng=20 solution is master-slave replication. Just use a second server of your LAN = as=20 a slave replica of your main database server. Should you have any problem= =20 with the main server, just switch the IP addresses of the master and slave= =20 servers and you are up and running again. The switching takes a few=20 milliseconds and can even be performed automatically by a few specific=20 programs (look for "fault-tolerant linux systems" with google). While your= =20 backup server serves your users, you have all the time to install a new har= d=20 disk into the main server, restore your data (from the running slave server= =20 or from a tape) and come back to service. When you need to put your data into a tape or a CD-ROM, just stop the=20 master-slave mechanism, make a "static" backup (a pg_dump or a file system= =20 copy) of your _slave_ server and restart the replication program. The=20 master-slave replication mechanism will take care to re-align the two serve= rs=20 in a few minutes. This way, you never need to stop the main server. > If I back up the pgdata directory, I will get inconsistent data, as > somebody might update data in 2 tables while my backup is running, one > change in a table I have already backed up, one in a table I have not. The > solution would be to shut the database server down, do the backup and then > start everything back up. But that's not really an option in a 24/7 > environment. Making a copy of your data directory while your server is running is=20 definitely a bad idea. Forget it. Backing up the file system used by a RDBM= S=20 usually requires to put the server off-line and flush all the unwritten dat= a=20 to the disk. This cannot be done in a 24/7 environment. > What I'd like to see is a transaction aware backup which backs up indexes > as well as data. Have a look at one of the master-slave replication systems available on the= =20 net. For this task you do not need anything as sophisticated as a=20 multi-master replication system (Like the Bettina Kemme "PG-Replicator"=20 project). You could even be able to write down your own client program with= =20 Perl o Python. The program just have to connect to your running master=20 server, read the relevant data and insert them into the database on the=20= =20 slave server. Using a SERIAL primary key it is quite easy to spot which dat= a=20 of a table have not been copied to the slave server yet. Using a timer, you= =20 can define the time granularity of the replication mechanism that best fit= =20 your need. Using transactions you can be sure to get just consistent data.= =20 Reading and saving system tables, you can backup even the most hidden and= =20 subtle things of your DB. Beware that restoring your data is up to you, if you use a custom-made syst= em.=20 Test the restore system before using your program in a production=20 environment. Good luck. ----------------------------------------- Alessandro Bottoni and Silvana Di Martino alessandrobottoni@interfree.it silvanadimartino@tin.it