Installing PostgreSQL and Psycopg2

Posted Thursday, 04 March 2010 at 03:26 by Andrew Liu
Tagged: python | linux | fedora | postgresql | databases | web development
Read more blogs...


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 (which hasn't really updated their site since their TRAC crashed, hence my reservations about its future), and PostgreSQL from


1. Get the files


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


$ wget
$ wget


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
readline-devel.i386 0:5.2-13.fc9
Dependency Installed:
ncurses-devel.i386 0:5.6-20.20080927.fc10
$ 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       
$ 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
    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       
 34 # Data directory       
 35 PGDATA="/home/postgres/data"
 37 # Who to run the postmaster as, usually "postgres".  (NOT "root")
 38 PGUSER=postgres
 40 # Where to keep a log file
 41 PGLOG="$PGDATA/serverlog"


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 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/ -> build/lib.linux-i686-2.6/psycopg2  
copying lib/ -> build/lib.linux-i686-2.6/psycopg2  
copying lib/ -> build/lib.linux-i686-2.6/psycopg2  
copying lib/ -> build/lib.linux-i686-2.6/psycopg2  
copying lib/ -> build/lib.linux-i686-2.6/psycopg2  
copying lib/ -> build/lib.linux-i686-2.6/psycopg2  
copying lib/ -> 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 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/", line 60, in <module>
    from _psycopg import BINARY, NUMBER, STRING, DATETIME, ROWID
ImportError: 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!







Web News

Intuit reports strong Q1, small business demand and eyes midmarket firms
Intuit recently added a bevy of artificial intelligence features to its platform and is also moving upstream to midmarket companies.
--- 22 Nov 2019 08:48 AM

Pure Storage Q3 misses, Splunk beats
Pure Storage missed revenue estimates for Q3 and issued a weak outlook, while Spunk delivered strong results and upped its guidance.
--- 22 Nov 2019 08:37 AM

Twitter will finally let users disable SMS as default 2FA method
And it only took Twitter's CEO getting hacked to happen.
--- 22 Nov 2019 08:29 AM

Salesforce teases its emerging AI capabilities
At Dreamforce, the CRM powerhouse gave live demos of a fully-autonomous AI agent, the world's largest open-source language model and other tools on the horizon.
--- 22 Nov 2019 07:58 AM

Best gifts: Gadgets to get you fit and healthy
With the new year approaching, why not gently shift your focus toward good habits, health, fitness, and being kind to yourself?
--- 22 Nov 2019 06:03 AM

Company Blog

Search Behaviour

Posted Tuesday, 19 October 2010 at 05:58 by Andrew Liu

As an SEO provider, you have one main goal. Get your clients website to show up in search results fo...


sm bus drivers missing in Device Manager

Posted Sunday, 18 April 2010
Updated Sunday, 24 February 2013 at 06:39 by Andrew Liu

When installing a new Windows XPinstallation, I seemingly always miss some drivers. One that trouble...


Multiple Domains for SEO performance?

Posted Friday, 05 March 2010 at 23:13 by Andrew Liu

Online businesses and websites that cover a broad range of topics or one large topic are sometimes b...


Tag Clouds - SEO or not?

Posted Thursday, 04 March 2010 at 04:34 by Andrew Liu

A tag cloud or word cloud is a visual depiction of tags or words related to a site, typically used t...


Mozilla Thunderbird and Gmail IMAP Attachments Bug

Posted Wednesday, 03 March 2010 at 20:15 by Andrew Liu

I've been using Gmail since its early inception, and Iwas one of the first to utilise Gmail's IMAPfe...


Read more blogs...