Tag Archives: windows

Testing Python and PostgreSQL on Windows, Part 6

Alliterative locales, languages, collations.

A tox on all your houses (test combinations).

The last item to fix in the Pyrseas unit tests so that they run on Windows is related to the PostgreSQL 9.1 COLLATION feature. When creating the tests, I was influenced by the examples in the documentation, i.e., I created a collation with ‘fr_FR.utf8′ LC_COLLATE and LC_CTYPE. On Linux, it’s fairly straightforward to add such a locale to your system (although perhaps Windows users may disagree :-)), so the tests worked as expected.

On Windows, however, most collation tests failed with

DataError: could not create locale "fr_FR.utf8": No error

Unfortunately, the PG documentation doesn’t seem to provide any hints on what is the Windows equivalent of ‘fr_FR.utf8′ (or similar Linux locales). Eventually I figured it out by looking at the output of \l (list databases) in psql. This showed the Collation and Ctype (in my case) where ‘English_United States.1252′ so I assumed what was needed was ‘French.France.1252′. Here is the procedure to set that up:

Open the Control Panel, select Date, Time, Language, and Regional Options, then Regional and Language Options (or Add other languages), click on the Advanced tab in the dialog and then choose “French (France)” from the dropdown. Finally, click OK and respond to any subsequent prompts to install the locale, including rebooting the machine.

Aside: For comparison, on Debian Linux, the equivalent procedure involves running sudo dpkg-reconfigure locales, selecting fr_FR.UTF-8 UTF-8 from a list, accepting the default locale and waiting for the locales to be generated (no reboot necessary). Second aside: On Linux, you can deselect a locale to remove it from your system, but Windows doesn’t appear to allow for language removals.

To test, make sure you have the latest Pyrseas code from GitHub, which includes a change to fix the COLLATION tests to run on Windows.

Finally, we’re ready to install Tox and run all the unit tests with a single command. First, run pip install tox under both Python 2.7 and 3.2. Next, define (set) the environment variables PG84_PORT, PG90_PORT, PG91_PORT, and PG92_PORT to point to the corresponding PostgreSQL ports.

Then simply invoke tox from the Python 2.7 environment. Thanks to the Pyrseas tox.ini, this will install Python 2.7 and 3.2 virtualenvs, under a .tox subdirectory in the Pyrseas tree, install Psycopg2, PyYAML and Pyrseas into each virtualenv and run the unit tests eight times, once for each combination of Python and PostgreSQL.

If you have been following along, the only test failure should be in test_extension.py, in test_map_lang_extension, when attempting to CREATE EXTENSION plperl, due to the missing PERL514.DLL (see previous post). The error will only occur under PG 9.1 and 9.2.

The only problem I noticed with tox is that when there are errors it may get confused in its summary report.

___________________________________ summary ___________________________________
  py27pg90: commands succeeded
  py27pg91: commands succeeded
  py27pg92: commands succeeded
ERROR:   py32pg91: commands failed
  py27pg84: commands succeeded
ERROR:   py32pg84: commands failed
ERROR:   py32pg90: commands failed
ERROR:   py32pg92: commands failed

The errors actually occurred in the *pg91 and *pg92 environments but tox reports that all py32* tests failed, which was not the case. This is a minor issue considering all that tox accomplishes, with very little setup or configuration.

Testing Python and PostgreSQL on Windows, Part 5

I’ve got the Perl on Windows blues …

Aside from PL/pgSQL, the base distribution of PostgreSQL supports three procedural languages: Perl, Python and Tcl. When creating Pyrseas unit tests for languages (before they became EXTENSIONs), PL/Perl seemed like the “natural” choice. Perl is available on virtually all Linux and BSD base distributions, and (in contrast to Python) PL/Perl is available in both trusted and untrusted versions—a distinguishing attribute that the unit tests ought to check.

Alas, when testing on Windows I discovered that Perl isn’t as ubiquitous or as easy to deal with as it is on Linux.

The preliminary research prompted me to install Active Perl, specifically its Community Edition. Currently, the only two versions available are 5.14 and 5.16. Strangely enough, if you install one of those versions and then attempt to create the PL/Perl language under PG 8.4 or 9.0, the command succeeds, but when you try to create a function using plperl, you’ll see something like:

ERROR:  could not load library "C:/Program Files/PostgreSQL/8.4/lib/plperl.dll":
 The specified module could not be found.

Against PG 9.1 and 9.2, if you installed Perl 5.16, you’ll see the error message when issuing the CREATE EXTENSION (or LANGUAGE) statement. If you installed Perl 5.14, there should be no error.

When I first saw the error message, I was a bit puzzled since the plperl.dll libraries had all been installed and were located in the paths show in the messages. What “specified module” was missing?

Some web searching pointed me to Dependency Walker (depends.exe), a tool that appears to be indispensable if you’re going to be testing with multiple executable and DLL versions. It is analogous to Linux ldd. Depends.exe showed that plperl.dll in the 8.4 and 9.0 installations was linked with PERL510.DLL and in 9.1 and 9.2 with PER514.DLL.

Unfortunately, Active Perl has no Perl 5.10 Community Edition available, so off I was looking for an alternative. Thus I found Strawberry Perl.

The downside of Strawberry Perl’s installers is that they install it in C:\strawberry so you can’t have both Perl 5.10 and 5.14 at the same time. Someone on IRC explained that it is possible to install it in two separate paths (but it ain’t easy). For now, I chose to only install Perl 5.10. This allowed me to test Pyrseas using Python 2.7 and 3.2 against PostgreSQL 8.4, 9.0, 9.1 and 9.2, with only one Perl-related test faling (under PG 9.1 and 9.2, due to the absence of Perl 5.14).

A note of caution: Strawberry Perl installs GCC (3.4.5 in the Perl 5.10 version). If you have a pydistutils.cfg specifying a mingw32 compiler (as mentioned in my previous post), that may cause problems if you try to install or upgrade psycopg2 (or some other C extension module).

Testing Python and PostgreSQL on Windows, Part 4

At the end of Part 2, I suggested those who were anxious to start testing could try python tests\dbobject\test_schema.py right after installing psycopg2, and implied everything would work just fine by showing the output of a successful run.

That was deceptive because before running the Pyrseas tests you need to create a PostgreSQL user. So you first need to connect using psql or pgAdmin, as the postgres user and issue a command such as the following:


Of course, username and password should be your user name and a suitable password, respectively. While you’re at it, you can also create the two testing roles:


The user username needs the CREATEDB privilege to create the Pyrseas test database, by default, pyrseas_testdb. Alternatively, the test database could be created first, e.g., by postgres and owned by username. Also, if username isn’t granted the SUPERUSER privilege then the tests requiring it will be skipped.

One last detail before being able to run the tests: create a PostgreSQL password file, i.e., %APPDATA%\postgresql\pgpass.conf. The format is straightforward:


Make sure you have the latest pyrseas/testutils.py. It includes a change to make the tests portable to Windows. You’re now ready to give all the tests a whirl:

python tests\dbobject\__init_py

or you may prefer to use test discovery:

python  -m unittest discover -s tests/dbobject

One of the first issues you’ll notice is when running test_tablespace.py, which exercises support for PostgreSQL tablespaces. According to the documentation, “tablespaces can be used only on systems that support symbolic links” (aside: thanks to Andres Freund and Merlin Moncure for answering questions about this on IRC).

It seems Windows Vista (or even XP) may have something akin to symbolic links (junctions?). Nevertheless for Pyrseas, it is sufficient to create directories for the tablespaces, e.g.,

C:\>md c:\somedir\pg\9.1\ts1
C:\>md c:\somedir\pg\9.1\ts2

However, when you try to define the tablespace from psql, you’ll probably see:

postgres=# CREATE TABLESPACE ts1 LOCATION E'C:\\somedir\\pg\\9.1\\ts1';
ERROR:  could not set permissions on directory "C:/somedir/pg/9.1/ts1": Permission denied

The problem is that the directory needs to be owned by the postgres user. Unfortunately, there is nothing equivalent to chown on native Windows XP Home. Andres had mentioned cacls and after some web searching and trying, I came up with the following commands as the nearest approximation to chown:

cacls c:\somedir\pg\9.1\ts1 /E /G postgres:F
cacls c:\somedir\pg\9.1\ts2 /E /G postgres:F

These give the postgres user FULL owner privileges over the directories. If you invoke the CREATE TABLESPACE statements again, then they should succeed … Well, at least on PostgreSQL 8.4, 9.0 and 9.1, the cacls commands allow the tablespaces to be defined. However, under 9.2 the “Permission denied” error persists (still haven’t figured out why—if you know why, do leave a comment).

Update: Thank to alemarko’s comment below, I figured out that for PG 9.2, assuming you installed with the defaults, the correct incantations  to use are:

cacls c:\somedir\pg\9.2\ts1 /E /G networkservice:F
cacls c:\somedir\pg\9.2\ts2 /E /G networkservice:F

Testing Python and PostgreSQL on Windows, Part 2

In the previous post, I covered installation of Git, PostgreSQL and Python under Windows in order to set up a Pyrseas testing and development environment. Today, we’ll explore installation of the Python dependencies.

The Hitchhiker’s Guide to Python recommends first downloading and running the distribute_setup.py script. This gives you the easy_install command but the Guide recommends installing pip (with easy_install pip) and then using pip to install all other modules.

You can use pip to install pyyaml with the following command:

pip install pyyaml

However, if you try pip install psycopg2 (or even easy_install psycopg2), it’s very likely you’ll see the error:

error: Unable to find vcvarsall.bat

As best as I’ve been able to determine the only way to get around this is by installing Microsoft Visual Express Studio. According to this email and this post, for Python 2.7, it must be the 2008 Express Studio which, to make things interesting, is no longer available from the download links given. If you search enough you may find it here (download vcsetup.exe) (Update below). After installing VC++ 2008 Express (and if you haven’t installed Strawberry Perl—a later installment in our saga), the pip install psycopg2 command should succeed.

However, if you try to import psycopg2 at the Python 2.7 prompt you may be surprised with a traceback ending in:

    from psycopg2._psycopg import BINARY, NUMBER, STRING, DATETIME, ROWID
ImportError: DLL load failed: The specified module could not be found.

Ahh … the mysteries of Windows DLLs. Don’t despair: this probably means you don’t have the PostgreSQL DLLs (libpq.dll in particular) in your PATH. Add one of the postgres\x.x\bin directories to your PATH and (hopefully) you should then be able to connect from Python 2.7 to your PostgreSQL installations.

OK, let’s turn our attention to Python 3.2. If you followed the Hitchhiker’s Guide instructions previously and added C:\Python27 to your PATH, you’ll now have to change that to C:\Python32. Suggestion: create a couple of batch scripts, e.g., env27.bat and env32.bat, so you can easily switch between the two Python installations. And don’t forget to add the postgres\x.x\bin directory as well.

For 3.2, once again run the distribute_setup.py script, easy_install pip, and pip install pyyaml, as for 2.7 above. Then you can run pip install psycopg2, and if you installed VC++ previously, the gods may smile upon you and you may see the following message:

Successfully installed psycopg2
Cleaning up...

At this point, if you followed along, you’ll have four versions of PostgreSQL (8.4 through 9.2), two versions of Python (2.7 and 3.2), each with PyYAML and psycopg2, ready for testing. If you’re anxious to check things out, invoke one of the PATH setup scripts and try the following, from the Pyrseas source directory:

C:\...\src\Pyrseas>python tests\dbobject\test_schema.py
Ran 12 tests in 1.452s


There are some alternatives to installing psycopg2 using pip and VC++ 2008.  I’ll cover those in a subsequent post.

Update: Microsoft seems to keep changing download URLs. Your best bet is to search for “Visual C++ 2008 Express download.” Currently, that should lead you to the following download link.

Testing Python and PostgreSQL on Windows – Basics

In my previous post, I wrote:

Although I have not yet personally run the [Pyrseas] unit tests on Windows …, I believe the tox setup should be quite portable …, since the tests only depend on Python and psycopg2 being able to connect to Postgres, i.e., they do not depend on running any PG utilities from the command line.

Several moons ago, I had done a cursory test of the Pyrseas utilities on Windows from a source zip file, but now I wanted to set up a full development environment (well, almost full—I used Notepad for minor editing) and run through all the unit tests on as many Python/PostgreSQL combinations as possible and ideally using tox.

This post describes what I found out during the install/test process. Hopefully others will find it useful.

Operating System

I chose to use Windows XP Home Edition running under VirtualBox. It’s not a professional solution, but I wasn’t prepared to pay for the “privilege” of using Windows and it’s likely others also have a home edition CD or similar media from an earlier hardware purchase.

Version Control

Pyrseas sources are stored on GitHub. Installing Git and cloning the repository was probably the most uneventful step. The Git download page gives you an installer which offers three options. I chose “Use Git Bash only” as this appears to be the most friendly to someone coming from a Linux/Unix environment. It doesn’t change nor does it require you to change the PATH, all you need to do is select “Git Bash” from the Start menu and a Bash shell is opened for you.


Installing PostgreSQL was fairly straightforward. The Windows download page leads to EnterpriseDB one-click installers for multiple platforms and for the more recent versions you have to choose between 32-bit and 64-bit systems. The installer asks for an installation directory, data directory, postgres user password, port number and locale, offering defaults except for the password.

The installer installs both the DBMS and pgAdmin III. If you’re more comfortable with psql, you can select “SQL Shell (psql)” from the Start menu. With either the latter or pgAdmin, you won’t have to change PATH, unless you want to run psql or some other PostgreSQL utility from a Command Prompt window.


Installing Python can be done from Windows MSI installers available from Python.org for the latest releases of Python 2.7 and 3.2. Aside from specifying the installation directory, you’re given the choice of additional components to install, e.g., Tcl/Tk, documentation.

The installers provide a “Python (command line)” option from the Start Menu, but for testing or development, you’ll probably want to open your own Command Prompt window, in order to customize your setup. This requires that you add, e.g., C:\Python27 and C:\Python27\Scripts, to your PATH. Alternatively, you could use the Git Bash window to stay within a Unix-like environment (in which case you’ll still have to add the equivalent directories, e.g., /c/Python27, to PATH).

So far so good. A forthcoming post will cover more, shall we say, entertaining topics.