Thread: tablespace restore

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.


I have tried some things unsuccessfully:
1)  - stop postgres on machine B
     - copy dir of tablelocation of machine A to
'/other/location/machineB' on machine B
     - start postgres on machine B
     - CREATE TABLESPACE tablelocation_name LOCATION
'/other/location/machineB';
       ERROR:  directory "/other/location/machineB" is not empty


Regards
Vangelis

Vangelis,

I don't believe you can do file level copying of single databases (especially as they are different versions).

Take a look at pg_dump in the documentation.  This will do what you need I think but at a logical level rather than physical.

On 11/19/2010 03:12 PM, Matthew Walden wrote:
> Vangelis,
>
> I don't believe you can do file level copying of single databases
> (especially as they are different versions).

Hi Matthew, thanks for your answer.

If the different versions is a problem, I can downgrade one server and
then upgrade afterwards.

> Take a look at pg_dump in the documentation.  This will do what you need I
> think but at a logical level rather than physical.

Hm, from what I understand pg_dump doesn't dump indexes (ie the indexing
information - not an SQL clause in CREATE TABLE). Also I have lots of
Giga of data, so I imagine that pg_restore will take quite some time to
execute the INSERTs and reindex.

Regards
Vangelis

>
> On Fri, Nov 19, 2010 at 11:52 AM, Vangelis Katsikaros<ibob17@yahoo.gr>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.
>>
>>
>> I have tried some things unsuccessfully:
>> 1)  - stop postgres on machine B
>>     - copy dir of tablelocation of machine A to '/other/location/machineB'
>> on machine B
>>     - start postgres on machine B
>>     - CREATE TABLESPACE tablelocation_name LOCATION
>> '/other/location/machineB';
>>       ERROR:  directory "/other/location/machineB" is not empty
>>
>>
>> Regards
>> Vangelis
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>

The problem is that there is a lot of metadata outside the tablespace you created (information that the catalog tables keep on your new database and its objects) and this can only be restored with a full restore which would overwrite your pre-existing databases on your target.  I hate to say it can't be done because someone will prove me wrong but I think pg_dump is your only answer even if you do need to give it some time and then rebuild the indexes.

That said, perhaps someone else can confirm or deny this too.

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.

Vangelis,

Did you find a best way to achieve what you were asking about? I have a
similar desire to migrate a large table and its indices.

Regards,
Sky
--
View this message in context: http://postgresql.1045698.n5.nabble.com/tablespace-restore-tp3272200p3361935.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.