Re: storing postgres data on dropbox

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.klaver@aklaver.com