Install Postgres 8.4 on a RHEL 5.5 Derivative

Continuing with the series of 'basics' type posts, I'll cover getting Postgres up and running on Scientific Linux. A little something on the motivation for these posts... it's not because I've found something unique or amazing, it's more a reminder for myself on how to do some things. Which allows me to replace all this info in my memory with a simple pointer to the info :). The other motivation is to consolidate information... I ran into a few self-inflicted issues when doing this first time around, and found there were many guides out there for specific pieces but not an overall one that covered everything I wanted to do. So read on if you're interested!

Preparation
The Postgres 8.4 EPEL package uses a 'home' of /var/lib/pgsql by default - you might want to mount a volume on that before yum installing. If you hot add a vmdk, you can do a rescan with

# echo "- - -" > /sys/class/scsi_host/host0/scan

Then do the necessary volume operations.

1. Install Postgres 8.4

# yum install postgresql84-server.x86_64

2. Assuming a successful install, chk out /etc/rc.d/init.d/postgresql - look for the key startup variables which are:

#Set defaults for configuration variables
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/var/lib/pgsql/data
PGLOG=/var/lib/pgsql/pgstartup.log

For the sake of this post, we'll just use the defaults.

4. You can now initialise the database server. Doing this as root will fail - you must run it as the postgres user

# su - postgres
$ initdb

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

postgres -D /var/lib/pgsql/data
or
pg_ctl -D /var/lib/pgsql/data -l logfile start

$ exit
logout

5. Now start the service

# service postgresql start

6. We need to change the postgres admin user password (ie not the Linux postgres user password). su to the “postgres” Linux user, execute the following commands.

# su - postgres
$ psql postgres
postgres=#\password postgres
Enter new password:
Enter it again:
postgres=#\q
$ exit
logout

7. Create a database user who is not a superuser, who can't create databases, who can't create other users/roles, and set the password right away

# su - postgres
$ createuser --no-superuser --no-createdb --no-createrole --pwprompt vinternals
Enter password for new role:
Enter it again:
$

8. Create a database and make the user we just created the owner

$ createdb --owner vinternals vinternals_db
$ exit
logout

9. Make the database we created remotely accessible from 192.168.1.0/24 by the user just created. Edit /var/lib/pgsql/data/pg_hba.conf and add the following line:

#Access to vinternals_db for user vinternals
host vinternals_db vinternals 192.168.1.0/24 password

10. Configure database server to listen for remote connections. For example, if the IP address of the database server is 192.168.1.11, change /var/lib/pgsql/data/postgresql.conf

listen_addresses = '192.168.1.11,localhost' #what IP address(es) to listen on;

11. Restart the database server.

# service postgresql restart

12. Install the psql client on another host in the same subnet (or whatever you allowed via pg_hba.conf) and test a connection

remote-host# psql -h 192.168.1.11 -U vinternals vinternals_db
psql (8.4.5)
Type "help" for help.

vinternals_db=>\l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+------------+----------+-------------+-------------+----------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
vinternals_db | vinternals | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

vinternals_db=>\q

13. Tell Larry Postgres is the ruler - you're done.