PostgreSQL Management on Windows

PostgreSQL is one of the oldest, most powerful, and arguably one of the most complicated databases to administer. It is the power user's database. Paradoxically its installation on Windows, in trying to ease deployment, makes system configuration so "plug-and-play" that almost no one seems to know how to make even the tiniest deviances from the default installation configuration. I've went through the pain of researching and experimentation, and it turns out that a moderate power user can relatively easily create extremely flexible PostgreSQL configurations on Windows, if only someone were to write down how to do it.

I'm using Windows 7 Professional 64-bit. This overview assumes you are using PostgreSQL 9.3, although much of what I describe will undoubtedly work on previous versions. One major change is that PostgreSQL 9.2 stopped creating a Windows system user named postgres, but I'm getting ahead of myself.

Default Installation

By default PostgreSQL will be installed in the following directory:

C:\Program Files\PostgreSQL\9.3

Binaries

Many command-line utilities talk about the "binaries" directory; that is here:

C:\Program Files\PostgreSQL\9.3\bin

Data Directory

The data directory is a larger grouping than database; the data directory can hold more than one database. In fact, the instructions for many PostgreSQL utilities will talk about the database cluster, and for most purposes they mean the database directory. For example, to upgrade a database you'll be required to create a new cluster; in practical terms this means you'll create and initialize a new data directory.

By default PostgreSQL installs all your data in subdirectory of the installation directory:

C:\Program Files\PostgreSQL\9.3\data

The installation program gives you an option to create the initial cluster in another data directory, but doesn't tell you how to move the data directory later or how to have multiple data directories at various times. It's not that hard, and is explained below.

System Service

In Windows you can view the system services by hitting Start and serching for "Services". (It is also available in Control Panel|System and Security|Administrative Tools|Services.) The installation program created a service named postgresql-x64-9.3, with its startup type set to "Automatic". Viewing its properties shows that its "path to executable" is the following:

"C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" runservice -N "postgresql-x64-9.3" -D "C:/Program Files/PostgreSQL/9.3/data" -w

This simply means that when the computer starts, Windows will automatically start a service that runs from the binary directory, pointing it to the data data directory where the cluster lives.

You'll note that under Log On As it says "Network Service". I previous versions it said .\postgres. This is because the PostgreSQL installer used to create a separate a separate Windows account named postgres, which apparently confused many users, as the cluster itself usually has a user named postgres. PostgreSQL 9.2 simplified things and switched to simply using the built-in "Network Service" instead.

Manually Starting and Stopping the PosgreSQL Service

Maybe you don't want the service starting each time you log into Windows. From the Services application, it's easy enough to edit the properties and change the startup type of the service to "Manual". Or you can enter the following on the command line. Important: you'll need to run the command prompt as administrator or you won't be able to make the change. Hit Start, right-click on Command Prompt, and select Run as administrator.

sc config postgresql-x64-9.3 start= demand

Note: the space after start= is necessary.

After setting the service to manual startup, you can manually start the service from the Services list. Or you can do it from the command prompt (as administrator):

net start postgresql-x64-9.3

You can stop the service like this (as administrator):

net stop postgresql-x64-9.3

Using Another Data Directory with the PostgreSQL Service

After installation, maybe you don't want to use the data directory in the program installation location. Make sure the service is running, and copy/move the default data directory somewhere else. Before starting the service, you'll need to change the "path to executable" used by the service (see above), which indicates the location of the data directory. You can find it in the registry. Run regedit and find the following key:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\postgresql-x64-9.3

Look at the ImagePath property name; you'll see that it reads:

"C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" runservice -N "postgresql-x64-9.3" -D "C:/Program Files/PostgreSQL/9.3/data" -w

Surprise! This the same as the "path to executable" used by the service. Change the value after -D to indicate the new location of your data directory, and then restart the service. Everything should work as it did before.

Manually Starting and Stopping PostgreSQL Without a Service

Once you are starting and stopping the server manually, and once you know how to change the data directory, you could theoretically use different data directories at different times. This is not an unlikely scenario; I personally have several clients using PostgreSQL, and I usually only have one client's data mounted at a time on my machine. I would prefer to start and stop PostgreSQL on a whim, and furthermore I would like to specify exactly which data directory I'm using at the time I start the server. Manually editing a registry each time would be a pain. Sure, I could create scripts and batch files and registry templates and eventually have an automated system for changing the registry and starting the service. But look closely at the "path to executable", and you'll see that the service is just a glorified way of starting the PostgreSQL from the actual control application in the binary directory.

It turns out you can forgo using the service altogether and just start PostgreSQL manually using pg_ctrl, like this, substituting your own data directory for D:\example\postgres\data:

"C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" start -D "D:\example\postgres\data" -w

When you're finished, stopping the server is just as easy:

"C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" stop -D "D:\example\postgres\data" -w

The -w option tells the application to wait until the server is fully started or stopped.

Using a Batch File to Start and Stop PostgreSQL

If you run these commands from a batch file, you'll still get a command prompt that doesn't go away, whether or not you use the -w switch. Even using the Windows command start with its /b switch doesn't seem to prevent a command prompt from appearing. The most you can do using the standard Windows tools seems to be to make the command prompt start minimized when starting the server from a batch file:

start "" /min "C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" start -D "D:\example\postgres\data" -w

This trick is not needed for stopping the server from a batch file, as the resulting command lines (from both utilities) will go away as soon as the server shuts down.

Creating a New Data Directory

Now that we can start and stop the server on a whim using the data directory of our choosing, how do we get the data directory in the first place? One option is to copy the initial data directory the installer created, perhaps even making a backup of it to use a template. But there's no need; it's simple enough to ask PostgreSQL to create a new cluster from scratch. The following command will create a data directory that is, as far as I know, equivalent to the default one provided by the installer:

"C:\Program Files\PostgreSQL\9.3\bin\initdb.exe" -A md5 -U postgres -W -D "D:\example\postgres\data"

The -A md5 option indicates that real passwords (hashed by MD5) should be used. Otherwise, the pg_hba.conf file in the new data directory will use trust access, which allows the system user to access the database as long as he/she has been authenticated by Windows—which may be what you want, but it's not the same security as the default installation provides. Specify the customary postgres user the -U postgres option. Adding the -W flag will make PostgreSQL ask you right away for a password for that user.

The server does not need to be running when you create a new cluster. After all, you'll need to start the server indicating the location of this new data directory before you can use it.

Upgrading a Data Directory

Creating a new data directory might be useful if you are upgrading a PostgreSQL database. It turns out that PostgreSQL changes the database format even between minor versions (e.g. 9.1 and 9.3), and it appears almost impossible to find information on the web on how to easily upgrade a PostgreSQL database. I personally have never been able to get pg_upgrade to work, with its dizzying number of binary and data directory options, even though one would think from community comments that pg_upgrade should be the easiest option. With the tools above it becomes a little easier to upgrade by simply dumping and reloading the database.

Besides specifying the data directory, you'll notice that the above command line invocations allow you to specify the the version of PostgreSQL to use as well simply by modifying the binary directory used. It turns out you can install multiple versions of PostgreSQL side-by-side on the same machine. If you change their services to start manually, you can start and stop each of them at will along with the data directory of choice.

First start up the old version of PostgreSQL pointing to the old data directory. Here I'll use version 9.1 as the old version and D:\example\postgres\old as the old data directory. I'll store the dump file in backup.sql.

"C:\Program Files\PostgreSQL\9.1\bin\pg_ctl.exe" start -D "D:\example\postgres\old" -w

Dump the old cluster into a dump file such as backup.sql:

"C:\Program Files\PostgreSQL\9.1\bin\pg_dumpall.exe" -U postgres > backup.sql

Interestingly it seems you'll need to enter the password several times, presumably for each of the databases that appaar in the dump file.

Stop the old server:

"C:\Program Files\PostgreSQL\9.1\bin\pg_ctl.exe" stop -D "D:\example\postgres\old" -w

Create a new data directory, such as D:\example\postgres\new, using the new PostgreSQL server:

"C:\Program Files\PostgreSQL\9.3\bin\initdb.exe" -A md5 -U postgres -W -D "D:\example\postgres\new"

Start the new server, using the new data directory you just created:

"C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" start -D "D:\example\postgres\new" -w

Use the main psql utility to process all the SQL commands in the dump, indicating the user you created for the new data directory, effectively loading the dump file:

psql -U postgres -f backup.sql

You should now have a new data directory using the new version of PostgreSQL with identical data to that of the old data directory using the old version of PostgreSQL.

When I did this, I noticed a warning that the postgres user already existed. Is there a way to create the data directory without specifying a user, and let the dump/load process take care of it? I can't say for sure; I'm sure the above process can be tweaked. But at least now the entire PostgreSQL installation on Windows isn't so "simplified" that it remains a mystery to all but the most expert of PostgreSQL power users.