[GENERAL] storing postgres data on dropbox
This is a queestion from a Postgresql novice. I use Postgresql in a single-user environment on a Mac with OS Sierra. I use AquaFold DataStudio as a client, which is nice but also keeps me woefully ignorant about many aspects of the underlying application. As I understand it, Postgres data are stored in my homedirectory /users/martin/Library ApplicationSupport/Postgres/var9.5/base/. I have read things on the Web about backing up data to Dropbox (I have 80GB of data) . But that means that my data sit first in the base directory, then in the Dropbox directory from which they are backed up to the Dropbox cloud. So my 80 GB of data take up 160GB on my machine. Is there a way of installing the base directory directly in the Dropbox directory so that the data are stored only once on my machine? There is a more radical version of that question. I spend a lot of time in a second home, where I have a MacPro that runs the same OS. Would it be possible to move the entire Postgres environment into Dropbox. If that were possible I could use the same Postgres installation in Location 1 and Location 2. I assume there are 'gotchas' in that scenario, but it seems worth asking. On the the other hand, if the primary location of the base directory inside Dropbox directory, the fileparth from the application to the data would be identical on the two machines. I am the only user of it, and when I go to Location 2 I would make sure that all Dropbox files have properly synched before firing up Postgres. In theory it should work but I am fond of saying that the difference between theory and practice is usually greater in practice than in theory. With thanks in advance for any advice Martin Mueller Professor emeritus of English and Classics Northwestern University
Re: [GENERAL] storing postgres data on dropbox
How close is close enough? In my case, the machines run OS Sierra, and the installation uses the same directory paths Keeping the Postgres version in sync should be simple. Is that close enough? In MySQL you can copy and paste individual tables if the data are kept in ISAM, but INNO is hopeless that way. Is Postgres more like INNO than ISAM when it comes to table storage? On 6/18/17, 12:58 PM, "pgsql-general-ow...@postgresql.org on behalf of Karsten Hilbert" <pgsql-general-ow...@postgresql.org on behalf of karsten.hilb...@gmx.net> wrote: >On Sun, Jun 18, 2017 at 05:30:44PM +0000, Martin Mueller wrote: > >> Thank for this very helpful answer, which can be >> implemented for less than $100. For somebody who started >> working a 128k Mac in the eighties, it is mindboggling that >> for that amount you can buy a terabyte of storage in a device >> that you put in a coat pocket. I'll read up on rsync > >I seem to remember that for this to work the two machines >must be *very* close in architecture, and the PostgreSQL >versions best be exactly the same. > >Karsten >-- >GPG key ID E4071346 @ eu.pool.sks-keyservers.net >E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >https://urldefense.proofpoint.com/v2/url?u=http-3A__www.postgresql.org_mailpref_pgsql-2Dgeneral=DwIBAg=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk=dRn-urP2CTrTrg6xFXNVHRpTwbZJSAI0SDEIGtDW5tM=NMV4EDSxwFIOUoomuK06tWbUWUTkD5pd_Q5thb2xZ6I= > -- 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] storing postgres data on dropbox
Thank for this very helpful answer, which can be implemented for less than $100. For somebody who started working a 128k Mac in the eighties, it is mindboggling that for that amount you can buy a terabyte of storage in a device that you put in a coat pocket. I'll read up on rsync On 6/18/17, 11:13 AM, "Adrian Klaver" <adrian.kla...@aklaver.com> wrote: >On 06/18/2017 06:16 AM, Martin Mueller wrote: >> Why not a PostgreSQL-database somewhere in the cloud? Good question, but >> it's a question of money and performance. I used MySQL for many years and >> then moved a dataset to an instance on AWS. The performance was horribly >> slow. Then some kind soul at my institution hooked me up with "Aurora," >> which I take to be MySQL on steroids. That was great, and the performance >> was almost as good as on my desktopc. But it cost hundreds of dollars per >> month. I work at home with a machine that has 32 GB of memory. In order to >> get comparable performance from a cloud-based Postgres instance, I'd have to >> spend a lot of money that I don't have. Dropbox costs $120 a year for a >> terabyte of storage, which is very affordable. > >If it where me I would pick up 1TB external hard drive then: > >1) On your Mac(Location 1) stop Postgres and then back up/sync your base >directory to the external harddrive. > >2) Take the external hard drive to Location 2. > >3) Stop Postgres at Location 2 and then sync to base directory there. > >4) Start Postgres at location 2. > >5) Repeat for going other direction. > >It is similar to using Dropbox, with the difference being you do not >have Dropbox trying to sync while you are using the database. That I am >pretty sure will not end well. The above does depend on familiarity with >programs like rsync or Unison for the syncing portion. > > > > >-- >Adrian Klaver >adrian.kla...@aklaver.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] storing postgres data on dropbox
I think I get it. 'base' is not the data directory but a child of var-9.5, which (with its entire path) is the "data directory". I honestly don't recall how I installed Posgres, but I'm pretty sure that I picked the default method from the Postgres. Many thanks for your help, which is exceptionally clear and detailed. MM On 6/18/17, 3:03 PM, "Adrian Klaver" <adrian.kla...@aklaver.com> wrote: >On 06/18/2017 01:00 PM, Martin Mueller wrote: >> Did you mean that "/users/martin/Library >> ApplicationSupport/Postgres/var9.5/base/" is above or below the data >> directory? As I understand it Postgres is the highest Postgres specific >> directory. It contains just one child directory, var-9.5, which has a lot of >> subdirectories, including 'base'. So I assume that "base" is the "entire >> Postgres directory". Is that correct > >Pretty sure the answer is no. I am going to say the Postgres data >directory is /users/martin/Library ApplicationSupport/Postgres/var9.5/. > >Take a look at this link: > >https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_9.6_static_storage-2Dfile-2Dlayout.html=DwICaQ=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk=EE03EBpU01pYlVG72ZO4h4D-1JJd--vXBmEvErByUOo=ekr93dxabdAgdsxrrTh5gg3pQOUFrX9muS6YFV9SIwM= > > >and see if what is under: > >/users/martin/Library ApplicationSupport/Postgres/var9.5/ > >looks similar. > >*** It would also help to know how you installed Postgres? *** > >> >> >> >> >> >>> >>> 3) You will need to make sure you are copying/syncing the entire >>> Postgres data directory: >>> >>> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_9.6_static_storage-2Dfile-2Dlayout.html=DwICaQ=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk=lQpZV77iEes6tm1L3zsHQm9eceGhQh8UV4IOgzDvapg=MpRqOYUdfX_Z4OK9-AFg2b7glEe4lY2aYupB6508ZjQ= >>> >>> From you original post the directory you mentioned: >>> >>> /users/martin/Library ApplicationSupport/Postgres/var9.5/base/ >>> >>> looks to be below the top level data directory. >>> >>> While I remember, how are you installing/updating Postgres on your machines? >>> > > >-- >Adrian Klaver >adrian.kla...@aklaver.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] storing postgres data on dropbox
Did you mean that "/users/martin/Library ApplicationSupport/Postgres/var9.5/base/" is above or below the data directory? As I understand it Postgres is the highest Postgres specific directory. It contains just one child directory, var-9.5, which has a lot of subdirectories, including 'base'. So I assume that "base" is the "entire Postgres directory". Is that correct > >3) You will need to make sure you are copying/syncing the entire >Postgres data directory: > >https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_docs_9.6_static_storage-2Dfile-2Dlayout.html=DwICaQ=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk=lQpZV77iEes6tm1L3zsHQm9eceGhQh8UV4IOgzDvapg=MpRqOYUdfX_Z4OK9-AFg2b7glEe4lY2aYupB6508ZjQ= > > > From you original post the directory you mentioned: > >/users/martin/Library ApplicationSupport/Postgres/var9.5/base/ > >looks to be below the top level data directory. > >While I remember, how are you installing/updating Postgres on your machines? > -- 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] storing postgres data on dropbox
Why not a PostgreSQL-database somewhere in the cloud? Good question, but it's a question of money and performance. I used MySQL for many years and then moved a dataset to an instance on AWS. The performance was horribly slow. Then some kind soul at my institution hooked me up with "Aurora," which I take to be MySQL on steroids. That was great, and the performance was almost as good as on my desktopc. But it cost hundreds of dollars per month. I work at home with a machine that has 32 GB of memory. In order to get comparable performance from a cloud-based Postgres instance, I'd have to spend a lot of money that I don't have. Dropbox costs $120 a year for a terabyte of storage, which is very affordable. On 6/18/17, 2:43 AM, "pgsql-general-ow...@postgresql.org on behalf of Andreas Kretschmer" <pgsql-general-ow...@postgresql.org on behalf of andr...@a-kretschmer.de> wrote: > > >Am 18.06.2017 um 03:03 schrieb Martin Mueller: >> This is a queestion from a Postgresql novice. >> >> I use Postgresql in a single-user environment on a Mac with OS Sierra. >> I use AquaFold DataStudio as a client, which is nice but also keeps me >> woefully ignorant about many aspects of the underlying application. >> >> As I understand it, Postgres data are stored in my homedirectory >> /users/martin/Library ApplicationSupport/Postgres/var9.5/base/. I >> have read things on the Web about backing up data to Dropbox (I have >> 80GB of data) . But that means that my data sit first in the base >> directory, then in the Dropbox directory from which they are backed up >> to the Dropbox cloud. So my 80 GB of data take up 160GB on my >> machine. Is there a way of installing the base directory directly in >> the Dropbox directory so that the data are stored only once on my machine? > >You can store Backups there (dumps), but i would strongly advise >against to store the db there. PostgreSQL relys on the fsync, that's not >possible in this case. I'm sure, a total data disaster would not be a >question, only when it would happen. > > >Why not a PostgreSQL-database somewhere in the cloud? > > >Regards, Andreas > >-- >2ndQuadrant - The PostgreSQL Support Company. >www.2ndQuadrant.com > > > >-- >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >To make changes to your subscription: >https://urldefense.proofpoint.com/v2/url?u=http-3A__www.postgresql.org_mailpref_pgsql-2Dgeneral=DwICaQ=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk=KXaWmcxNhlCSHssyqrqU1S_mVlceHc7yM3UpQ2fBVXQ=9eN95fgPNJGdCFSD_ozbDci12h1SjX_qAovIBV01Pcc= > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
