Installing PostgreSQL and Psycopg2

OS: Fedora 10

At some stage during development, you will need a database. And the database of choice is PostgreSQL. Yes I know many people use MySQL out there, and its more dominant, and blah and yada, and I've used both. But still PostgreSQL comes out on top because it is able to deal with the issues I've encountered, in a sane, logical way (not like just increasing the number of connections in MySQL and hoping for the best). I'm probably biased (just a tad), but when I started out, there were no stored procedures in MySQL. There were no triggers, no views (!), nothing that you'd expect from a true database. It was just a storage mechanism. Heck a simple FoxPro, thats all it was. So I chose PostgreSQL, and stuck with it. Sure, they've come a long way since then, but I question the ability to retrofit this, rather than building it in the framework from the start.

Ok, thats my ramble for today. Now, cut to the chase. Lets install PostgreSQL, and Psycopg2. Psycopg2 is the python database adapter that allows python to talk with the database through a DBAPI2 compliant manner (sounds cool eh). Basically thats the python standard for communicating with SQL databases. Psycopg2, again, has been my preferred choice, but this may change in the future. You can get Psycopg2 from www.initd.org (which hasn't really updated their site since their TRAC crashed, hence my reservations about its future), and PostgreSQL from www.postgresql.org.

1. Get the files

Go find the version that suits you best. I'd get just the latest stable versions to be safe.

$ wget http://wwwmaster.postgresql.org/redir/226/h/source/v8.3.6/postgresql-8.3.6.tar.bz2
$ wget http://www.initd.org/pub/software/psycopg/psycopg2-2.0.9.tar.gz

2. Install Postgres

Installing postgres can be done using the standard procedure of "./configure;make;make install", however, I like to prefix the postgresql directory so I know what version I will be running. I also move all my source files over to /usr/local/src.

$ tar xvfj postgresql-8.3.6.tar.bz2
$ mv postgresql-8.3.6 /usr/local/src   $ cd /usr/local/src/postgresql-8.3.6   $ ./configure --prefix=/usr/local/postgresql-8.3.6   $ make   $ make install

If you encounter an error when configuring postgresql, you may need to install additional libraries. Just use "yum" to do this. I need to install the readline-devel library after seeing this error. From memory, I think you may also need the zlib-devel library too.

$ ./configure --prefix=/usr/local/postgresql-8.3.6
...
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure.  It is possible the compiler isnt looking in the proper directory.
Use --without-readline to disable readline support.
$ yum install readline-devel
...
Installed:
  readline-devel.i386 0:5.2-13.fc9
Dependency Installed:
  ncurses-devel.i386 0:5.6-20.20080927.fc10
Complete!
$ make
$ make install

Postgresql cannot run as root, so we need to have a new user (normally "postgres") to act as the user for running postgres. So lets create this user, and then create some links within his home directory to postgresql, and create a new directory to store the databases.

$ useradd postgres $ cd /home/postgres
$ ln -s /usr/local/postgresql-8.3.6 postgresql-8.3.6
$ ln -s postgresql-8.3.6 postgresql $ mkdir data-8.3.6
$ ln -s data-8.3.6 data

Once this is installed, because the installation is in a non-standard directory, we need to update our PATH.

Edit our "/etc/profile" and add this in, then source the new file to make sure it is in our current environment. POSTGRESQL_HOME is for our own information, while PGDATA defines where the databases for postgresql will be stored. I created /home/postgres/data in the previous step, but if you want to have a different location for databases, you can define it yourself. Traditionally, postgresql databases are stored in /var/lib/pgsql. Wherever you have this, the directory should be owned by the user "postgres" and he should have write permissions to that directory. Thats why I tend to keep it simple and store the data within the user's home directory. The LD_LIBRARY_PATH defines the library path to postgresql.

$ vi /etc/profile
...         
 67 # Postgresql
 68 export POSTGRESQL_HOME=/usr/local/postgresql       
 69 export LD_LIBRARY_PATH=/usr/local/postgresql/lib
 70 export PGDATA=/home/postgres/data
 71 pathmunge $POSTGRESQL_HOME/bin       
 72      
 73      
 74 export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC
...
$ source /etc/profile

3. Running Postgresql for the first time

As the postgres user, you now can initialise the database. This is a once off only step for each new postgresql installation. If you get the following, then your permissions for the data directory are incorrect.

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".      

creating directory /home/postgres/data ...
initdb: could not create directory "/home/postgres/data": Permission denied

You should get the following if all goes well.

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 /home/postgres/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers/max_fsm_pages ... 24MB/153600
creating configuration files ... ok
creating template1 database in /home/postgres/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 /home/postgres/data
or
    pg_ctl -D /home/postgres/data -l logfile start

Almost there. To clean things up, we would like to add this to our startup scripts, so each time we restart the server, postgres starts up as well. We need to create a simple startup script. Luckily, the postgresql source has one lying around. So grab this and put it into the right place, then we need to make a few adjustments to that script.

postgres$ exit   
$ cp /usr/local/src/postgresql-8.3.6/contrib/start-scripts/linux /etc/rc.d/init.d/postgresql   
$ chmod 755 /etc/rc.d/init.d/postgresql   
$ vi /etc/rc.d/init.d/postgresql   
...       
 31 # Installation prefix       
 32 prefix=/usr/local/postgresql       
 33       
 34 # Data directory       
 35 PGDATA="/home/postgres/data"
 36
 37 # Who to run the postmaster as, usually "postgres".  (NOT "root")
 38 PGUSER=postgres
 39
 40 # Where to keep a log file
 41 PGLOG="$PGDATA/serverlog"
 42

Lets add this to our startup services.

$ chkconfig --add postgresql
$ chkconfig --levels 35 postgresql on
$ chkconfig --list postgresql
postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off

And start it up to make sure all is well.

$ /etc/rc.d/init.d/postgresql start
Starting PostgreSQL: ok
$ ps -ef | grep post  
root      8390 25988  0 03:31 pts/1    00:00:00 su - postgres  
postgres  8392  8390  0 03:31 pts/1    00:00:00 -bash  
postgres 10498     1  0 04:14 ?        00:00:00 /usr/local/postgresql/bin/postmaster -D /usr/local/postgresql/data  
postgres 10500 10498  0 04:14 ?        00:00:00 postgres: writer process        
postgres 10501 10498  0 04:14 ?        00:00:00 postgres: wal writer process    
postgres 10502 10498  0 04:14 ?        00:00:00 postgres: autovacuum launcher process  
postgres 10503 10498  0 04:14 ?        00:00:00 postgres: stats collector process  
root     10507  8282  0 04:15 pts/0    00:00:00 grep post

If you see something equivalent to the processes above, all is well and postgres is running. Remember the default port for postgres is 5432.

4. Installing Psycopg2

Now that we have postgresql installed and running, we can compile psycopg2 as well. The reason for a source compilation is that:

a) we know what we are doing and where we are putting it, and

b) we need the source files in order to compile psycopg2 (I'm not a big fan of binary installations).

So, we've already grabbed the source in step 1. Lets unroll that, and run the setup.

$ cd
$ tar xvfz psycopg2-2.0.9.tar.gz
$ mv psycopg2-2.0.9 /usr/local/src
$ cd /usr/local/src/psycopg2-2.0.9
$ python setup.py install
running install  
running build  
running build_py 
creating build  
creating build/lib.linux-i686-2.6  
creating build/lib.linux-i686-2.6/psycopg2  
copying lib/__init__.py -> build/lib.linux-i686-2.6/psycopg2  
copying lib/psycopg1.py -> build/lib.linux-i686-2.6/psycopg2  
copying lib/tz.py -> build/lib.linux-i686-2.6/psycopg2  
copying lib/extras.py -> build/lib.linux-i686-2.6/psycopg2  
copying lib/pool.py -> build/lib.linux-i686-2.6/psycopg2  
copying lib/errorcodes.py -> build/lib.linux-i686-2.6/psycopg2  
copying lib/extensions.py -> build/lib.linux-i686-2.6/psycopg2  
running build_ext error: No such file or directory

If you get the above error, that means that your postgresql installation is not in the path. If you added the POSTGRESQL_HOME/bin path to /etc/profile in the eariler steps, you shouldn't get this. This error, while it doesn't tell you much about it, is a complaint that the system cannot find the "pg_config" file. The "pg_config" file, as part of your postgresql installation, should be located in /usr/local/postgresql/bin/pg_config. Thus, it should be in your path. You may have to source your "/etc/profile" in case you forgot to do that.

$ which pg_config
/usr/bin/which: no pg_config in (/usr/kerberos/sbin:/usr/kerberos/bin:/usr/local/git/bin:/usr/local/Python/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/aliu/bin) 
$ source /etc/profile
$ which pg_config /usr/local/postgresql/bin/pg_config

So now you should be able to run the installation command with no trouble.

$ python setup.py install  
running install  
running build  
running build_py  
running build_ext  
building 'psycopg2._psycopg' extension  
creating build/temp.linux-i686-2.6  
creating build/temp.linux-i686-2.6/psycopg  
...  
running install_egg_info  
Writing /usr/local/Python/lib/python2.6/site-packages/psycopg2-2.0.9-py2.6.egg-info

Double check in python to make sure that psycopg2 is actually there (always a good measure to check).

$ python 
Python 2.6.1 (r261:67515, Feb 27 2009, 02:54:13) [GCC 4.3.2 20081105 (Red Hat 4.3.2-7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/Python/lib/python2.6/site-packages/psycopg2/__init__.py", line 60, in <module>
    from _psycopg import BINARY, NUMBER, STRING, DATETIME, ROWID
ImportError: libpq.so.5: cannot open shared object file: No such file or directory
>>>

If you get an import error, then you probably left out the LD_LIBRARY_PATH, or that isn't set in your current environment. Make sure you set this value in /etc/profile, and have this file sourced.

$ source /etc/profile
$ python 
Python 2.6.1 (r261:67515, Feb 27 2009, 02:54:13) [GCC 4.3.2 20081105 (Red Hat 4.3.2-7)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import psycopg2
>>>

No message means that it has successfully loaded!