Re: [GENERAL] tablespace restore
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tablespace restore
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 wrote: > 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
Re: [GENERAL] tablespace restore
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 Katsikaroswrote: 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tablespace restore
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 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 >
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
