Обсуждение: [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
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
Why not a PostgreSQL-database somewhere in the cloud? Good question, but it's a question of money and performance. I usedMySQL for many years and then moved a dataset to an instance on AWS. The performance was horribly slow. Then some kindsoul at my institution hooked me up with "Aurora," which I take to be MySQL on steroids. That was great, and the performancewas almost as good as on my desktopc. But it cost hundreds of dollars per month. I work at home with a machinethat has 32 GB of memory. In order to get comparable performance from a cloud-based Postgres instance, I'd have tospend 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-owner@postgresql.org on behalf of Andreas Kretschmer" <pgsql-general-owner@postgresql.orgon behalf of andreas@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&d=DwICaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=KXaWmcxNhlCSHssyqrqU1S_mVlceHc7yM3UpQ2fBVXQ&s=9eN95fgPNJGdCFSD_ozbDci12h1SjX_qAovIBV01Pcc&e=
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 usedMySQL for many years and then moved a dataset to an instance on AWS. The performance was horribly slow. Then some kindsoul at my institution hooked me up with "Aurora," which I take to be MySQL on steroids. That was great, and the performancewas almost as good as on my desktopc. But it cost hundreds of dollars per month. I work at home with a machinethat has 32 GB of memory. In order to get comparable performance from a cloud-based Postgres instance, I'd have tospend 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.klaver@aklaver.com
On Sun, Jun 18, 2017 at 13:16:16 +0000, Martin Mueller <martinmueller@northwestern.edu> wrote: >Why not a PostgreSQL-database somewhere in the cloud? Good question, but it's a question of money and performance. I usedMySQL for many years and then moved a dataset to an instance on AWS. The performance was horribly slow. Then some kindsoul at my institution hooked me up with "Aurora," which I take to be MySQL on steroids. That was great, and the performancewas almost as good as on my desktopc. But it cost hundreds of dollars per month. I work at home with a machinethat has 32 GB of memory. In order to get comparable performance from a cloud-based Postgres instance, I'd have tospend a lot of money that I don't have. Dropbox costs $120 a year for a terabyte of storage, which is very affordable. You aren't going to be able to use copies of the raw files taken while the database is running, to restore the database. Storing compressed output from pg_dumpall is probably the best way to create backups you can restore from.
Thank for this very helpful answer, which can be implemented for less than $100. For somebody who started working a 128kMac in the eighties, it is mindboggling that for that amount you can buy a terabyte of storage in a device that you putin a coat pocket. I'll read up on rsync On 6/18/17, 11:13 AM, "Adrian Klaver" <adrian.klaver@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 usedMySQL for many years and then moved a dataset to an instance on AWS. The performance was horribly slow. Then some kindsoul at my institution hooked me up with "Aurora," which I take to be MySQL on steroids. That was great, and the performancewas almost as good as on my desktopc. But it cost hundreds of dollars per month. I work at home with a machinethat has 32 GB of memory. In order to get comparable performance from a cloud-based Postgres instance, I'd have tospend 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.klaver@aklaver.com
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
> On Jun 18, 2017, at 10:58 AM, Karsten Hilbert <Karsten.Hilbert@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. If the two machines have the same architecture you can also just have the data directory (or the whole postgresql installation) installed on an external drive and run it from there. Plug it in, start postgresql, use it.Shut down postgresql, unplug it. I've been running from an external drive for years with no problems, but backing it up regularly to the machines you plug it into (with pg_dump) is probably a good idea. I have the entire postgresql installation on the external drive, and have /Volumes/whatever/pgsql/bin early in my path, so if the drive is plugged in pg_ctl, psql etc go to the installation on the external drive. With one of the little samsung usb3 SSDs it'll fit in your pocket. Cheers, Steve
How close is close enough? In my case, the machines run OS Sierra, and the installation uses the same directory paths Keepingthe 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 Postgresmore like INNO than ISAM when it comes to table storage? On 6/18/17, 12:58 PM, "pgsql-general-owner@postgresql.org on behalf of Karsten Hilbert" <pgsql-general-owner@postgresql.orgon behalf of Karsten.Hilbert@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&d=DwIBAg&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=dRn-urP2CTrTrg6xFXNVHRpTwbZJSAI0SDEIGtDW5tM&s=NMV4EDSxwFIOUoomuK06tWbUWUTkD5pd_Q5thb2xZ6I&e=
On Sun, Jun 18, 2017 at 06:29:50PM +0000, Martin Mueller wrote: > 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? *more* like INNO but not at all *like* INNO :-) Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Sun, Jun 18, 2017 at 06:29:50PM +0000, Martin Mueller wrote: > 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? I am not an expert on that. Methinks the mailing list archive should have posts on that topic. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On 06/18/2017 11:29 AM, Martin Mueller wrote: > How close is close enough? In my case, the machines run OS Sierra, and the installation uses the same directory paths Keepingthe 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 Postgresmore like INNO than ISAM when it comes to table storage? > Postgres will not like you to cut and pasting individual tables. To keep two Postgres instances on separate machines in sync using an external hard drive you will need to: 1) Make sure you have compatible OS'es. You have that covered already. 2) Maintain compatible Postgres versions. For Postgres up to version 9.6 the versioning scheme is X.X.x where a change in the first two numbers denotes a major version change and a change in the last means a minor version change. You cannot do what you intend(sync binary files) across major versions. Minor versions should not be a problem until they are. By this I mean you should check the Release Notes for any gotchas: https://www.postgresql.org/docs/9.6/static/release.html For Postgres 10(now in development) and up the versioning scheme has been changed to X.x, where a change in the first number is a major change and a change in the second number indicates a minor release change. Checking the Release Notes still applies. 3) You will need to make sure you are copying/syncing the entire Postgres data directory: https://www.postgresql.org/docs/9.6/static/storage-file-layout.html 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? 4) Before you do anything I would use pg_dump/pg_dumpall: https://www.postgresql.org/docs/9.6/static/app-pgdump.html https://www.postgresql.org/docs/9.6/static/app-pg-dumpall.html to make a backup of your data, just in case things do not work out as expected. 5) The above is valid for Steve Atkins suggestion of using the external drive as the sole data directory. The only thing I would be concerned about is that external hard drives I have worked with are not all that fast, you will have to see if that is a problem in your case. -- Adrian Klaver adrian.klaver@aklaver.com
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&d=DwICaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=lQpZV77iEes6tm1L3zsHQm9eceGhQh8UV4IOgzDvapg&s=MpRqOYUdfX_Z4OK9-AFg2b7glEe4lY2aYupB6508ZjQ&e= > > 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? >
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://www.postgresql.org/docs/9.6/static/storage-file-layout.html 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&d=DwICaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=lQpZV77iEes6tm1L3zsHQm9eceGhQh8UV4IOgzDvapg&s=MpRqOYUdfX_Z4OK9-AFg2b7glEe4lY2aYupB6508ZjQ&e= >> >> 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.klaver@aklaver.com
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 thePostgres. Many thanks for your help, which is exceptionally clear and detailed. MM On 6/18/17, 3:03 PM, "Adrian Klaver" <adrian.klaver@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&d=DwICaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=EE03EBpU01pYlVG72ZO4h4D-1JJd--vXBmEvErByUOo&s=ekr93dxabdAgdsxrrTh5gg3pQOUFrX9muS6YFV9SIwM&e= > >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&d=DwICaQ&c=yHlS04HhBraes5BQ9ueu5zKhE7rtNXt_d012z2PA6ws&r=rG8zxOdssqSzDRz4x1GLlmLOW60xyVXydxwnJZpkxbk&m=lQpZV77iEes6tm1L3zsHQm9eceGhQh8UV4IOgzDvapg&s=MpRqOYUdfX_Z4OK9-AFg2b7glEe4lY2aYupB6508ZjQ&e= >>> >>> 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.klaver@aklaver.com
On 06/18/2017 01:13 PM, Martin Mueller wrote: > 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 thePostgres. Not something you have to do right this instant, but I would try to track down how you did the install. It will come in handy when you do an upgrade as the various installers differ in how they do an install. Picking the wrong one when you do the upgrade could make things difficult. > > Many thanks for your help, which is exceptionally clear and detailed. > > MM > -- Adrian Klaver adrian.klaver@aklaver.com