Thread: 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: > 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
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: > 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.
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