Re: [GENERAL] effective_io_concurrency increasing

[David G. Johnston]

On Sun, Jun 18, 2017 at 6:02 PM, Patrick B  wrote:

> Hi guys.
>
> I just wanna understand the effective_io_concurrency value better.
>
> My current Master database server has 16 vCPUS and I use
> ​​
>  effective_io_concurrency = 0.
>

​It seems as though the number of virtual CPUs little to no bearing on
whether, or to what value, you should set this parameter.  Obviously with
only one CPU parallelism wouldn't be possible (I'm assuming a single query
does not make multiple parallel requests for data) but the value seems to
strictly describe a characteristic the I/O subsystem.  Whether you can
fully leverage a properly set large value is another matter.

​As general advice, even you are using a soon to be obsolete (or any
non-current really) version of PostgreSQL when you are learning about a new
concept checking the most recent docs can be helpful.  Generally only bugs
in the docs get back-patched but a number of doc contributions are not bug
related but helpful none-the-less.

https://www.postgresql.org/docs/devel/static/runtime-config-resource.html​

​In short, if you want any good advice you will need to figure out the
specifics of your I/O subsystem (non-volatile memory and any associated
hardware), and share that with the list.​ Lacking rules-of-thumb learning
how to test your system and measure changes would help get you to the end
goal.  Sadly not a skill I've really picked up as of yet.

​David J.​

Re: [GENERAL] effective_io_concurrency increasing

[Melvin Davidson]

*As per the docs:*



*1. This is dependent on whether or not you are using a RAID disk,2. "Some
experimentation may be needed to find the best value"*

*IOW, there is no general recommendation.*

On Sun, Jun 18, 2017 at 9:24 PM, Patrick B  wrote:

>
>
> 2017-06-19 13:19 GMT+12:00 Melvin Davidson :
>
>>
>>
>> On Sun, Jun 18, 2017 at 9:02 PM, Patrick B 
>> wrote:
>>
>>> Hi guys.
>>>
>>> I just wanna understand the effective_io_concurrency value better.
>>>
>>> My current Master database server has 16 vCPUS and I
>>> use effective_io_concurrency = 0.
>>>
>>> What can be the benefits of increasing that number? Also, do you guys
>>> have any recommendations?
>>>
>>> I'm using PG 9.2 and the official doc does not say much about which
>>> value you should use.
>>>
>>> If I put it to 1, does it mean I can have a query spread into 1
>>> processor?
>>>
>>> Thanks
>>> P
>>>
>>
>>
>>
>> *Perhaps you should read the
>> doc.https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
>> *
>> 18.4.6. Asynchronous Behavior *effective_io_concurrency (integer)*
>>
>
>
> I've done that! But I'm looking for some personal experiences and
> suggestions!!
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] effective_io_concurrency increasing

[Patrick B]

2017-06-19 13:19 GMT+12:00 Melvin Davidson :

>
>
> On Sun, Jun 18, 2017 at 9:02 PM, Patrick B 
> wrote:
>
>> Hi guys.
>>
>> I just wanna understand the effective_io_concurrency value better.
>>
>> My current Master database server has 16 vCPUS and I
>> use effective_io_concurrency = 0.
>>
>> What can be the benefits of increasing that number? Also, do you guys
>> have any recommendations?
>>
>> I'm using PG 9.2 and the official doc does not say much about which value
>> you should use.
>>
>> If I put it to 1, does it mean I can have a query spread into 1 processor?
>>
>> Thanks
>> P
>>
>
>
>
> *Perhaps you should read the
> doc.https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
> *
> 18.4.6. Asynchronous Behavior *effective_io_concurrency (integer)*
>


I've done that! But I'm looking for some personal experiences and
suggestions!!

Re: [GENERAL] effective_io_concurrency increasing

[Melvin Davidson]

On Sun, Jun 18, 2017 at 9:02 PM, Patrick B  wrote:

> Hi guys.
>
> I just wanna understand the effective_io_concurrency value better.
>
> My current Master database server has 16 vCPUS and I
> use effective_io_concurrency = 0.
>
> What can be the benefits of increasing that number? Also, do you guys have
> any recommendations?
>
> I'm using PG 9.2 and the official doc does not say much about which value
> you should use.
>
> If I put it to 1, does it mean I can have a query spread into 1 processor?
>
> Thanks
> P
>



*Perhaps you should read the
doc.https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
*
18.4.6. Asynchronous Behavior *effective_io_concurrency (integer)*
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] effective_io_concurrency increasing

[Patrick B]

Hi guys.

I just wanna understand the effective_io_concurrency value better.

My current Master database server has 16 vCPUS and I
use effective_io_concurrency = 0.

What can be the benefits of increasing that number? Also, do you guys have
any recommendations?

I'm using PG 9.2 and the official doc does not say much about which value
you should use.

If I put it to 1, does it mean I can have a query spread into 1 processor?

Thanks
P

Re: [GENERAL] storing postgres data on dropbox

[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

[GENERAL] Postgres Data Encryption Using LUKS with dm-crypt ?

[Condor]

Hello ppl,

a few years ago I asked the same question but did not receive valued 
answers and we use different way to realize the project.
Today I wanna ask did some one do it and most important for me, can some 
one share his experience ?

What I should expect, what is good and bad things that can be happened.

Im thinking the problems can be occurred if server is restarted and data 
is not synced, but for that is raid cache battery.
Also if hard drive need to be checked for bad clusters or broken index / 
files on filesystem what will happened with data?
Because postgresql does not support data level encryption, Im wanna 
realize with third party tools.



Regards,
Hristo S


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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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

[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