Moving Tablespaces

From: Allen Fair <dba(at)girders(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Moving Tablespaces
Date: 2006-01-03 20:53:01
Message-ID: 43BAE42D.4070109@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 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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Allen Fair 2006-01-03 21:00:07 Moving Tablespaces
Previous Message Tom Lane 2006-01-03 20:36:48 Re: Problem restoring db-dump on Postgres 8.0.4 Power5