Re: [GENERAL] storing postgres data on dropbox

2017-06-18 Thread Adrian Klaver 

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 the Postgres.


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.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

2017-06-18 Thread Martin Mueller 
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"  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

2017-06-18 Thread Adrian Klaver 

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=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

2017-06-18 Thread Martin Mueller 
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

2017-06-18 Thread Adrian Klaver 

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 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?



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.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

2017-06-18 Thread Karsten Hilbert 
On Sun, Jun 18, 2017 at 06:29:50PM +, 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


-- 
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

2017-06-18 Thread Karsten Hilbert 
On Sun, Jun 18, 2017 at 06:29:50PM +, 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


-- 
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

2017-06-18 Thread Martin Mueller 
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"  wrote:

>On Sun, Jun 18, 2017 at 05:30:44PM +, 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

2017-06-18 Thread Steve Atkins 

> On Jun 18, 2017, at 10:58 AM, Karsten Hilbert  wrote:
> 
> On Sun, Jun 18, 2017 at 05:30:44PM +, 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



-- 
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

2017-06-18 Thread Karsten Hilbert 
On Sun, Jun 18, 2017 at 05:30:44PM +, 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:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] storing postgres data on dropbox

2017-06-18 Thread Martin Mueller 
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"  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

2017-06-18 Thread Bruno Wolff III 

On Sun, Jun 18, 2017 at 13:16:16 +,
 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.


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.



--
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

2017-06-18 Thread Adrian Klaver 

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

2017-06-18 Thread Martin Mueller 
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"  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

Re: [GENERAL] storing postgres data on dropbox

2017-06-18 Thread Andreas Kretschmer 



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:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] storing postgres data on dropbox

2017-06-17 Thread 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?

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