Re: Moving Tablespaces

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(at)girders(dot)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.