postgresql on centos / redhat (again)

18Apr09

do to my chronic fiddling i had to reinstall postgresql from scratch.  here’s what i did.  i wrote this after the fact, so hopefully i haven’t forgotten anything and it’s of some use.

1.  install the postgresql packages via yum or the redhat gui.  you may want to add some additional packages for a procedural language or whatever your situation dictates.

2.  make the directory and set up db.

root# mkdir /usr/local/pgsql/data
root# chown postgres /usr/local/pgsql/data
root# su postgres
postgres$ initdb -D /usr/local/pgsql/data

3.  i’m not really into the current default setup so we’re going to change it so everybody has to use a password.

i started the db using the nifty services gui but there’s other ways such as, postmaster -D /usr/local/pgsql/data.

open the interactive shell, psql, and give superuser a password.

alter role postgres with password ‘your_pass_here’;

see more info here:
http://www.postgresql.org/docs/8.3/static/app-psql.html
http://www.postgresql.org/docs/current/static/sql-alterrole.html

now stop the db, again i used the services gui.

4.  now we will change the default authentication.  go to /var/lib/pgsql/data and open pg_hba.conf with vi.  in the column method change the current value to md5.

for more info see:
http://en.wikipedia.org/wiki/Vi
http://wiki.postgresql.org/wiki/Client_Authentication

start the db.

5.  let’s test it out to make sure we haven’t screwed anything up.  su to root.  type ‘psql -U postgres’ without quotes at the prompt.  it should ask you for the newly created password.  this is a good thing.

6.  we really don’t want to do everything as the superuser so lets create a limited user.  in this case i’m doing it for a drupal install.  i want him to be able to create databases but not users.

create user drupal0 with password ‘pass_here’ createdb;

to view users type ‘\du’.

i’ve learned a lot at my current job about using oracle’s pl/sql.  feel really comfortable writing functions and procedures and i’m looking forward to playing with postgresql’s procedural languages.  my next post will probably be about how many different ways i can not get drupal to work.  ;)

Filed under: Linux   |  1 Comment
Tags: centos, install, Linux, postgresql, RedHat