Moving Tablespaces
Hi!
Looking at the "create tablespace " command in the docs, I was wondering
how easy it is to move a database/tablespace to another server/instance
of PostgreSQL.
We have lots of databases, with the same definitions, with 1 database
per client account on the server. I can setup the database tables to be
built within an account-named tablespace.
create tablespace ts01 owner acctname location '/path/to/tspace';
create database db01 tablespace ts01;
create table tb01 (...) tablespace ts01;
Question: if database created with tablespace, i don't need to specify
on create table, correct? Or good form to do it regardless (it makes the
DDL less generic).
Now, when one database outgrows the server and I wish to move it to
another server, can i simply copy the tablespace /path/ to the new
server? I assume the same PG version must be running on each server.
Issue: moving databases using pg_dump and pg_restore is more
time-consuming than desired, requiring the db to be unavailable for a
while, which we want to avoid. Also, we want to minimize manual
processes that are more tedious and error-prone. I am looking for a
low-maintenance solution, thinking tablespaces holds the answer. If not,
is there a better approach?
Allen Fair <dba@girders.org> writes:
Looking at the "create tablespace " command in the docs, I was wondering
how easy it is to move a database/tablespace to another server/instance
of PostgreSQL.
It's not. The contents of the tablespace are tied to the instance
(because rows contain transaction numbers that won't match up with
another instance). Also, a tablespace by itself is just a useless
pile of bits; you can't access it unless there are system catalog
entries for the tables, and there's no way to export selected catalog
entries into another instance.
It's possible to get around the transaction-numbering problem by
doing VACUUM FREEZE before you copy the table files, but I don't
see any very workable solution to the catalog-entries problem.
You could possibly copy an entire database including its catalogs
after freezing the whole thing ... but the tablespace feature
certainly does not make that any easier.
regards, tom lane
Tom,
Thanks for the reply. That makes sense.
Do you or (or anyone else) suggest a method for moving databases from
one server to another without the time consuming dump/transfer/restore
process? Anything in the contrib directory or a good management tool?
Our databases can get large and we want to find the best way to plan for
when a database outgrows its current server.
How about a replication scheme and cutover? If so, is there a preferred
replication package to support this?
Thanks again! You folks are great, and that makes postgres great!
Allen
Tom Lane wrote:
Show quoted text
Allen Fair <dba@girders.org> writes:
Looking at the "create tablespace " command in the docs, I was wondering
how easy it is to move a database/tablespace to another server/instance
of PostgreSQL.It's not. The contents of the tablespace are tied to the instance
(because rows contain transaction numbers that won't match up with
another instance). Also, a tablespace by itself is just a useless
pile of bits; you can't access it unless there are system catalog
entries for the tables, and there's no way to export selected catalog
entries into another instance.It's possible to get around the transaction-numbering problem by
doing VACUUM FREEZE before you copy the table files, but I don't
see any very workable solution to the catalog-entries problem.
You could possibly copy an entire database including its catalogs
after freezing the whole thing ... but the tablespace feature
certainly does not make that any easier.
Tom,
Thanks for the reply. That makes sense.
Do you or (or anyone else) suggest a method for moving databases from
one server to another without the time consuming dump/transfer/restore
process? Anything in the contrib directory or a good management tool?
Our databases can get large and we want to find the best way to plan for
when a database outgrows its current server.
How about a replication scheme and cutover? If so, is there a preferred
replication package to support this?
Thanks again! You folks are great, and that makes postgres great!
Allen
Tom Lane wrote:
Show quoted text
Allen Fair <dba@girders.org> writes:
Looking at the "create tablespace " command in the docs, I was wondering
how easy it is to move a database/tablespace to another server/instance
of PostgreSQL.It's not. The contents of the tablespace are tied to the instance
(because rows contain transaction numbers that won't match up with
another instance). Also, a tablespace by itself is just a useless
pile of bits; you can't access it unless there are system catalog
entries for the tables, and there's no way to export selected catalog
entries into another instance.It's possible to get around the transaction-numbering problem by
doing VACUUM FREEZE before you copy the table files, but I don't
see any very workable solution to the catalog-entries problem.
You could possibly copy an entire database including its catalogs
after freezing the whole thing ... but the tablespace feature
certainly does not make that any easier.
Allen Fair <dba@girders.org> writes:
Our databases can get large and we want to find the best way to plan for
when a database outgrows its current server.How about a replication scheme and cutover? If so, is there a preferred
replication package to support this?
Slony-I was designed for this scenario (among others) and is actively
developed. There are also other replication solutions, some of them
proprietary.
-Doug
In article <43BB1FAA.6090001@cyberdesk.com>,
Allen Fair <allen@cyberdesk.com> wrote:
% Do you or (or anyone else) suggest a method for moving databases from
% one server to another without the time consuming dump/transfer/restore
% process? Anything in the contrib directory or a good management tool?
If you want to transfer an entire server to a different machine (i.e.,
not just a database, but all the databases), you can simply copy the
files, assuming the other machine has the same postgres version and
is architecturally compatible.
Tom's answer refers to mixing table files from one postgres server
with table files from a different postgres server. If you need to
do something like that, the answer is to use replication to move
the data over as it changes.
--
Patrick TJ McPhee
North York Canada
ptjm@interlog.com
You can also use WAL log shipping; there's been recent discussion about
that on -admin.
BTW, I'm seeing duplicated emails from you...
On Tue, Jan 03, 2006 at 08:31:09PM -0500, Doug McNaught wrote:
Allen Fair <dba@girders.org> writes:
Our databases can get large and we want to find the best way to plan for
when a database outgrows its current server.How about a replication scheme and cutover? If so, is there a preferred
replication package to support this?Slony-I was designed for this scenario (among others) and is actively
developed. There are also other replication solutions, some of them
proprietary.-Doug
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461