Search Postgresql Archives

Moving Tablespaces

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux