Probably mirroring the database and then making a backup of the mirror would allow you to never stop the master server but just the slave. I don't know if it is possible to have a slave running on a different port on the same server is possible or not. If not, you will need and second computer. If it is possible, you will just consume a lot of disk space for the replication and (I think 25% of the database size) extra space for the sql dump. Ragnar Kjørstad a écrit : > On Thu, Jun 13, 2002 at 05:33:31PM -0500, Brandon D. Valentine wrote: > > >The easiest way to snapshot the filesystem is to use a logical volume > > >manager (LVM or EVMS on linux) and then do: > > >1. take database offline > > >2. take snapshot > > >3. take database online > > >4. backup from snapshot > > >5. remove snapshot > > > > I would like to comment that while this is a possible way to backup your > > database, it's not the way I would recommend going about it. There are > > a couple of caveats: > > > > 1) In order to take a filesystem snapshot you must have enough diskspace > > elsewhere to contain the filesystem snapshot. If your database resides > > on a large filesystem with other data[0] then you're unlikely to want to > > deal with caching an entire snapshot until amanda backs it up. > > You need extra space with both approaches (pg_dump and snapshot). Which > solution requires the most space will depend on many factors, e.g. how > much you write you your database. If it's mostly read-only, the snapshot > will not require much space. > > > 2) Backing up a database by grabbing the actual files off of the disk > > can introduce problems if trying to restore onto, for instance, an > > upgraded version of Postgres, which might have changed the ondisk > > representation slightly. There are also problems if you migrate to a > > different architecture since things like byte ordering can change. By > > generating a database dump with pg_dump you insure that you have a > > portable, plain text file full of valid query commands which can be read > > into any future version of Postgres and possibly even into other RDMBS > > products provided you choose a pg_dump format which is standards > > complaint enough. > > Yes, this is a backup-solution, not a migration-solution. > > pg_dumps can not always be imported into newer postgresql versions > without modifications. > > > 3) If your snapshot code is not atomic it means you must take your > > database server down everytime you make the snapshot, which on a large > > filesystem could be a non-trivial amount of time. With pg_dump you're > > just dumping the tables via the standard Postgres interface so you've > > got no issues with doing it on a running database. > > Hmm, is the pg_dump consistent? > IOW, is it done in a single transaction? (even for multiple databases?) > If yes, would a very long-running pg_dump not cause problems for the > running server? I know postgresql doesn't lock whole tables, but it > means that if data changes postgresql needs to keep two branches, and it > will require extra diskspace and I suppose also introduce overhead to > other processes? > > Of course, if the database is mostly read-only this is just a minor > problem, but that's true for snapshot-backups as well. > > -- > Ragnar Kjørstad > Big Storage -- -----BEGIN CERTIFICATE----- MIIDlzCCA0GgAwIBAgICAR4wDQYJKoZIhvcNAQEEBQAwgZ4xCzAJBgNVBAYTAlRO MQ4wDAYDVQQIEwVUdW5pczEOMAwGA1UEChMFU05DRlQxHjAcBgNVBAsTFUluZHVz dHJpZSBGZXJyb3ZpYWlyZTEkMCIGA1UEAxMbU0ZBIENlcnRpZmljYXRpb24gQXV0 aG9yaXR5MSkwJwYJKoZIhvcNAQkBFhpjZXJ0LWF1dGhAc2ZhLnNuY2Z0LmNvbS50 bjAeFw0wMDEyMTYwODI2MjNaFw0wMTEyMTYwODI2MjNaMIGVMQswCQYDVQQGEwJU TjEOMAwGA1UECBMFVHVuaXMxDjAMBgNVBAoTBVNOQ0ZUMR4wHAYDVQQLExVJbmR1 c3RyaWUgRmVycm92aWFpcmUxFzAVBgNVBAMTDkZhdGhpIEJlbiBOYXNyMS0wKwYJ KoZIhvcNAQkBFh5mYXRoaS5iZW5uYXNyQHNmYS5zbmNmdC5jb20udG4wgZ8wDQYJ KoZIhvcNAQEBBQADgY0AMIGJAoGBALT4Bpeomgr6Qw0CO6xIS3yvkSetbMwZgr88 YOamkZzOrSslQzIBLaZCCxgzGzLcJ/X4TZnE+55DWtQxUn6DxUpjJZ8icN02Ywmn PQwnpCsb3C7jAJEDx95lrHNOf7EN0bYnoQVGr6L3H35ZiaFBpUt0P2wTBrsCvdmq S8siUBkhAgMBAAGjggEqMIIBJjAJBgNVHRMEAjAAMCwGCWCGSAGG+EIBDQQfFh1P cGVuU1NMIEdlbmVyYXRlZCBDZXJ0aWZpY2F0ZTAdBgNVHQ4EFgQUqzN0eirv0jFy +glnuTF9g/Y+WgEwgcsGA1UdIwSBwzCBwIAUFuVoX6xutnL7VMRIu8z6qmMnAayh gaSkgaEwgZ4xCzAJBgNVBAYTAlROMQ4wDAYDVQQIEwVUdW5pczEOMAwGA1UEChMF U05DRlQxHjAcBgNVBAsTFUluZHVzdHJpZSBGZXJyb3ZpYWlyZTEkMCIGA1UEAxMb U0ZBIENlcnRpZmljYXRpb24gQXV0aG9yaXR5MSkwJwYJKoZIhvcNAQkBFhpjZXJ0 LWF1dGhAc2ZhLnNuY2Z0LmNvbS50boIBADANBgkqhkiG9w0BAQQFAANBADFM0w88 Eiw0mBcX+cV8GrRnNH80vY97xez+syuLgalkAn3B7YZ462dH6m03+p67ZG+hT5vn QXMFOkpM8/nIgm4= -----END CERTIFICATE----- (See attached file: smime.p7s)
=?iso-8859-1?Q?smime.p7s?=
Description: Binary data