Обсуждение: How to store data on an external drive
Hi there, As a beginner of PostgreSQL, I installed PostgreSQL 8.3.7 on Ubuntu 9.04 by using sudo apt-get install. Now, I want to store PostgreSQL data on an external drive because I work both on my office machine and on my home machine a lot. This way, I can always bring my external drive to either location and work on data stored on the drive. I tried to use tablespaces as in http://www.postgresql.org/docs/8.3/static/manage-ag-tablespaces.html. But, it does not seem to work because once I create a tablespace located at one folder in the external drive for one machine, I can not create a tablespace located at the same folder on the other machine since the folder is not empty any more. Then, on my home machine, I copied everything in /var/lib/postgresql/8.3/main to a folder on my external drive (say folder /media/disk/psqldata) and edited postgresql.conf to comment out line data_directory = '/var/lib/postgresql/8.3/main' and add data_directory = '/media/disk/psqldata'. I am hoping that once I edit postgresql.conf on my office machine the same way, it would work. But, my guess is that this won't work. Please let me know whether this is right way to do it. Please let me know your way of doing it too. Thanks. Best, Jia
your tables are probably not using the new default tablespacecan you put the WHOLE database on the external drive?
SET default_tablespace = new_tablespace_name
any new tables will be placed into new default tablespace by inserting TABLESPACE clause
Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
> Date: Mon, 7 Sep 2009 20:20:52 -0400
> From: chen.1002@gmail.com
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to store data on an external drive
>
> Hi there,
>
> As a beginner of PostgreSQL, I installed PostgreSQL 8.3.7 on Ubuntu 9.04
> by using sudo apt-get install.
>
> Now, I want to store PostgreSQL data on an external drive because I work
> both on my office machine and on my home machine a lot. This way, I can
> always bring my external drive to either location and work on data
> stored on the drive.
>
> I tried to use tablespaces as in
> http://www.postgresql.org/docs/8.3/static/manage-ag-tablespaces.html.
> But, it does not seem to work because once I create a tablespace located
> at one folder in the external drive for one machine, I can not create a
> tablespace located at the same folder on the other machine since the
> folder is not empty any more.
>
> Then, on my home machine, I copied everything in
> /var/lib/postgresql/8.3/main to a folder on my external drive (say
> folder /media/disk/psqldata) and edited postgresql.conf to comment out
> line data_directory = '/var/lib/postgresql/8.3/main' and add
> data_directory = '/media/disk/psqldata'. I am hoping that once I edit
> postgresql.conf on my office machine the same way, it would work. But,
> my guess is that this won't work.
>
> Please let me know whether this is right way to do it. Please let me
> know your way of doing it too. Thanks.
>
> Best,
> Jia
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
With Windows Live, you can organize, edit, and share your photos. Click here.
Hi Martin, Thanks for reply. However, your answer confuses me. >your tables are probably not using the new default tablespace >SET default_tablespace = new_tablespace_name >any new tables will be placed into new default tablespace by inserting TABLESPACE clause This is what I did: I created tablespace on my home machine by using CREATE TABLESPACE psqldata LOCATION '/media/disk/psqldata'; The mounted folder '/media/disk/psqldata' is on my external drive. Yes, it works fine on my home machine when I use SET default_tablespace = psqldata. The problem came when I went to work on my office machine with the same external drive. I tried to create a tablespace by using the same clause, CREATE TABLESPACE psqldata LOCATION '/media/disk/psqldata'; But, I got an error message saying that I can create this table space because the folder is not empty. The PostgreSQL manual dose say "The location must be an existing, empty directory" as in http://www.postgresql.org/docs/8.3/static/manage-ag-tablespaces.html. There is already stuff in that folder after I created the tablespace on my home machine. So, I did not even create a tablespace for that folder on my office machine yet. I don't see how set SET default_tablespace = new_tablespace_name can solve this problem. Maybe, there was a misunderstanding between us in your last e-mail. > Can you put the WHOLE database on the external drive? This is my question to you guys. I have not succeeded putting whole database on my external drive yet with PostgreSQL. Yet, it seems to be easy to do the same with MySQL by editing my.cnf file. Thanks. Best, Jia Martin Gainty wrote: > your tables are probably not using the new default tablespace > > SET default_tablespace = new_tablespace_name > any new tables will be placed into new default tablespace by inserting TABLESPACE clause > > can you put the WHOLE database on the external drive? > Martin Gainty > ______________________________________________ > Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité > > Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene > Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede > unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. > Diese Nachricht dient lediglich dem Austausch von Informationen und > entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten > Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den > Inhalt uebernehmen. > Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bontéque pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite.Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donnéque les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pourle contenu fourni. > > > > > > > Date: Mon, 7 Sep 2009 20:20:52 -0400 > > From: chen.1002@gmail.com > > To: pgsql-general@postgresql.org > > Subject: [GENERAL] How to store data on an external drive > > > > Hi there, > > > > As a beginner of PostgreSQL, I installed PostgreSQL 8.3.7 on Ubuntu > 9.04 > > by using sudo apt-get install. > > > > Now, I want to store PostgreSQL data on an external drive because I > work > > both on my office machine and on my home machine a lot. This way, I can > > always bring my external drive to either location and work on data > > stored on the drive. > > > > I tried to use tablespaces as in > > http://www.postgresql.org/docs/8.3/static/manage-ag-tablespaces.html. > > But, it does not seem to work because once I create a tablespace > located > > at one folder in the external drive for one machine, I can not create a > > tablespace located at the same folder on the other machine since the > > folder is not empty any more. > > > > Then, on my home machine, I copied everything in > > /var/lib/postgresql/8.3/main to a folder on my external drive (say > > folder /media/disk/psqldata) and edited postgresql.conf to comment out > > line data_directory = '/var/lib/postgresql/8.3/main' and add > > data_directory = '/media/disk/psqldata'. I am hoping that once I edit > > postgresql.conf on my office machine the same way, it would work. But, > > my guess is that this won't work. > > > > Please let me know whether this is right way to do it. Please let me > > know your way of doing it too. Thanks. > > > > Best, > > Jia > > > > -- > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > ------------------------------------------------------------------------ > With Windows Live, you can organize, edit, and share your photos. > Click here. <http://www.windowslive.com/Desktop/PhotoGallery>
On 2009-09-08, Jia Chen <chen.1002@gmail.com> wrote: > Hi there, > > As a beginner of PostgreSQL, I installed PostgreSQL 8.3.7 on Ubuntu 9.04 > by using sudo apt-get install. > > Now, I want to store PostgreSQL data on an external drive because I work > both on my office machine and on my home machine a lot. This way, I can > always bring my external drive to either location and work on data > stored on the drive. start again and install from the source. the ubuntu packge spreads the components of the postgres database about putting some in /usr/bin, /usr/lib, /etc/postgres, /var/log, /var/lib and /etc you should install them all onto the removable drive instead. it's not just the table data that is needed need for the database to work.
On Tue, Sep 08, 2009 at 08:52:23AM +0000, Jasen Betts wrote: > On 2009-09-08, Jia Chen <chen.1002@gmail.com> wrote: > > Now, I want to store PostgreSQL data on an external drive because I work > > both on my office machine and on my home machine a lot. This way, I can > > always bring my external drive to either location and work on data > > stored on the drive. > > start again and install from the source. > the ubuntu packge spreads the components of the postgres database about > putting some in /usr/bin, /usr/lib, /etc/postgres, /var/log, /var/lib and /etc I don't think you need to go that far. I'd just do an "initdb" somewhere on the removable disk and then start PG pointing at where the cluster was (i.e. postgres -D /media/disk/psqldata) and all should be good. I'd stay away from the official system startup scripts for PG. > you should install them all onto the removable drive instead. > it's not just the table data that is needed need for the database to work. Yup, the table data is very tied to the state of transactions and other "system level" information, you need to keep everything together unfortunately. This is the price of having transactions with ACID semantics. One other thing to note is that PG tends to change the on-disk format between "major" versions, where a major version is defined as the second digit changing. 8.3 and 8.2 are different major versions and have different on-disk formats, while 8.3.6 and 8.3.7 are not. -- Sam http://samason.me.uk/
[ please CC the mailing list and not the list owner, they answer mailing list questions not PG questions ] On Tue, Sep 08, 2009 at 10:31:50AM -0400, Jia Chen wrote: > Sam Mason wrote: > >I don't think you need to go that far. I'd just do an "initdb" > >somewhere on the removable disk and then start PG pointing at where the > >cluster was (i.e. postgres -D /media/disk/psqldata) and all should be > >good. I'd stay away from the official system startup scripts for PG. > > If I am not mistaken, the paragraph above means that I don't need to > reinstall postgresql from source. Yes; these are all standard programs included with Debian/Ubuntu packages as normal. Have a look through the man pages for: update-rc.d initdb postgres > >Yup, the table data is very tied to the state of transactions and > >other "system level" information, you need to keep everything together > >unfortunately. This is the price of having transactions with ACID > >semantics. > > However, this paragraph implies that I do need to put other "system > level" information together on the external drive. Do you mean that I > can put it together without re-installation? If so, could you offer > some hints on how to do that? Thanks. initdb creates a new PG cluster (i.e. the set of files that PG considers to be a database). You should direct this to be run on your external disk and then get PG running using this cluster. This is what "postgres -D /media/disk/psqldata" does, i.e. start the postgres server. Once it's started you can connect to it from the "normal" clients, psql, odbc whatever you want. I expect all you need to run is: sudo /etc/init.d/postgresql-8.3 stop sudo update-rc.d -f postgresql-8.3 remove initdb /media/disk/psqldata postgres -D /media/disk/psqldata from there on, all you need to do is to run the last line when you plug the drive in. Before you take the drive out, just hit the normal Ctrl+C and PG will shutdown cleanly. -- Sam http://samason.me.uk/
Hi Sam, Thanks for your help. Your solution seems to work. I may let you know if I run into problems. Thanks again. Best, Jia On Tue, Sep 8, 2009 at 11:31 AM, Sam Mason<sam@samason.me.uk> wrote: > [ please CC the mailing list and not the list owner, they answer > mailing list questions not PG questions ] > > On Tue, Sep 08, 2009 at 10:31:50AM -0400, Jia Chen wrote: >> Sam Mason wrote: >> >I don't think you need to go that far. I'd just do an "initdb" >> >somewhere on the removable disk and then start PG pointing at where the >> >cluster was (i.e. postgres -D /media/disk/psqldata) and all should be >> >good. I'd stay away from the official system startup scripts for PG. >> >> If I am not mistaken, the paragraph above means that I don't need to >> reinstall postgresql from source. > > Yes; these are all standard programs included with Debian/Ubuntu > packages as normal. Have a look through the man pages for: > > update-rc.d > initdb > postgres > >> >Yup, the table data is very tied to the state of transactions and >> >other "system level" information, you need to keep everything together >> >unfortunately. This is the price of having transactions with ACID >> >semantics. >> >> However, this paragraph implies that I do need to put other "system >> level" information together on the external drive. Do you mean that I >> can put it together without re-installation? If so, could you offer >> some hints on how to do that? Thanks. > > initdb creates a new PG cluster (i.e. the set of files that PG considers > to be a database). You should direct this to be run on your external > disk and then get PG running using this cluster. This is what "postgres > -D /media/disk/psqldata" does, i.e. start the postgres server. Once > it's started you can connect to it from the "normal" clients, psql, odbc > whatever you want. > > I expect all you need to run is: > > sudo /etc/init.d/postgresql-8.3 stop > sudo update-rc.d -f postgresql-8.3 remove > initdb /media/disk/psqldata > postgres -D /media/disk/psqldata > > from there on, all you need to do is to run the last line when you plug > the drive in. Before you take the drive out, just hit the normal Ctrl+C > and PG will shutdown cleanly. > > -- > Sam http://samason.me.uk/ > -- Ohio State University - Finance 248 Fisher Hall 2100 Neil Ave. Columbus, Ohio 43210 Telephone: 614-292-2979 http://www.fisher.osu.edu/~chen_1002/