Re: basic questions: Postgres with yum on CentOS 5.1

On Mon, Dec 31, 2007 at 02:39:02PM -0800, Chuck wrote:
> yum -y install postgre postgre-server postgre-devel

I assume that's all spelled "postgres". But otherwise, ok.

> Upgrade to PostgreSQL 8.2.5?
> Before I start creating databases, it seems like I should up consider
> upgrading to PostgreSQL 8.2.5. I do not know of specific features I
> need in version 8.2.5, I just thought I should be using the latest
> stable version. Is this a good idea or should I just use version
> 8.1.9? If 8.2.5 is recommended, could someone suggest the best
> approach for upgrading? If this isn't fairly quick and easy, I may
> need to do it later.

I suspect your web hosting company will not install the non-packaged
binaries for the OS and distribution you're using. In such a case, you're
stuck with whatever yum installs. If they will install any RPM set, then
you can just download the RPMs provided by the project, which are IIRC
source RPMs intended to be built on the target OS.

One thing that is important to note is that usually major version upgrades
require a dump-and-restore. That means a database outage. You'll want to
factor that into what you're planning for.

8.3 is about to be released, BTW, which sort of means 8.1 is looking longer
in the tooth every day. But there's no reason to suppose 8.1 support will
stop any time soon.

> Questions:
> 1. Using root, should I change the password for the postgre user with
> 'passwd postgre' (so that I know the password)? Do I need to know the
> password?

It's entirely possible that you don't need postgres even to have a login
shell -- the OS users and the PostgreSQL users are not strictly dependent on
one another, unless you're using "ident" authentication. You also should
probably not do most database operations as superuser anyway.

> 2. Do I need to manually create the data directory with postgre as
> the owner before running initdb? Such as:

Unlikely, since usually your package manager did this for you.

> 3. To ensure that the databases I create use UTF-8 do I merely use
> the '-E UTF8' option with initdb? Do I need to be concerned with

Yes (and see below). But be careful: you cannot change the encoding without
doing initdb again, note. Be especially aware of the interaction between
encoding and locale

> LANG=
> LC_CTYPE="POSIX"

This (and the rest of it) is what you want when you do that initdb.

A