Обсуждение: Probably been asked a hundred times before.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Which disto is best for running a Postgres server?
I just installed OpenSuse and downloaded and compiled the latest version
of Postgres.  It isn't that big of a hassle but I noticed that almost
none of the big distros keep all that up to date with Postgres as far as
what they have in their repositories.

I was wondering if anybody has made an Postgres centric distro?  Just a
nice stripped down server Distro that is ideal for running a database
server on?

More just curious since I already got mine installed and compiled.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3-nr1 (Windows XP)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iQEVAwUBSGEE9uLnn4qAcbUGAQJpkQf/VkeYeG9aCklcybj6qFPAgNRxT7foIiqt
sbjQI/Geik1qDX3WwD7o9bbPR7V8VmZqcY8JhXjsxD8ggNA9qi92YI5Sx/l7Nmaq
yhCPUPZfaexyGynI5DgUxx7glgZP4iPZfYcbjoy0nyaZPLXXDR11i3q8CXvdPhoE
oTGQBT07CijtGPN6y4h2ymlgePUQKoN0NPT9JQ5KQOc8PECpqJsFDUuIon0BtbeN
S+TB1lwAgiceINQBys6wYpw1epbYb3LV2zuN6GjEx41SQQO+8vF1tBpJTvkXBHZI
G3j457pL4NaRgbZtGC0aAz90G2QFFV1MJ9ocMX4Im6HYL2SYOBbDyQ==
=n84g
-----END PGP SIGNATURE-----

On Tuesday 24 June 2008 11:30:14 David Siebert wrote:

> Which disto is best for running a Postgres server?

> I just installed OpenSuse and downloaded and compiled the latest version

> of Postgres. It isn't that big of a hassle but I noticed that almost

> none of the big distros keep all that up to date with Postgres as far as

> what they have in their repositories.

>

> I was wondering if anybody has made an Postgres centric distro? Just a

> nice stripped down server Distro that is ideal for running a database

> server on?

>

> More just curious since I already got mine installed and compiled.

I'm running OpenSuSE 11.0 and I have PostgreSQL 8.3.1 right from the installation DVD.

I know it isn't 8.3.3, but they couldn't package and test everything until the release date.

There are packagers for Fedora here... But I would rather compile PG myself than switch to Fedora just because of a PostgreSQL package.

Generating new RPMs / updating existing ones isn't so hard.

--

Jorge Godoy <jgodoy@gmail.com>

On Tue, Jun 24, 2008 at 8:30 AM, David Siebert <david@eclipsecat.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Which disto is best for running a Postgres server?

That is the subject of many a holy flame war.  FreeBSD 7.0 seems to
currently be regarded as being one of the top performers.

I use ubuntu because it's easier to maintain and it provides very good
performance

> I just installed OpenSuse and downloaded and compiled the latest version
> of Postgres.  It isn't that big of a hassle but I noticed that almost
> none of the big distros keep all that up to date with Postgres as far as
> what they have in their repositories.

Ubuntu does, and there are the PGDG rpms on the postgresql website
that work on RHEL / Centos / Whitebox linux quite nicely.

> I was wondering if anybody has made an Postgres centric distro?  Just a
> nice stripped down server Distro that is ideal for running a database
> server on?

There's been a few pg_live CDs floating around.  They're more of a
demo / super easy setup type of thing, not intended for production
use.  But they are very handy for trying out pgsql.

Jorge Godoy <jgodoy@gmail.com> writes:
> On Tuesday 24 June 2008 11:30:14 David Siebert wrote:
>> I was wondering if anybody has made an Postgres centric distro?

> I'm running OpenSuSE 11.0 and I have PostgreSQL 8.3.1 right from the
> installation DVD.

Fedora 9 likewise shipped with PG 8.3.1.  It's all a matter of what was
current when a particular major distro release was frozen.

Some distros are more aggressive than others about updating to new minor
PG releases, but there is no one who will auto-update you to a new major
PG release.  Lack of upgrade-in-place is part of the reason; but even
if we had that, few distros would consider it because of the application
compatibility issues that new major releases typically create.

            regards, tom lane

At 2:12p -0400 on Tue, 24 Jun 2008, Tom Lane wrote:
> Jorge Godoy <jgodoy@gmail.com> writes:
>> On Tuesday 24 June 2008 11:30:14 David Siebert wrote:
>>> I was wondering if anybody has made an Postgres centric distro?
>
>> I'm running OpenSuSE 11.0 and I have PostgreSQL 8.3.1 right from the
>> installation DVD.
>
> Fedora 9 likewise shipped with PG 8.3.1.  It's all a matter of what was
> current when a particular major distro release was frozen.

Isn't there the Postgres Live CD?  I don't suppose that "distro" is at
all meant to be liberated or installed?

Other than that, what're your criterion for "best"?

Short of a response, I've read a number of reports that given some
tuning FreeBSD 7.0 is the current top performer.

Kevin

On Tue, 24 Jun 2008, Kevin Hunter wrote:

> Short of a response, I've read a number of reports that given some
> tuning FreeBSD 7.0 is the current top performer.

Those reports are all not quite right and I'm trying to get time to fully
debunk them in PostgreSQL land.

First off, they were running a small read-only benchmark, which is not
representative at all of real database performance.  The FreeBSD team was
looking for something that stressed database kernel operations, and never
intended this to be a true database comparison.

Second, there was a problem with the new Linux CFS scheduler running
sysbench at the time the FreeBSD 7.0 reports touting its superiority were
released.  It's since been fixed;
http://www.kernel.org/pub/linux/kernel/people/npiggin/sysbench/ shows you
the after and links to the before when using MySQL.  PostgreSQL sysbench
results also benefitted, I haven't seen someone do a new set of benchmarks
there yet.

Third, there was also a bad interaction between the kernel and the
malloc/free sections of glibc that really impacted results here.  The
FreeBSD 7.0 results had a specific fix in this area for their kernel.
Shortly afterward, a similar one was merged into Linux:
http://lkml.org/lkml/2007/4/1/268 (that fix was also active at the point
the previous benchmarks I pointed to were done)

So, yes, there was a brief window where the new FreeBSD 7.0 had a
performance advantage over Linux on this artificial (and bad) benchmark
running both its old scheduler and the still buggy and new CFS one, but
the two issues responsible have been resolved and current Linux kernels
using CFS are back to being on top again.  The exact performance you'll
get depends on which Linux distribution/kernel combination you use, but
it's just not true that FreeBSD has an unambiguous lead here.  The minute
the FreeBSD team declared that these benchmark results were somehow
interesting, it was simple for the Linux team to blow right by them by
optimizing for the weird things sysbench does the same way.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--On Tuesday, June 24, 2008 10:30:14 AM -0400 David Siebert
<david@eclipsecat.com> wrote:


> Which disto is best for running a Postgres server?

I run most of my postgres servers on Debian. I really love it, because
once a new major version comes out you can very easy install it
parallel to your current version and test and once you are done,
migrate the data and just switch the port.

I also have some external servers with RedHat ES and postgres. But when
I upgrade there I use the RPMs provided by postgres. Major version
upgrades are a bit more tricky here, but they work.


[ Clemens Schwaighofer                      -----=====:::::~ ]
[ IT Engineer/Manager, TEQUILA\ Japan IT Group               ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.co.jp                                   ]

On 2008-06-24 16:30, David Siebert wrote:
> Which disto is best for running a Postgres server?

I'd go for CentOS 5.2 (or better RedHat Enterprise Linux 5.2, if you can
afford it, as $349/year for basic support can save you several hours of
problem solving).

But by default CentOS5/RHEL5 have PostgreSQL 8.1, so I'd configure an
official Postgresql 8.3 yum repository:
http://yum.pgsqlrpms.org/howtoyum.php

I'd get:

- automatic OS bug fixes up until Sep 30, 2010;

- automatic OS security updates and mission critical bug fixes up until
Mar 31, 2014;

- automatic PostgreSQL security and bug fixes while 8.3 branch is supported;

- additional OS security protection from SElinux;

- Tom Lane, the most active developer, works for RedHat ;-)

> It isn't that big of a hassle but I noticed that almost none of the
> big distros keep all that up to date with Postgres as far as what
> they have in their repositories.

It is not possible to keep up for enterprise grade distros. A major
PostgreSQL version is released every year, compared to 2-3 years between
enterprise grade major distro release. A distro can not do a major
upgrade of PostgreSQL as its major versions data storage formats are not
compatible.

I'd stay away from not enterprise grade distros like Fedora, as a 1 year
support lifetime is much too low. It is good for your home computer but
not for a server.

Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
                                                      Winnie the Pooh

On Tue, 24 Jun 2008, David Siebert wrote:

> Which disto is best for running a Postgres server?

You didn't define what best means for you.

If you want to always want to stay current with new releases, the
RedHat/Fedora packages available at http://www.postgresql.org/download are
on average the most up to date.  I personally avoid Fedora because the
support lifetime is so short, and on non-production servers I'll use
CentOS instead of the official RedHat.

If you want something popular so that you will likely be able to find
support help if you run into issues, again a RHEL/Fedora system is good
for that, with Ubuntu being another increasingly mainstream choice.

Should running multiple databases instances at once, having easy scripts
to upgrade between versions, and being able to easily install additional
software be important goals, a Debian or Ubuntu system has some nice
features.  The main thing to watch for is that the Ubuntu desktop system
is optimized a bit oddly for database use.

If you'd like a more stable system with powerful filesystem and
OS-debugging tools, and don't mind having a less popular system with less
open-source gadgets tacked on, consider Solaris or FreeBSD.

If performance is your priority, what will work best really depends on
what hardware you intend to deploy on.  It's possible to get a good
high-performance setup out of any of these, it's just a matter of matching
the appropriate supported hardware.  There are some weird issues with
really recent Linux kernels and PostgreSQL so you need to be careful
there.  I've put some suggestions about what works well and badly for me
at
http://notemagnet.blogspot.com/2008/05/pgbench-suffering-with-linux-2623-2626.html
you might find interesting.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Well I am kind of stuck using OpenSuse. Not a bad distro and is the one
we use in our office for production work.
I like CentOS myself for database work and tend to use that for test
systems here since I manage them myself.
I was more wondering if someone had made a Postgres centric distro yet.
Sort of FreeNAS, OpenFiler, or what ever the Asterisk distro is called
these days.
Seems like you could build a nice little distro that was database
centric. Maybe use FreeBSD, Solaris, or Centos as the base.
Sort of a plug and play solution.
I do wonder just how well Solaris plus ZFS would work for a Postgres server.
I am lucky that my database is only several hundred thousands records
and only has a few dozen users hitting it.
It ran for the longest time on just a 400 MHZ PII and is now running
under CentOS on a whopping 600Mhz PIII with all of 256 mb of ram.
It is going to finally move to a real server with dual Xeons and a gig
of ram. That should keep it happy for a decade or two.  Did I mention
that I love the performance of Postgres and Linux?


Greg Smith wrote:
> On Tue, 24 Jun 2008, David Siebert wrote:
>
>> Which disto is best for running a Postgres server?
>
> You didn't define what best means for you.
>
> If you want to always want to stay current with new releases, the
> RedHat/Fedora packages available at http://www.postgresql.org/download
> are on average the most up to date.  I personally avoid Fedora because
> the support lifetime is so short, and on non-production servers I'll use
> CentOS instead of the official RedHat.
>
> If you want something popular so that you will likely be able to find
> support help if you run into issues, again a RHEL/Fedora system is good
> for that, with Ubuntu being another increasingly mainstream choice.
>
> Should running multiple databases instances at once, having easy scripts
> to upgrade between versions, and being able to easily install additional
> software be important goals, a Debian or Ubuntu system has some nice
> features.  The main thing to watch for is that the Ubuntu desktop system
> is optimized a bit oddly for database use.
>
> If you'd like a more stable system with powerful filesystem and
> OS-debugging tools, and don't mind having a less popular system with
> less open-source gadgets tacked on, consider Solaris or FreeBSD.
>
> If performance is your priority, what will work best really depends on
> what hardware you intend to deploy on.  It's possible to get a good
> high-performance setup out of any of these, it's just a matter of
> matching the appropriate supported hardware.  There are some weird
> issues with really recent Linux kernels and PostgreSQL so you need to be
> careful there.  I've put some suggestions about what works well and
> badly for me at
> http://notemagnet.blogspot.com/2008/05/pgbench-suffering-with-linux-2623-2626.html
> you might find interesting.
>
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>

david@eclipsecat.com (David Siebert) writes:
> Well I am kind of stuck using OpenSuse. Not a bad distro and is the one
> we use in our office for production work.
> I like CentOS myself for database work and tend to use that for test
> systems here since I manage them myself.
> I was more wondering if someone had made a Postgres centric distro yet.
> Sort of FreeNAS, OpenFiler, or what ever the Asterisk distro is called
> these days.
> Seems like you could build a nice little distro that was database
> centric. Maybe use FreeBSD, Solaris, or Centos as the base.
> Sort of a plug and play solution.

A "pretty minimalist" approach would be...

- Install Debian base (~20MB of "install")

- Figure out packages needed for PostgreSQL
  PKGS="postgresql-client-8.3 postgresql-8.3"

- Libraries, and such
  PKGS="${PKGS} libpq5 libdbd-pg-perl"

- Some tools
  PKGS="${PKGS} pgadmin3 pgadmin3-data"

- Some useful 3rd party bits
  PKGS="${PKGS} cfengine2 ntp ssh vim"

Then install that...

$ apt-get install ${PKGS}

That's going to draw in some dependancies, but is still quite, quite
minimal, moreso than anything that wasn't *expressly* customized for
the purpose.  That will, for instance, be *way* smaller than Centos.

You could do much the same using ports/openpkg on FreeBSD.
--
output = reverse("gro.mca" "@" "enworbbc")
http://cbbrowne.com/info/finances.html
"Computers are  like air conditioners:  They stop working  properly if
you open windows."

At 10:30 PM 6/24/2008, David Siebert wrote:
>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>Which disto is best for running a Postgres server?
>I just installed OpenSuse and downloaded and compiled the latest version
>of Postgres.  It isn't that big of a hassle but I noticed that almost
>none of the big distros keep all that up to date with Postgres as far as
>what they have in their repositories.
>
>I was wondering if anybody has made an Postgres centric distro?  Just a
>nice stripped down server Distro that is ideal for running a database
>server on?

To me an ideal server distro to use would be one which:
1) will run a reasonably recent postgresql release
2) is stable, reliable and hopefully doesn't have painful bugs.
3) performs reasonably well
4) has reasonable hardware support
5) can monitor the required hardware and respond appropriately

Stripped down distros often do not do 5), and 5) is quite important to me.

For example:
I'd want to know if any of the drives in the RAID that my database
sits on is having problems (and to trigger an email alert)
I'd also would like the O/S to be able to know if the UPSes the
server runs off are on battery and start an automatic shutdown at the
appropriate time.

Rather than waiting for the battery to be low (which appears to be
the default for NUTS), I personally prefer my servers to shutdown
much earlier. If the generators have not started within a minute I'd
assume they are not going to start. One reason is because if
someone/something restarts the server and the system is still on
battery or goes back on battery again, the batteries will be more
likely to have enough charge for another boot up and shutdown cycle.
Not sure if more recent versions of NUTS can do what I want without
modification.

I had some problems with NUTS on opensuse 10.3 - some
filesystem/device permission problems and also hald-addon-hid-ups had
a mem leak, and over time tried to use up all mem (not good for the
O/S to run out of mem and kill the DB server). I have not tried version 11.

Regards,
Link.

Lincoln Yeoh wrote:
> At 10:30 PM 6/24/2008, David Siebert wrote:
>> Which disto is best for running a Postgres server?

Just to add one more slightly different philosophy.

For servers I manage, I run the most conservative
and slow changing distros that only update security
releases (Debian Stable, RHEL are good choices; no
doubt Solaris would be too;  Ubuntu updates too
frequently for my tastes).  For the components less
core to our business (ssh, munin, etc) we trust the
distro provider to provide security updates and to do
the very minimum of other changes that might have
compatibility issues.

For the components that are more core to our
business, though, we get the source from the projects
themselves (like postgresql.org) and compile from
source.   This gives us the advantages of being
totally in control of when updates occur, and of
having developers be able to attach debuggers if
need be.

I'm using Ubuntu for my development server. The live update updated
postgres either the day of or the day after 8.3.3 came out.  Can't
complain about that.

Artacus