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 Fri, Nov 19, 2010 at 11:52 AM, Vangelis Katsikaros <ibob17@yahoo.gr>wrote:
Show quoted text
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 emptyRegards
Vangelis--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
Show quoted text
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 emptyRegards
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 Fri, Nov 19, 2010 at 1:30 PM, Vangelis Katsikaros <ibob17@yahoo.gr>wrote:
Show quoted text
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 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.