Thread: Using SAN Splits to instantly copy a DB

Using SAN Splits to instantly copy a DB

From
Paul Silveira
Date:
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.


Re: Using SAN Splits to instantly copy a DB

From
Jim Nasby
Date:
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)





Re: Using SAN Splits to instantly copy a DB

From
Ron Johnson
Date:
-----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-----

Re: Using SAN Splits to instantly copy a DB

From
Martijn van Oosterhout
Date:
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.

Attachment

Re: Using SAN Splits to instantly copy a DB

From
Ron Johnson
Date:
-----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-----

Re: Using SAN Splits to instantly copy a DB

From
Martijn van Oosterhout
Date:
> 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.

Attachment

Re: Using SAN Splits to instantly copy a DB

From
Ron Johnson
Date:
-----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-----

Re: Using SAN Splits to instantly copy a DB

From
Martijn van Oosterhout
Date:
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.

Attachment

Re: Using SAN Splits to instantly copy a DB

From
Ron Johnson
Date:
-----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-----