Thread: Setting up Postgres for production web/db work

Setting up Postgres for production web/db work

From
Charles Martin
Date:
We are about to set up a production web/db site with
Apache 1.3.9, PHP 3.0.12, and PostgreSQL 6.5.2.  The
platform is a single-cpu Dell server running FreeBSD
3.4-STABLE.  I have looked but have not seen anything
with recommendations for how to set up Postgres for a
production site.

For example, is:

    % postmaster

the preferred invocation?  Should we be specifying
something about the memory usage, number of processes,
etc?  Is there a FAQ for how to set up cron jobs to do
nightly dumps and etc with maximal efficiency?  Should
we be vaccuuming regularly, and do we need to take the
db offline to do so?  Etc.

Any help on this issue would be greatly appreciated!


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

Re: [GENERAL] Setting up Postgres for production web/db work

From
Ed Loehr
Date:
See 'man postmaster' and 'man postgres' for available startup
options on number of servers, memory usage, etc.  Also, check out
www.postgresql.org under "Info Central"-->"Documentation" for a
host of pretty decent documents answering many of your
current/future questions.  Lots of very useful stuff found at
Deja.com as well (http://www.deja.com/home_ps.shtml)...deja is
archiving *all* pgsql mailing list posts).  Memory usage, number
of servers, etc. really depends on tuning for your
application/hardware, but running with defaults seems to be a
good starting point.

Things I wish I'd known about early on (some found in
documentation, some not):

    * The -F flag (a 2200% performance boost on inserts for me);
    * The -S flag (customizes amount of memory to be used for
sorts);
    * Vacuum is needed nightly at least, more often after many
inserts/deletes;
    * Vacuum can also fix certain showstoppers;
    * Some folks report it cannot be run safely while online with
a load;
    * How to turn timestamped db server logging on...
        http://www.deja.com/getdoc.xp?AN=562128922

Cheers,
Ed Loehr

Charles Martin wrote:

> We are about to set up a production web/db site with
> Apache 1.3.9, PHP 3.0.12, and PostgreSQL 6.5.2.  The
> platform is a single-cpu Dell server running FreeBSD
> 3.4-STABLE.  I have looked but have not seen anything
> with recommendations for how to set up Postgres for a
> production site.
>
> For example, is:
>
>     % postmaster
>
> the preferred invocation?  Should we be specifying
> something about the memory usage, number of processes,
> etc?  Is there a FAQ for how to set up cron jobs to do
> nightly dumps and etc with maximal efficiency?  Should
> we be vaccuuming regularly, and do we need to take the
> db offline to do so?  Etc.
>
> Any help on this issue would be greatly appreciated!