Thread: postgreSQL & amazon ec2 cloud

Sanjay Arora wrote:
> Hello all
>
> Is it possible to host postgreSQL on Amazon's cloud? What are the
> issues involved?

in theory, sure.   anything is possible.

in practice, as I understand it from my relatively superficial reading,
fast storage is fairly expensive and limited in the EC2 compute cloud,
and also not real persistent (if the cloud node goes down, so does its
directly attached storage).. and the bulk S3 storage is high latency and
not really suitable for relational database use.

the most effective use of the S3 and EC2 cloud I've read of is where the
S3 storage is used for bulk persistent storage of backups of things like
large photo libraries,, and the EC2 cloud is used for relatively atomic
computational operations, such as batch scaling multiple image sizes
from large volumes of digital photos.

John,

>> Is it possible to host postgreSQL on Amazon's cloud? What are the issues
>> involved?
>
> in theory, sure.   anything is possible.
>
> in practice, as I understand it from my relatively superficial reading, fast
> storage is fairly expensive and limited in the EC2 compute cloud, and also
> not real persistent

That also was my understanding. But just today a message from AWS
dropped in my inbox:

"....Starting today, you can now launch Amazon EC2 running Windows or
SQL Server instances in the the EU Region,  ...."

So there must be some way to run a relational database with EC2, as
the storage requirements of SQL Server and PostgreSQL are not THAT
different.

Harald











--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
LASIK good, steroids bad?

On Tuesday 03 March 2009 4:41:48 am Sanjay Arora wrote:
> I found today that postgres EnterpriseDB supports Amazon EC2. On a
> shoestring budget EnterpriseDB is just as much an option as Oracle ;-(
>
> So, question is what makes EnterpriseDB more suitable for the cloud than
> plain vanilla postgreSQL?
>
> Anyone?
>
> With best regards.
> Sanjay.
>
>

Nothing. I have created a Postgres instance on an EC2 virtual machine with
attached EBS(Elastic Block Storage). I only got as far as creating in it and
verifying it would run, no benchmarking. EC2 instances have storage as part of
the instance but it is temporary and goes away when the instance is shut down.
For a database you want EBS as it is a virtual harddrive that persists. Should
an EC2 instance go down, you just reattach the EBS drive on reboot. If I
remember correctly there are also some articles at aws.amazon.com about setting
up RAID using EBS drives.



--
Adrian Klaver
aklaver@comcast.net

I don't know exactly, but here here is a paper from Elastra
http://elastra.com/about/2008/03/07/enterprisedb-to-deliver-oltp-database-using-amazon-cloud/


Oleg
On Tue, 3 Mar 2009, Sanjay Arora wrote:

> I found today that postgres EnterpriseDB supports Amazon EC2. On a
> shoestring budget EnterpriseDB is just as much an option as Oracle ;-(
>
> So, question is what makes EnterpriseDB more suitable for the cloud than
> plain vanilla postgreSQL?
>
> Anyone?
>
> With best regards.
> Sanjay.
>
>
> On Tue, Mar 3, 2009 at 3:49 PM, Harald Armin Massa[legacy] <
> haraldarminmassa@gmail.com> wrote:
>
>> John,
>>
>>>> Is it possible to host postgreSQL on Amazon's cloud? What are the issues
>>>> involved?
>>>
>>> in theory, sure.   anything is possible.
>>>
>>> in practice, as I understand it from my relatively superficial reading,
>> fast
>>> storage is fairly expensive and limited in the EC2 compute cloud, and
>> also
>>> not real persistent
>>
>> That also was my understanding. But just today a message from AWS
>> dropped in my inbox:
>>
>> "....Starting today, you can now launch Amazon EC2 running Windows or
>> SQL Server instances in the the EU Region,  ...."
>>
>> So there must be some way to run a relational database with EC2, as
>> the storage requirements of SQL Server and PostgreSQL are not THAT
>> different.
>>
>> Harald
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> GHUM Harald Massa
>> persuadere et programmare
>> Harald Armin Massa
>> Spielberger Stra?e 49
>> 70435 Stuttgart
>> 0173/9409607
>> no fx, no carrier pigeon
>> -
>> LASIK good, steroids bad?
>>
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Adrian Klaver <aklaver@comcast.net> writes:
> Nothing. I have created a Postgres instance on an EC2 virtual machine with
> attached EBS(Elastic Block Storage). I only got as far as creating in it and
> verifying it would run, no benchmarking. EC2 instances have storage as part of
> the instance but it is temporary and goes away when the instance is shut down.
> For a database you want EBS as it is a virtual harddrive that persists. Should
> an EC2 instance go down, you just reattach the EBS drive on reboot.

... I wonder whether you have any guarantees about database consistency
in that situation?  PG has some pretty strong requirements about fsync
behavior etc, and I'd not want to take it on faith that a cloud
environment will meet those requirements.

Performance would be an interesting question too.

            regards, tom lane

On Tuesday 03 March 2009 7:25:17 am Tom Lane wrote:
> Adrian Klaver <aklaver@comcast.net> writes:
> > Nothing. I have created a Postgres instance on an EC2 virtual machine
> > with attached EBS(Elastic Block Storage). I only got as far as creating
> > in it and verifying it would run, no benchmarking. EC2 instances have
> > storage as part of the instance but it is temporary and goes away when
> > the instance is shut down. For a database you want EBS as it is a virtual
> > harddrive that persists. Should an EC2 instance go down, you just
> > reattach the EBS drive on reboot.
>
> ... I wonder whether you have any guarantees about database consistency
> in that situation?  PG has some pretty strong requirements about fsync
> behavior etc, and I'd not want to take it on faith that a cloud
> environment will meet those requirements.
>
> Performance would be an interesting question too.
>
>             regards, tom lane

The EBS starts out as a raw drive. You format it with the file system of your
choice and it gets mounted as a regular drive. From the point of view of the OS
it is a physical hard drive.

As to the cloud environment meeting the requirements I can only go with IBM on
this one- http://aws.amazon.com/solutions/featured-partners/ibm/
To quote:
"In the coming months, AWS will provide pay-as-you-go pricing for the Amazon EC2
running IBM service, enabling you to purchase these services by the hour. These
AMIs will enable you to utilize Amazon EC2 with many of the IBM platform
technologies you’re already familiar with in the cost-effective,
high-performance, reliable, and secure Amazon EC2 environment. The initial list
of IBM AMIs that Amazon EC2 will run include: IBM DB2, IBM Informix, IBM
WebSphere sMash, IBM Lotus Web Content Management, and IBM WebSphere Portal
Server."

Performance remains to be determined.

--
Adrian Klaver
aklaver@comcast.net

On Tue, Mar 03, 2009 at 10:25:17AM -0500, Tom Lane wrote:
> Adrian Klaver <aklaver@comcast.net> writes:
> > Nothing. I have created a Postgres instance on an EC2 virtual machine with
> > attached EBS(Elastic Block Storage). I only got as far as creating in it and
> > verifying it would run, no benchmarking. EC2 instances have storage as part of
> > the instance but it is temporary and goes away when the instance is shut down.
> > For a database you want EBS as it is a virtual harddrive that persists. Should
> > an EC2 instance go down, you just reattach the EBS drive on reboot.
>
> ... I wonder whether you have any guarantees about database consistency
> in that situation?  PG has some pretty strong requirements about fsync
> behavior etc, and I'd not want to take it on faith that a cloud
> environment will meet those requirements.
>
> Performance would be an interesting question too.
>
>             regards, tom lane

There's a place called Engine Yard offering Ruby on Rails hosting with
PostgreSQL on Amazon EC2.

- Josh / eggyknap

On Mar 3, 2009, at 12:39 PM, Joshua Tolley wrote:

> On Tue, Mar 03, 2009 at 10:25:17AM -0500, Tom Lane wrote:
>> Adrian Klaver <aklaver@comcast.net> writes:
>>> Nothing. I have created a Postgres instance on an EC2 virtual
>>> machine with
>>> attached EBS(Elastic Block Storage). I only got as far as creating
>>> in it and
>>> verifying it would run, no benchmarking. EC2 instances have
>>> storage as part of
>>> the instance but it is temporary and goes away when the instance
>>> is shut down.
>>> For a database you want EBS as it is a virtual harddrive that
>>> persists. Should
>>> an EC2 instance go down, you just reattach the EBS drive on reboot.
>>
>> ... I wonder whether you have any guarantees about database
>> consistency
>> in that situation?  PG has some pretty strong requirements about
>> fsync
>> behavior etc, and I'd not want to take it on faith that a cloud
>> environment will meet those requirements.
>>
>> Performance would be an interesting question too.
>>
>>             regards, tom lane
>
> There's a place called Engine Yard offering Ruby on Rails hosting with
> PostgreSQL on Amazon EC2.

Actually, we don't have any EC2 offerings for PostgreSQL yet.  Well,
technically it *is* installed, but it's not pre-configured and, thus,
not supported yet.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k

On Tue, Mar 3, 2009 at 11:53 AM, Sanjay Arora <sanjay.k.arora@gmail.com> wrote:

> Is it possible to host postgreSQL on Amazon's cloud? What are the issues
> involved?

Runs just fine under Ubuntu. We haven't tried it under serious load though.

--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/

Tom Lane wrote:
> Adrian Klaver <aklaver@comcast.net> writes:
>> Nothing. I have created a Postgres instance on an EC2 virtual machine with
>> attached EBS(Elastic Block Storage)..[...]
>
> ... I wonder whether you have any guarantees about database consistency
> in that situation?  PG has some pretty strong requirements about fsync

While I agree it shouldn't be taken on faith, their documentation
does take the time to point out that syncing of I/O's is pretty
expensive operation (even literally - they charge extra for individual
i/o operations and point out that those increase with syncs).
http://aws.amazon.com/ebs/

I have a couple databases there, and based on the rather high I/O-wait
times on their cheapest ($0.10/hr) instances, I'm wildly guessing that
they're doing something reasonable for sync :-).  Their higher priced
instances supposedly have better I/O performance.

> behavior etc, and I'd not want to take it on faith that a cloud
> environment will meet those requirements.

That said, even apart from any SLA of fsync itself, I imagine there's
a concern that an entire hosted cloud might vanish for any number of
reasons.

Sanjay Arora wrote:
>
> Hello all
>
> Is it possible to host postgreSQL on Amazon's cloud? What are the issues
> involved?
>
> With best regards.
> Sanjay.
>
>
You create postgres on EC2 in the same way you would on any Linux server.  I
created one on the Amazon-Fedora AMI about a year and half ago by uploading
the binaries for postgres and compiling. No problems.  I compiled to insure
compatibility with the OS and processor. At the time, all of Amazon's AMIs
were 32 bit even though the processors were 64 bit. I don't know if that has
changed.  You don't need S3 to run postgres, but S3 provides a convenient
storage for backups and S3 is where you store your customized AMIs.

My concerns at the time were the one spindle per processor limitation (since
changed), no permanent IP (since changed) and concerns about losing the
database if the processor or anything else hiccuped.  The last concern has
been addressed with the advent of Elastic Blocks. So we are about to try it
all again and see if it will work for us.

-Cheers

--
View this message in context: http://www.nabble.com/postgreSQL---amazon-ec2-cloud-tp22302502p22626764.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Do you think that it could useful mounting two different EBS to handle
data and pg_xlog ?

cheers,
ste

Hi,
I wanted to reply to an existing thread but it seems a new one has been
created, so I think more details are required...

I want to run my postgres DB on Amazon EC2 using a EBS persistent disk
for postgres installation. In this way data and pg_xlog will be on the
same disk. I was just wondering if also on ec2 with EBS disks having
pg_xlog on a different EBS disk could be useful.

Thanks in advance
ste


Stefano Nichele wrote:
> Do you think that it could useful mounting two different EBS to handle
> data and pg_xlog ?
>
> cheers,
> ste
>

From the (very little) work I've done with EC2, it seems that what you get
logically doesn't have much relation to what you get physically. I don't
recall any guarentee that a virtual disk is equivilent to a physical
spindle in terms of determining performance, or even that your virtual
disk will perform consistently over time. Of course, you should test a few
setups and see how they perform.

On Mon, 20 Apr 2009, Stefano Nichele wrote:

> Hi,
> I wanted to reply to an existing thread but it seems a new one has been
> created, so I think more details are required...
>
> I want to run my postgres DB on Amazon EC2 using a EBS persistent disk for
> postgres installation. In this way data and pg_xlog will be on the same disk.
> I was just wondering if also on ec2 with EBS disks having pg_xlog on a
> different EBS disk could be useful.
>
> Thanks in advance
> ste
>
>
> Stefano Nichele wrote:
>>  Do you think that it could useful mounting two different EBS to handle
>>  data and pg_xlog ?
>>
>>  cheers,
>>  ste
>>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hi Stefano

I'm intrigued with Amazon EC2 and did a little search on it.

From your experience could it be used in similar fashion as a website ->
running a Postgresql database and having users access the database through
an interface?

If so, would the users need a Java environment installed on their machines
or would the interface connect directley to Postgresql - like a website??

Bob


----- Original Message -----
From: "Stefano Nichele" <stefano.nichele@gmail.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, April 20, 2009 9:26 AM
Subject: Re: [GENERAL] postgreSQL & amazon ec2 cloud


> Hi,
> I wanted to reply to an existing thread but it seems a new one has been
> created, so I think more details are required...
>
> I want to run my postgres DB on Amazon EC2 using a EBS persistent disk for
> postgres installation. In this way data and pg_xlog will be on the same
> disk. I was just wondering if also on ec2 with EBS disks having pg_xlog on
> a different EBS disk could be useful.
>
> Thanks in advance
> ste
>
>
> Stefano Nichele wrote:
>> Do you think that it could useful mounting two different EBS to handle
>> data and pg_xlog ?
>>
>> cheers,
>> ste
>>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Bob Pawley wrote:
> From your experience could it be used in similar fashion as a website
> -> running a Postgresql database and having users access the database
> through an interface?
>
> If so, would the users need a Java environment installed on their
> machines ...

um, the users would need a Java environment if they were running Java
applications.


> or would the interface connect directley to Postgresql - like a website??

what 'interface' are you referring to ?  a client application can
connect to a postgres server with the postgres protocol, by default over
port 5432/tcp...  a client application would do this using libpq, or a
native interface like jdbc (if the client is java)

I'll run a java webapp running in tomcat connected to postgres via jdbc.
BTW, why the access method should be important  ?
I mean, my main question is .... should pg_xlog be located on a
different EBS than data ?

My doubt is really about logical vs physical disk, since i think EBS is
logical and two EBS disks could be run on the same physical disk.....so
why to separate data and pg_xlog ? Is it still useful ? It 's like to
put data and pg_xlog on two partitions of the same disk.

Cheers,
ste


John R Pierce wrote:
> Bob Pawley wrote:
>> From your experience could it be used in similar fashion as a website
>> -> running a Postgresql database and having users access the database
>> through an interface?
>>
>> If so, would the users need a Java environment installed on their
>> machines ...
>
> um, the users would need a Java environment if they were running Java
> applications.
>
>
>> or would the interface connect directley to Postgresql - like a
>> website??
>
> what 'interface' are you referring to ?  a client application can
> connect to a postgres server with the postgres protocol, by default
> over port 5432/tcp...  a client application would do this using libpq,
> or a native interface like jdbc (if the client is java)
>
>
>

On Mon, Apr 20, 2009 at 02:14:00PM +0200, Stefano Nichele wrote:
> Do you think that it could useful mounting two different EBS to handle
> data and pg_xlog ?

Testing I've participated in suggests that it helps to split pg_xlog
elsewhere. Your mileage may vary.

- Josh / eggyknap