Swapping volumes under tablespaces: supported?
Currently we refresh our test DB instance by cloning the single production
EC2 volume we use for our entire PG environment and attaching it to the dev
EC2 instance running Postgres. This works well.
But now we are about to add a large quantity of largely static data to our
database. To avoid cloning the static data every time we refresh test
(several times a day), we want to put the new data in its own tablespace
pointing to a different volume, then clone only the default volume to which
the Postgres and default spaces point.
Is that supported?
What if we wanted to replace just the static data? In this case the
unchanged volume would contain the Postgres metadata and we would be
swapping in only the static data volume. Given the important constraint
that nothing can have changed in re the metadata, would that be supported?
My concern is that Postgres might have some internal checksum or something
that would detect our volume swapping and complain on principle. As long as
we are meticulous about not changing the metadata* in the Postgres internal
DBs between volume-swaps, will PG play along?
* When we do make metadata changes we'll take the hit and clone everything.
--
Kenneth Tilton
*Director of Software Development*
*MCNA Dental Plans*
200 West Cypress Creek Road
Suite 500
Fort Lauderdale, FL 33309
954-730-7131 X181 (Office)
954-628-3347 (Fax)
1-800-494-6262 X181 (Toll Free)
ktilton@mcna.net <glipari@mcna.net> (Email)
www.mcna.net (Website)
CONFIDENTIALITY NOTICE: This electronic mail may contain information that
is privileged, confidential, and/or otherwise protected from disclosure to
anyone other than its intended recipient(s). Any dissemination or use of
this electronic mail or its contents by persons other than the intended
recipient(s) is strictly prohibited. If you have received this
communication in error, please notify the sender immediately by reply
e-mail so that we may correct our internal records. Please then delete the
original message. Thank you.
Kenneth Tilton <ktilton@mcna.net> writes:
Currently we refresh our test DB instance by cloning the single production
EC2 volume we use for our entire PG environment and attaching it to the dev
EC2 instance running Postgres. This works well.
But now we are about to add a large quantity of largely static data to our
database. To avoid cloning the static data every time we refresh test
(several times a day), we want to put the new data in its own tablespace
pointing to a different volume, then clone only the default volume to which
the Postgres and default spaces point.
Is that supported?
No. XID and table-OID considerations are going to give you headaches.
It's possible you can make it work as long as the static data is really
static (not "largely" static, but absolutely read-only) and you VACUUM
FREEZE all of it and then don't change the catalog entries about it in
the DB image being cloned. But you shouldn't imagine that we consider
this supported. If it breaks you get to keep both pieces.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Friday, February 8, 2013 at 10:58, Tom Lane wrote:
If it breaks you get to keep both pieces.
Tom is an optimist. My (unscheduled) attempt at this resulted in
a lot more than two pieces all of which appeared broken in their
own right.
If you want to (re)start a conversation about making
mount/unmount/move tablespace a reality be my guest, but, be
warned, there seem to be some very fundamental barriers.
Regards
Gavan Schneider
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
OK. What if we do not need to access the static data on the test volume? It
is a rare application that goes there, and for those we can bring over both
volumes/tablespaces.
Thx, ken
On Fri, Feb 8, 2013 at 10:09 PM, Gavan Schneider <pg-gts@snkmail.com> wrote:
On Friday, February 8, 2013 at 10:58, Tom Lane wrote:
If it breaks you get to keep both pieces.
Tom is an optimist. My (unscheduled) attempt at this resulted in a lot
more than two pieces all of which appeared broken in their own right.
If you want to (re)start a conversation about making mount/unmount/move
tablespace a reality be my guest, but, be warned, there seem to be some
very fundamental barriers.Regards
Gavan Schneider--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
--
Kenneth Tilton
*Director of Software Development*
*MCNA Dental Plans*
200 West Cypress Creek Road
Suite 500
Fort Lauderdale, FL 33309
954-730-7131 X181 (Office)
954-628-3347 (Fax)
1-800-494-6262 X181 (Toll Free)
ktilton@mcna.net <glipari@mcna.net> (Email)
www.mcna.net (Website)
CONFIDENTIALITY NOTICE: This electronic mail may contain information that
is privileged, confidential, and/or otherwise protected from disclosure to
anyone other than its intended recipient(s). Any dissemination or use of
this electronic mail or its contents by persons other than the intended
recipient(s) is strictly prohibited. If you have received this
communication in error, please notify the sender immediately by reply
e-mail so that we may correct our internal records. Please then delete the
original message. Thank you.