At the end of this walkthrough you will have the PostgreSQL 8.4 database installed on CentOS 6.2 ready for use with your web projects. Postgres 8.4 is not the latest version, but it is stable and good enough for web development purposes. This set-up is “webby” in the sense that the it should be familiar to web developers.

Prerequisites

You need to be familiar with basic Linux system administration including editing configuration files with text-editors like vi or emacs.

This is our system. It is a basic CentOS 6.2 installation with a static IP:

$ uname -a
Linux schettino.kelvinwong.ca 2.6.32-220.4.1.el6.i686 #1 SMP Mon Jan 23 22:37:12 GMT 2012 i686 i686 i386 GNU/Linux
$ cat /etc/redhat-release
CentOS release 6.2 (Final)

Install Postgres

Installation of Postgres with yum is simple:

[kelvin@schettino ~]$ sudo yum install postgresql-server
[sudo] password for kelvin: 
Loaded plugins: fastestmirror, presto
Loading mirror speeds from cached hostfile
 * base: mirror.its.sfu.ca
 * extras: mirror.its.sfu.ca
 * updates: mirror.its.sfu.ca
base                                                     | 3.7 kB     00:00     
extras                                                   | 3.5 kB     00:00     
updates                                                  | 3.5 kB     00:00     
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql-server.i686 0:8.4.9-1.el6_1.1 will be installed
--> Processing Dependency: postgresql-libs(x86-32) = 8.4.9-1.el6_1.1 for package: postgresql-server-8.4.9-1.el6_1.1.i686
--> Processing Dependency: postgresql(x86-32) = 8.4.9-1.el6_1.1 for package: postgresql-server-8.4.9-1.el6_1.1.i686
--> Processing Dependency: libpq.so.5 for package: postgresql-server-8.4.9-1.el6_1.1.i686
--> Running transaction check
---> Package postgresql.i686 0:8.4.9-1.el6_1.1 will be installed
---> Package postgresql-libs.i686 0:8.4.9-1.el6_1.1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                  Arch        Version                 Repository   Size
================================================================================
Installing:
 postgresql-server        i686        8.4.9-1.el6_1.1         base        3.3 M
Installing for dependencies:
 postgresql               i686        8.4.9-1.el6_1.1         base        2.7 M
 postgresql-libs          i686        8.4.9-1.el6_1.1         base        201 k

Transaction Summary
================================================================================
Install       3 Package(s)

Total download size: 6.2 M
Installed size: 28 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 6.2 M
(1/3): postgresql-8.4.9-1.el6_1.1.i686.rpm               | 2.7 MB     00:01     
(2/3): postgresql-libs-8.4.9-1.el6_1.1.i686.rpm          | 201 kB     00:00     
(3/3): postgresql-server-8.4.9-1.el6_1.1.i686.rpm        | 3.3 MB     00:01     
--------------------------------------------------------------------------------
Total                                           1.5 MB/s | 6.2 MB     00:04     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : postgresql-libs-8.4.9-1.el6_1.1.i686                         1/3 
  Installing : postgresql-8.4.9-1.el6_1.1.i686                              2/3 
  Installing : postgresql-server-8.4.9-1.el6_1.1.i686                       3/3 

Installed:
  postgresql-server.i686 0:8.4.9-1.el6_1.1                                      

Dependency Installed:
  postgresql.i686 0:8.4.9-1.el6_1.1    postgresql-libs.i686 0:8.4.9-1.el6_1.1   

Complete!
[kelvin@schettino ~]$

The server is installed along with the required client programs.

Configure Postgres – Initialize and start service

After installing Postgres you will need to initialize the database (once only):

[kelvin@schettino ~]$ sudo service postgresql initdb
Initializing database:                                     [  OK  ]

Set the server to restart on reboots and start the postmaster service:

[kelvin@schettino ~]$ sudo chkconfig postgresql on
[sudo] password for kelvin: 
[kelvin@schettino ~]$ sudo service postgresql start
Starting postgresql service:                               [  OK  ]

Configure Postgres – Set superuser password

Now let’s set a password for the superuser (the postgres user) using the PostgreSQL interactive terminal. Jump into the postgres user by using su (with the dash to get a login shell):

[kelvin@schettino ~]$ su -
Password: 
[root@schettino ~]# su - postgres
-bash-4.1$ psql
psql (8.4.9)
Type "help" for help.

postgres=# \password postgres
Enter new password: 
Enter it again:
postgres=# \q
-bash-4.1$

Configure Postgres – Activate password authentication

By default, the server uses ident as defined in the “PostgreSQL Client Authentication Configuration File”. If you open up pg_hba.conf you can see this default configuration:

67
68
69
70
71
72
73
74
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident
# IPv6 local connections:
host    all         all         ::1/128               ident

Ident is a mapping of local system users (see /etc/passwd for list of system users) to Postgres users. I have never found this authentication method useful for any of the web development work that I have done. I always change it to “md5″ which allows you to create arbitrary users and passwords. Let’s change the server’s client configuration file (I assume you are still using the postgres user shell):

-bash-4.1$ whoami
postgres
-bash-4.1$ vim /var/lib/pgsql/data/pg_hba.conf

Change the “ident” methods to “md5″ methods at the bottom of the pg_hba.conf file:

67
68
69
70
71
72
73
74
75
76
77
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# "local" is for Unix domain socket connections only
local   all         all                               md5
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5
# If you don't want to open Postgres to the Internet
# don't enable this line
host    all         all         0.0.0.0/0             md5

By default, Postgres binds only to localhost and you will need to explicitly tell it to bind to your machine’s IP address. The setting is in postgres.conf. If you don’t need remote access you can skip this.

-bash-4.1$ vim /var/lib/pgsql/data/postgresql.conf

Change the listen_addresses setting to an asterisk to listen to all available IP addresses:

57
58
59
60
61
62
63
# - Connection Settings -
 
listen_addresses = '*'
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)

Restart your postgres server (exit postgres user into the root shell):

-bash-4.1$ exit
logout
[root@schettino ~]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
[root@schettino ~]#

Open Firewall (optional)

If you want remote access to the server on Postgres port 5432 you will have to open a port on the firewall. If you still are the root user, type the following:

[root@schettino ~]# whoami
root
[root@schettino ~]# vim /etc/sysconfig/iptables

You can just copy the SSH port rule in iptables and modify the port number from 22 to 5432. Add the following rule just below the SSH port rule and above the rejection rule for the INPUT chain:

10
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT

When changed, it should look like this:

2
3
4
5
6
7
8
9
10
11
12
13
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -i lo -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT
-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
-A INPUT -j REJECT --reject-with icmp-host-prohibited
-A FORWARD -j REJECT --reject-with icmp-host-prohibited
COMMIT

Reload the rules:

[root@schettino ~]# service iptables restart
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Unloading modules:                               [  OK  ]
iptables: Applying firewall rules:                         [  OK  ]
[root@schettino ~]# exit
logout
[kelvin@schettino ~]$

Try it out with pgbench (optional)

To demonstrate the basic use of your new Postgres server, you can try out pgbench which is in the postgresql-contrib RPM. Let’s install it, create a new user, create a new database and run pgbench against it:

[kelvin@schettino ~]$ sudo yum install postgresql-contrib
Loaded plugins: fastestmirror, presto
Loading mirror speeds from cached hostfile
 * base: mirror.its.sfu.ca
 * extras: mirror.its.sfu.ca
 * updates: mirror.its.sfu.ca
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package postgresql-contrib.i686 0:8.4.9-1.el6_1.1 will be installed
--> Processing Dependency: libxslt.so.1(LIBXML2_1.0.18) for package: postgresql-contrib-8.4.9-1.el6_1.1.i686
--> Processing Dependency: libxslt.so.1(LIBXML2_1.0.11) for package: postgresql-contrib-8.4.9-1.el6_1.1.i686
--> Processing Dependency: libxslt.so.1 for package: postgresql-contrib-8.4.9-1.el6_1.1.i686
--> Processing Dependency: libossp-uuid.so.16 for package: postgresql-contrib-8.4.9-1.el6_1.1.i686
--> Running transaction check
---> Package libxslt.i686 0:1.1.26-2.el6 will be installed
---> Package uuid.i686 0:1.6.1-10.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package                   Arch        Version                Repository   Size
================================================================================
Installing:
 postgresql-contrib        i686        8.4.9-1.el6_1.1        base        346 k
Installing for dependencies:
 libxslt                   i686        1.1.26-2.el6           base        448 k
 uuid                      i686        1.6.1-10.el6           base         54 k

Transaction Summary
================================================================================
Install       3 Package(s)

Total download size: 848 k
Installed size: 3.3 M
Is this ok [y/N]: y
Downloading Packages:
Setting up and reading Presto delta metadata
Processing delta metadata
Package(s) data still to download: 848 k
(1/3): libxslt-1.1.26-2.el6.i686.rpm                     | 448 kB     00:00     
(2/3): postgresql-contrib-8.4.9-1.el6_1.1.i686.rpm       | 346 kB     00:00     
(3/3): uuid-1.6.1-10.el6.i686.rpm                        |  54 kB     00:00     
--------------------------------------------------------------------------------
Total                                           520 kB/s | 848 kB     00:01     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : uuid-1.6.1-10.el6.i686                                       1/3 
  Installing : libxslt-1.1.26-2.el6.i686                                    2/3 
  Installing : postgresql-contrib-8.4.9-1.el6_1.1.i686                      3/3 

Installed:
  postgresql-contrib.i686 0:8.4.9-1.el6_1.1                                     

Dependency Installed:
  libxslt.i686 0:1.1.26-2.el6              uuid.i686 0:1.6.1-10.el6             

Complete!
[kelvin@schettino ~]$ which pgbench
/usr/bin/pgbench

Create a new Postgres user by using the createuser wrapper (the P switch allows you to set a password for your new user):

[kelvin@schettino ~]$ su -
Password: 
[root@schettino ~]# su - postgres
-bash-4.1$ createuser -P francesco
Enter password for new role: [password for user francesco]
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
Password: [password for postgres]
-bash-4.1$

Make a new database named “winnings” and change the owner to “francesco”:

-bash-4.1$ createdb -O francesco winnings
Password: [password for postgres]
-bash-4.1$ 

Now we can fill it up with pgbench:

-bash-4.1$ pgbench -i -U francesco winnings
Password: [password for user francesco]
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
creating tables...
10000 tuples done.
20000 tuples done.
30000 tuples done.
40000 tuples done.
50000 tuples done.
60000 tuples done.
70000 tuples done.
80000 tuples done.
90000 tuples done.
100000 tuples done.
set primary key...
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"
vacuum...done.
-bash-4.1$ pgbench -c 4 -S -t 2000 -U francesco winnings
Password: [password for user francesco]
starting vacuum...end.
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 4
number of transactions per client: 2000
number of transactions actually processed: 8000/8000
tps = 4836.016718 (including connections establishing)
tps = 5052.773057 (excluding connections establishing)
-bash-4.1$ pgbench -c 4 -t 2000 -U francesco winnings
Password: [password for user francesco]
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 4
number of transactions per client: 2000
number of transactions actually processed: 8000/8000
tps = 237.345234 (including connections establishing)
tps = 237.889294 (excluding connections establishing)
-bash-4.1$

You can clean up the database by dropping the “winnings” database and dropping “francesco”:

-bash-4.1$ dropdb winnings
Password: [password for postgres]
-bash-4.1$ dropuser francesco
Password: [password for postgres]
-bash-4.1$ 

Enjoy your webby Postgres!

Caveat! If you have an Apache/PHP5 server that wants to talk to your Postgres, you will have to set the appropriate SELinux boolean to allow the communication: setsebool -P httpd_can_network_connect_db 1