Re: First production install - general advice

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Ray Stell
> Sent: Monday, March 07, 2011 10:45 AM
> To: runner
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] First production install - general advice
>
> On Mon, Mar 07, 2011 at 12:34:19PM -0500, runner wrote:
> >
> > I'd like to know if any of you have ever installed a PostgreSQL
> database for production use and then found something you wish you had
> done differently after the fact.
>
>
> Test and document your disaster recovery plan. You don't want be
> trying
> to figure it out when you need it. It is what gets left in the scurry
> very often. I'm pretty sure mine is dusty and I regret that.
>

DR is a critical one to have worked out and well tested in advance of any
production deployment.

I would add->
Explicitly setting the encoding, lc_ctype, and lc_collate when running
initdb. (less of an issue now with 9 than it was in the 8.{1,2,3} days,
since different encodings, ctypes and collate settings can at least
cohabitate in the same pg instance now)

Was a non-issue for us until some version/update of redhat when suddenly,
since we didn't specify what lc_ctype and lc_collate were supposed to be, we
foot-gunned out selves on creating a new database.... that took me a while
to figure out that that was the cause of some "slow" queries that were not
(as) slow on other boxes.

Obviously some people want to use settings for ctype and collate other than
straight C/POSIX. But it can be kind of shock when you expected them to be C
and they are not... at least it was for me so make sure it explicitly gets
set to "whatever" you want, not just letting some defaults go in.

Maybe that is just me.::shrug::

I will assume since you have the (great -IMO) high performance book by Greg
Smith you have done things around setting the xlog directory to a different
set of spindles, set readahead, using a more modern file system than ext3,
setting the IO scheduler , using a raid card with a battery backed write
cache, max semaphores, ...etc tuning the config parameters in
postgresql.conf ...etc...etc...etc. Using a connection pooler from the
start. Something to monitor server trends over time like cacti, something to
page (someone) when things go bad (nagios). And benchmark/profile as much as
possibile to compare to down the road. Something like are just good sys
admin things to have ... like say a tested and working lights out management

I am rambling but yeah ... those are all things that came to mind.

\mark