Re: tablespace restore

On 11/19/10 3:52 AM, Vangelis Katsikaros wrote:
> Hello
>
> I use postgres 8.3.12 on machineA and 8.4.5 on machineB.
>
> On machineA I have created a tablespace with
> CREATE TABLESPACE tablelocation_name LOCATION '/my/location/machineA';
>
> I then created a database with
> CREATE DATABASE db_name TABLESPACE tablelocation;
>
> I created tables, inserted data and created indexes.
>
> I now want to "move" the db from '/my/location/machineA' of machine A
> to '/other/location/machineB' of machine B. My question is how I can
> do a filesystem backup/restore (I want to move the indexes too - too
> time consuming to reindex).
>
> Machine B already has a postgres running, and postgres on machine B
> already has other databases. During this process I have no problem of
> shutting down postgres.

on machineB, logged on as the postgres user,
     pg_dump -Fc -h machinea databasename | pg_restore -d newdbname

thats really the only way this will work.

note, btw, dump/restore defaults to using COPY not INSERT, so it
shouldn't be as slow as you are afraid.  and I'm pretty sure each index
is created after the data is populated so it also should be relatively fast.