Thread: First production install - general advice
I'm going to go live with my first production install of PostgreSQL 9.0 in about a week. I've done a LOT of reading on the internet and I've purchased two very good reference books and actually read them:
PostgreSQL 9 Administration Cookbook
PostgreSQL 9.0 High Performance
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. Maybe your directory naming scheme, your backup strategy, environment variable settings etc. In this last week before we go live I'm hoping to get a few last minute tidbits of information that me help me avoid some common problems.
Thank you,
Rick
PostgreSQL 9 Administration Cookbook
PostgreSQL 9.0 High Performance
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. Maybe your directory naming scheme, your backup strategy, environment variable settings etc. In this last week before we go live I'm hoping to get a few last minute tidbits of information that me help me avoid some common problems.
Thank you,
Rick
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 youwish 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.
One thing that comes to mind... Have you tested the install process from start to end?
Other than that, a week until to go live is a is time to relax, exhale, prop your feet on the desk, and visualize the process thinking of every step, automated and manual, what could happen here and is a resolution in place to get past it. If there is not a resolution and if it is not an easy one to work up, write it down and do it manually, if the issue arises. At this stage of the project, if all the work is done, tested and accepted, there is not really much else that you can do other than relax.
To: pgsql-general@postgresql.org
Subject: [GENERAL] First production install - general advice
Date: Mon, 7 Mar 2011 12:34:19 -0500
From: runner@winning.com
Other than that, a week until to go live is a is time to relax, exhale, prop your feet on the desk, and visualize the process thinking of every step, automated and manual, what could happen here and is a resolution in place to get past it. If there is not a resolution and if it is not an easy one to work up, write it down and do it manually, if the issue arises. At this stage of the project, if all the work is done, tested and accepted, there is not really much else that you can do other than relax.
To: pgsql-general@postgresql.org
Subject: [GENERAL] First production install - general advice
Date: Mon, 7 Mar 2011 12:34:19 -0500
From: runner@winning.com
I'm going to go live with my first production install of PostgreSQL 9.0 in about a week. I've done a LOT of reading on the internet and I've purchased two very good reference books and actually read them:
PostgreSQL 9 Administration Cookbook
PostgreSQL 9.0 High Performance
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. Maybe your directory naming scheme, your backup strategy, environment variable settings etc. In this last week before we go live I'm hoping to get a few last minute tidbits of information that me help me avoid some common problems.
Thank you,
Rick
PostgreSQL 9 Administration Cookbook
PostgreSQL 9.0 High Performance
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. Maybe your directory naming scheme, your backup strategy, environment variable settings etc. In this last week before we go live I'm hoping to get a few last minute tidbits of information that me help me avoid some common problems.
Thank you,
Rick
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Ray Stell
> Sent: Monday, March 07, 2011 10:45 AM
> To: runner
> Cc: pgsql-general@postgresql.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