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 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.
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
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
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.
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!
Posted Tuesday, 19 October 2010 at 05:58 by Andrew Liu
Posted Sunday, 18 April 2010
Updated Sunday, 24 February 2013 at 06:39 by Andrew Liu
Posted Friday, 05 March 2010 at 23:13 by Andrew Liu
Posted Thursday, 04 March 2010 at 04:34 by Andrew Liu
Posted Wednesday, 03 March 2010 at 20:15 by Andrew Liu