Обсуждение: Using SAN Splits to instantly copy a DB
Hello, Does anyone know if it is possible to use SAN Splitting (the function of splitting a mirror of disks so that there are two idential copies of a Postgres Instance)? The reason that I ask is because I'm working on a Hashing database to scale postgres to the moon and I'd like to be able to create exact replica's of my Postgres database and then divide the throughput of the data independently to both. (through a hashing algoithm, I'd be able to then split the I/O to each of the nodes evenly...) The question basically boils down to whether I can have two idential copies of the DB running on different servers at the same time. I guess as long as I have the Server Name be different on each server, the database platform should not care... Once the split was done, they would go on their own independent way and take a new life of their own. I would then delete 1/2 of the data from each node. (opposite data of course so that it's still logically one unit) and I'd be able to provide twice the throughput because of this. Thoughts? -- View this message in context: http://www.nabble.com/Using-SAN-Splits-to-instantly-copy-a-DB-tf2632763.html#a7348176 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Nov 14, 2006, at 3:44 PM, Paul Silveira wrote: > Does anyone know if it is possible to use SAN Splitting (the > function of > splitting a mirror of disks so that there are two idential copies of a > Postgres Instance)? There are essentially 2 rules for doing a filesystem-level copy of the database: 1. A copy is unlikely to work on a different architecture or OS. 2. You must either shut the database down to make the copy, use PITR, or do a filesystem snapshot or equivalent. Rule 2 is needed to ensure that the data files in the database are all consistent to each other. If you have a SAN/filesystem with snapshot capability (sounds like you do), then you can do that to create the copy rather than shutting the database down. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 11/15/06 09:47, Jim Nasby wrote: > On Nov 14, 2006, at 3:44 PM, Paul Silveira wrote: [snip] > Rule 2 is needed to ensure that the data files in the database are all > consistent to each other. If you have a SAN/filesystem with snapshot > capability (sounds like you do), then you can do that to create the copy > rather than shutting the database down. How does SAN-snapshot ensure transactional consistency? - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFW217S9HxQb37XmcRAocuAKCBvrpXWQrNeAEaFQ7UqXZxj0NPnwCfbbYP 9sPsnMb0uYqyPonOOFKVR6U= =IL6Q -----END PGP SIGNATURE-----
On Wed, Nov 15, 2006 at 01:41:47PM -0600, Ron Johnson wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On 11/15/06 09:47, Jim Nasby wrote: > > On Nov 14, 2006, at 3:44 PM, Paul Silveira wrote: > [snip] > > Rule 2 is needed to ensure that the data files in the database are all > > consistent to each other. If you have a SAN/filesystem with snapshot > > capability (sounds like you do), then you can do that to create the copy > > rather than shutting the database down. > > How does SAN-snapshot ensure transactional consistency? There is write-ahead logging to do that. It's the same machanism used to ensure database consistancy after a crash. When you take a snapshot and start a new postmaster on the snapshot, it sees what looks like a crashed database and recovers it to the instant it snapshotted (aka "crashed"). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 11/15/06 14:28, Martijn van Oosterhout wrote: > On Wed, Nov 15, 2006 at 01:41:47PM -0600, Ron Johnson wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> On 11/15/06 09:47, Jim Nasby wrote: >>> On Nov 14, 2006, at 3:44 PM, Paul Silveira wrote: >> [snip] >>> Rule 2 is needed to ensure that the data files in the database are all >>> consistent to each other. If you have a SAN/filesystem with snapshot >>> capability (sounds like you do), then you can do that to create the copy >>> rather than shutting the database down. >> How does SAN-snapshot ensure transactional consistency? > > There is write-ahead logging to do that. It's the same machanism used > to ensure database consistancy after a crash. When you take a snapshot > and start a new postmaster on the snapshot, it sees what looks like a > crashed database and recovers it to the instant it snapshotted (aka > "crashed"). How does it know what a crashed PostgreSQL database look like? Besides, active transactions need to be *rolled back*, not written ahead, since half the data hasn't been sent from the computer yet. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFW3nMS9HxQb37XmcRAn0vAJ996FVUuoBrtiS/B59iU5jkkJxR2ACeM5A8 BE1Bvph5pG0a+uLg9Kc/zwc= =ZwsZ -----END PGP SIGNATURE-----
> How does it know what a crashed PostgreSQL database look like? > > Besides, active transactions need to be *rolled back*, not written > ahead, since half the data hasn't been sent from the computer yet. There's a section of the docs dealing with this: http://www.postgresql.org/docs/8.1/static/wal.html Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 11/15/06 14:46, Martijn van Oosterhout wrote: >> How does it know what a crashed PostgreSQL database look like? >> >> Besides, active transactions need to be *rolled back*, not written >> ahead, since half the data hasn't been sent from the computer yet. > > There's a section of the docs dealing with this: > > http://www.postgresql.org/docs/8.1/static/wal.html Oh, wait, I missed this in your previous email: "start a new postmaster". That resolves a *lot* of my confusion. However, what if the WAL is not on the SAN? You'd have to shut down pg anyway, in order to copy the WAL to a new directory, no? Lastly: in order to do SAN splitting without risking your data, wouldn't you have to configure the disks as RAID-15 (mirrored RAID-5), since splitting a RAID10 would leave you with stripesets? - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFW4XRS9HxQb37XmcRAmE2AKCczirloNDRAIIgftniLYDn7G2+cwCg6pN2 mQGjvqHE3AW4DpLc6yN+0ZI= =HFlW -----END PGP SIGNATURE-----
On Wed, Nov 15, 2006 at 03:25:38PM -0600, Ron Johnson wrote: > However, what if the WAL is not on the SAN? You'd have to shut down > pg anyway, in order to copy the WAL to a new directory, no? You have to copy the *entire* cluster, you cannot split out one database, for example. Two postmaster instances cannot share data, period. > Lastly: in order to do SAN splitting without risking your data, > wouldn't you have to configure the disks as RAID-15 (mirrored > RAID-5), since splitting a RAID10 would leave you with stripesets? As long as you get all the data it OK. I'm not 100% clear on what SAN splitting is so I'm not totally sure. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 11/15/06 15:51, Martijn van Oosterhout wrote: > On Wed, Nov 15, 2006 at 03:25:38PM -0600, Ron Johnson wrote: >> However, what if the WAL is not on the SAN? You'd have to shut down >> pg anyway, in order to copy the WAL to a new directory, no? > > You have to copy the *entire* cluster, you cannot split out one > database, for example. Two postmaster instances cannot share data, > period. > >> Lastly: in order to do SAN splitting without risking your data, >> wouldn't you have to configure the disks as RAID-15 (mirrored >> RAID-5), since splitting a RAID10 would leave you with stripesets? > > As long as you get all the data it OK. I'm not 100% clear on what SAN > splitting is so I'm not totally sure. If it's anything like what happens on SCSI controllers, you can break a mirrorset and wind up with 2 identical JBODs. Similarly, you can break a RAID-10 set into 2 identical RAID-0 sets. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFW5D2S9HxQb37XmcRAuH8AJ9UtfDrr1+3xhdoaivoKxxvz5hALACcDNuk mlRbye96rdaZDRGk8WG7YNU= =aCO2 -----END PGP SIGNATURE-----