From: | Allen Fair <dba(at)girders(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Moving Tablespaces |
Date: | 2006-01-03 21:00:07 |
Message-ID: | 43BAE5D7.8080807@girders.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi!
Looking at the "create tablespace " command in the docs, I was wondering
how feasible it is to move a database/tablespace to another
server/instance of PostgreSQL.
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?
Background: We have lots of databases with the same table structure,
with 1 database per client account on the server. I can setup the client
database tables to be built within a client 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.
Will we have problems with the catalog tables? Would we still need to
dump/restore without data to copy over the catalog information and table
structures?
Thanks,
Allen
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-01-03 21:13:35 | Re: Moving Tablespaces |
Previous Message | Allen Fair | 2006-01-03 20:53:01 | Moving Tablespaces |