Calvin's

In my previous post, I explained how we can run postgresql92 and postgresql91 concurrently.  However, that post does not explain how we can ensure that the psycopg2 we install in our project’s python virtual environment will build against postgresql92 server.

When psycopg2 is installed (via `pip install psycopg2` for instance), psycopg2 depends on the presence of `pg_config` to locate the currently active postgresql version.

If we do `pg_config –sharedir`, we will see

calvin$ pg_config --sharedir
/opt/local/share/postgresql91

which tells us that if we run `pip install virtualenv`, we will essentially be building psycopg2 against postgresql91.

Fortunately for us, using macports, it’s trivial and completely painless to switch our postgresql server version.

calvin$ sudo port select --list postgresql
Password:
Available versions for postgresql:
 none
 postgresql91 (active)
 postgresql92

calvin$ sudo port select --set postgresql postgresql92
Selecting 'postgresql92' for 'postgresql' succeeded. 'postgresql92' is now active.

calvin$ sudo port select --list postgresql
Available versions for postgresql:
 none
 postgresql91
 postgresql92 (active)

Now, running `pip install psycopg2` in our python virtual environment will correctly build our psycopg2 against our postgresql 9.2 server.

calvin$ pip install psycopg2
Downloading/unpacking psycopg2
 Downloading psycopg2-2.4.5.tar.gz (719kB): 719kB downloaded
 Running setup.py egg_info for package psycopg2

 no previously-included directories found matching 'doc/src/_build'
Installing collected packages: psycopg2
 Running setup.py install for psycopg2
 building 'psycopg2._psycopg' extension
 /usr/bin/clang -fno-strict-aliasing -fno-common -dynamic -pipe -O2 -fwrapv -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.4.5 (dt dec pq3 ext)" -DPG_VERSION_HEX=0x090202 -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/opt/local/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7 -I. -I/opt/local/include/postgresql92 -I/opt/local/include/postgresql92/server -c psycopg/psycopgmodule.c -o build/temp.macosx-10.7-x86_64-2.7/psycopg/psycopgmodule.o
 /usr/bin/clang -fno-strict-aliasing -fno-common -dynamic -pipe -O2 -fwrapv -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.4.5 (dt dec pq3 ext)" -DPG_VERSION_HEX=0x090202 -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/opt/local/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7 -I. -I/opt/local/include/postgresql92 -I/opt/local/include/postgresql92/server -c psycopg/green.c -o build/temp.macosx-10.7-x86_64-2.7/psycopg/green.o
 /usr/bin/clang -fno-strict-aliasing -fno-common -dynamic -pipe -O2 -fwrapv -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.4.5 (dt dec pq3 ext)" -DPG_VERSION_HEX=0x090202 -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/opt/local/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7 -I. -I/opt/local/include/postgresql92 -I/opt/local/include/postgresql92/server -c psycopg/pqpath.c -o build/temp.macosx-10.7-x86_64-2.7/psycopg/pqpath.o
 /usr/bin/clang -fno-strict-aliasing -fno-common -dynamic -pipe -O2 -fwrapv -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.4.5 (dt dec pq3 ext)" -DPG_VERSION_HEX=0x090202 -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/opt/local/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7 -I. -I/opt/local/include/postgresql92 -I/opt/local/include/postgresql92/server -c psycopg/utils.c -o build/temp.macosx-10.7-x86_64-2.7/psycopg/utils.o
 /usr/bin/clang -fno-strict-aliasing -fno-common -dynamic -pipe -O2 -fwrapv -DNDEBUG -g -fwrapv -O3 -Wall -Wstrict-prototypes -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.4.5 (dt dec pq3 ext)" -DPG_VERSION_HEX=0x090202 -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/opt/local/Library/Frameworks/Python.framework/Versions/2.7/include/python2.7 -I. -I/opt/local/include/postgresql92 -I/opt/local/include/postgresql92/server -c psycopg/bytes_format.c -o build/temp.macosx-10.7-x86_64-2.7/psycopg/bytes_format.o

Q.E.D.

Next! :-)

One of the trickier problems when managing multiple projects involve supporting different versions of open source library or software that your varying projects depend on.

In my current scenario, I need to support web projects which are still running on postgresql 9.1, while building up new projects which uses postgresql 9.2.

Getting postgresql92, postgresql92-server packages installed via macports is the easy part.

$ sudo port install postgresql92 postgresql92-server

A simple `port install` command does the  job.  The problem is getting making sure that they run alongside each other so that I can switch out from working on a project that uses postgresql 9.1 server easily to another project that uses postgresql 9.2.

Once our macports install completes running, we will create our initial database. Like so:

$ sudo mkdir -p /opt/local/var/db/postgresql92/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql92/defaultdb
$ sudo su postgres -c '/opt/local/lib/postgresql92/bin/initdb -D /opt/local/var/db/postgresql92/defaultdb'

Nothing surprising here. Except that our postgresql 9.1 server has occupied the default 5432, so we will need to make sure that our postgresql 9.2 server uses a different port.  Opening up our postgresql 9.2 server’s postgresql.conf file with `sudo vim /opt/local/var/db/postgresql92/defaultdb/postgresql.conf`,we set our postgresql 9.2′s port to a different one.

port = 5433 # (change requires restart)

Once our postgresql 9.2 server is properly initialized, we can manually start it using

$ sudo su postgres -c '/opt/local/lib/postgresql92/bin/postgres -D /opt/local/var/db/postgresql92/defaultdb'

which may not release the process to the background.

To release the process to the background, we can instead use Mac OSX’s daemondo. Like this:

$ sudo daemondo --label=postgresql92-server --start-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql92-server/postgresql92-server.wrapper start

Finally, to ensure that our postgresql 9.2 server starts up everytime we boot up our Mac, alongside our postgresql 9.1 server, we can set

$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql92-server.plist

No different from our we make `launchctl` start up postgresql 9.1 server when our Mac boots up.

To check that our postgresql 9.2 server is indeed running, we can execute:

$ psql92 -p 5433 -U postgres
psql92 (9.2.1)
Type "help" for help.
postgres=#

From now on, we will have to be sure that settings and configuration for projects that try to connect to our postgresql 9.2 server uses port 5433.

One more thing – Postgis 2.0.

$ sudo port -v install postgis2 +postgresql92 +raster +topology

Since we use postgis regularly in our projects (for applications requiring GIS functionalities), we need to install the version compatible with postgresql 9.2. This is trivial with Postgis 2.0(.1). We no longer need to go through multiple steps to create a template spatial database (“template_postgis”). In Postgis 1.5, we need to initialize our database with this “template_postgis” template spatial database that we create.

From Postgis 2.0 onwards, it’s straightforward.

$ psql92 -p 5433 -U postgres -d your_db

psql92 (9.2.1)
Type "help" for help.

your_db=#  CREATE EXTENSION postgis;

your_db=# CREATE EXTENSION postgis_topology;

The 2nd `CREATE EXTENSION postgis_topology` is optional at this time of writing as geodjango does not have the necessary functionality to support Postgis’ Topological features yet.

I have recently upgraded to use Python 2.7 on my Mac for local development work for a python/django project and needed to use py27-mysql.  So naturally, this is what I did…

calvin$ sudo port -v install python27
calvin$ sudo python_select python27
calvin$ sudo port -v install py27-setuptools

Unfortunately when I attempted to install py27-mysql via MacPorts, this is the error I received:

calvin$ sudo port -v install py27-mysql
--->  Computing dependencies for py27-distribute.Error: Unable to execute port: Can't install py27-distribute because conflicting ports are installed: py27-setuptools
To report a bug, see <http://guide.macports.org/#project.tickets>

It appears that py27-mysql needs to use py27-distribute instead of py27-setuptools.

To solve this problem, I decided to uninstall py27-setuptools and give py27-distribute a spin.  So:

calvin$ sudo port -v uninstall py27-setuptools
--->  Deactivating py27-setuptools @0.6c11_1
--->  Uninstalling py27-setuptools @0.6c11_1
--->  Uninstall is removing py27-setuptools from the port registry.
calvin$ sudo port -v install py27-mysql
--->  Computing dependencies for py27-mysql..
--->  Dependencies to be installed: py27-distribute
--->  Fetching py27-distribute
--->  Verifying checksum(s) for py27-distribute
--->  Checksumming distribute-0.6.15.zip
--->  Extracting py27-distribute
--->  Extracting distribute-0.6.15.zip
--->  Configuring py27-distribute
--->  Building py27-distribute
...
running install_egg_info
running egg_info
writing MySQL_python.egg-info/PKG-INFO
writing top-level names to MySQL_python.egg-info/top_level.txt
writing dependency_links to MySQL_python.egg-info/dependency_links.txt
deleting MySQL_python.egg-info/native_libs.txt
reading manifest file 'MySQL_python.egg-info/SOURCES.txt'
reading manifest template 'MANIFEST.in'
writing manifest file 'MySQL_python.egg-info/SOURCES.txt'
Copying MySQL_python.egg-info to /opt/local/var/macports/build/_opt_local_var_macports_sources_rsync.macports.org_release_ports_python_py27-mysql/work/destroot/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/MySQL_python-1.2.2-py2.7.egg-info
running install_scripts
--->  Installing py27-mysql @1.2.2_0+mysql5
--->  Activating py27-mysql @1.2.2_0+mysql5
--->  Cleaning py27-mysql
--->  Removing work directory for py27-mysql

And magically, py27-mysql now works as required.

Hurrah. :-)