Problem solve Get help with specific problems with your technologies, process and projects.

Running PostgreSQL in a cross-platform environment

PostgreSQL is a great open source database management tool that's compatible across platforms. This article by Robert Treat explains how PostgreSQL facilitates cross-platform development and points out various tools within PostgreSQL that help database administrators get the job done.

Many people consider PostgreSQL 8.0 to be a groundbreaking release for the PostgreSQL project, calling it the first version to include native Windows support. But while it did indeed take a significant step in this direction, if you look a bit further you will see that PostgreSQL 8.0 did not cover entirely new territory so much as bring the final piece to the puzzle.

PostgreSQL has actually had GUI clients available on the Windows platform for a number of years, and the majority of the ERD tools available for PostgreSQL run on Windows as well. Furthermore PostgreSQL has also supported Windows on the server in a few different ways in the past, such as using Unix toolkits like Cygwin or offering commercial forks of the code, like SRA's Powergres. The difference is that with the advent of the Windows release, PostgreSQL offers a full featured, enterprise-class, open source database that can be managed across all platforms.

PostgreSQL client tools for every platform
Regardless of your preferred platform, PostgreSQL offers a number of potential tools. All of the basic tools that come with the PostgreSQL package, like psql, pg_dump and pg_restore, are available on each of PostgreSQL's supported platforms.

If you're looking for a GUI tool, the most popular of the open source solutions is pgAdmin III, which runs on Windows, Linux, Solaris and OSX. Of course you could also choose a Web-based solution like phpPgAdmin; being PHP-based it will run on any platform that runs PHP, and being Web-based it can be accessed from any platform that supports a modern Web browser.

If cross-platform compatibility is not a concern, you can choose from a wide range of platform-specific tools. On the Linux side, both Gnome and KDE offer database front ends in Mergeant and Knoda, respectively. On the Windows side, you have a plethora of options, although most are commercial programs. You'll find some PostgreSQL-specific tools, like PG Lightning Admin, as well as support from a number of companies that offer cross-database products, such as Navicat and EMS Hi-tech.

It's worth stating that most, if not all, of these application can actually administer servers on other operating systems. For example, you can use a Windows GUI client to connect to a PostgreSQL server running on Linux with no limitation or restrictions. In this way PostgreSQL lets you interact with your server from whatever platform you are most comfortable with.

Managing PostgreSQL on multiple platforms
This spirit of cross compatibility carries over to the core PostgreSQL server software itself. For the most part, working with a PostgreSQL database is the same on a Linux machine, a Windows machine, or even a BSD machine. For example, the postgresql.conf, PostgreSQL's server configuration file, will contain the same options on each platform.

Of course SQL works the same regardless of OS, but going deeper, internal functions and even the basic function languages like plpgsql are inherently compatible across platforms. As long as you are running a fairly recent release of your operating system, you won't find any missing features within PostgreSQL when moving between operating systems. This cross-platform compatibility is especially helpful for developers, who can do their development on the platform most comfortable to them and then deploy to whichever platform their companies have chosen for their servers.

While PostgreSQL tries its best to work with all platforms equally, database administrators should be aware of a few differences. Unlike some other databases, PostgreSQL generally tries to avoid re-implementing a lot of the functionality that your operating system handles, like file system management or some process-related tools. The advantage is that administrators are often already familiar with the basic tools used to study PostgreSQL performance, but it does mean a steeper learning curve if you are deploying on an operating system that you are not already familiar with.

The thing to remember is that the principals are the same on all platforms; you'll want to search for tools that can monitor processes, memory usage, CPU usage, and I/O activity. On Linux or BSD systems, you'll want to use programs like ps, top, vmstat, or iostat. On Windows, you'll use programs like task manager and resource monitor, and probably some additional packages, like the highly recommended Sysinternals Windows utilities.

Cross-platform development
As noted before, PostgreSQL's cross-platform compatibility is especially helpful for developers. As you would expect, the access methods and SQL syntax are all compatible across platforms, so the majority of development is virtually indifferent to the operating system environment. PostgreSQL also supplies you with the powerful plpgsql procedural language, which can be used to build cross-platform applications right in the database. In fact some people use plpgsql to centralize business functionality inside the database and build platform-specific front ends to access those functions.

If you want to employ such a system, PostgreSQL offers a wide variety of language-specific interfaces. With its support for old stand-bys like C, tcl or Perl along with relatively newer languages like C#, Python and Ruby, chances are PostgreSQL offers an interface in your language of interest. It is worth pointing out that not all of these interfaces are bundled directly with the core server, so you might need to visit one of the PostgreSQL project development sites, like, or

Don't misinterpret this as a sign of under-supported development; it is actually rather the opposite. Take, for example, Npgsql, the C# interface created by over a dozen developers including one of the PostgreSQL Core Team members. With the freedom of living outside the project, team members can spur faster development releases and allow for more timely bugfixes than if they had to coordinate everything through the core server.

In some areas you will need to take extra care when embarking on cross-platform development, primarily if you are developing C language database functions. The more operating system interaction you have, the more platform-specific code you will need inside your functions. Also, when you compile these functions for Windows platforms you will need a .dll file, but on Linux you will end up referencing a .so file when you create the function inside PostgreSQL. Remember this is just for function creation; once the function is loaded into the database, you can interact with it from any platform. For a good example of a complex cross-platform application designed with a mix of C and plpgsql functions, look up the Slony project, which provides a cross-version, cross-platform, replication system for PostgreSQL.

Future developments
Right now the future of PostgreSQL has never been brighter. Since releasing its Windows version, the PostgreSQL project has picked up a number of developers with strong Windows expertise who have added key core functionality to the project. The project also has seen increased commercial interest, with companies like Pervasive Inc., and EnterpriseDB helping to push PostgreSQL adoption on multiple platforms. With the release of PostgreSQL 8.1, even some core developers were surprised by how few bugs and other related problems they had with the initial release, and they are now fully engaged in keeping PostgreSQL as a truly cross platform database system.

Robert Treat is a longtime open source user, developer and advocate. He helps maintain several PostgreSQL project Web sites, he contributes to a number of open source projects including phpPgAdmin, and he has been recognized as a "Major Developer" for his work within the PostgreSQL community. Treat has also written several articles for the PostgreSQL "techdocs" Web site and teamed with Jason Gilmore to write the book Beginning PHP and PostgreSQL 8.

Dig Deeper on Linux servers