Peripheral Links
Header And Logo
Site Navigation
Running & Installing PostgreSQL On Native Windows FAQ
Last updated: $Date: 2007/02/06 21:38:18 $
Current maintainer: Dave Page (dpage@postgresql.org)
The most recent version of this document can be viewed at http://pginstaller.projects.postgresql.org/faq/FAQ_windows.html.
The FAQ for compiling PostgreSQL on native Windows is at http://www.postgresql.org/files/documentation/faqs/FAQ_MINGW.html.
This FAQ deals only with the native Windows port of PostgreSQL. There is also a Cygwin port, with it's own FAQ at: http://www.postgresql.org/files/documentation/faqs/text/FAQ_CYGWIN.
Supported Platforms
1.1) What versions of Windows does PostgreSQL run on?1.2) I heard that NT4 is supported. Is that true?
1.3) What about Windows 95/98/ME?
Installation
2.1) What do I need to install PostgreSQL on Windows?2.2) What do I need to compile PostgreSQL from source code?
2.3) Why do I need a non-administrator account to run PostgreSQL under?
2.4) Can I install PostgreSQL on a FAT partition?
2.5) What filesystem permissions does PostgreSQL require?
2.6) Why can't I select Unicode as an encoding?
2.7) I installed in a non-english language, but all messages show up in english!
Common installation errors
3.1) PostgreSQL and/or the installer crashes at startup, fails to start or hangs on start3.2) The installer claims the specified account is an administrator, but it isn't!
3.3) I get the error "the user has not been granted the requested logon type at this computer" or similar
3.4) I'm getting permissions errors when installing/running initdb
3.5) I'm getting an error message that says PostgreSQL cannot be installed from a Terminal Services session
3.6) I changed the directory but PostgreSQL was still installed in the default directory
3.7) When upgrading the installer says I don't have permissions to install a service, but I'm logged in as administrator
3.8) I get an error message saying the installation package could not be opened
Common runtime issues
4.1) I'm getting "dynamic load error" when installing a procedural language4.2) I'm seeing a lot of postgres.exe processes even though I only started the server once
4.3) How do I set an environment variable
Supported Platforms
1.1) What versions of Windows does PostgreSQL run on?
PostgreSQL is supported on Windows 2000, XP and 2003. At the time of writing, it has only been tested on 32 bit systems.
1.2) I heard that NT4 is supported. Is that true?
Although not officially supported, PostgreSQL will run on Windows NT4 with a few minor issues including:
- The installer will not work correctly, thus you will need to compile and install the code manually.
- PostgreSQL uses a feature in the NTFS filesystem called 'reparse points' to implement tablespaces. Reparse points are not available in NT4, hence tablespaces cannot be used.
- There is no 'runas.exe' included in Windows NT4 as standard, making it difficult to start PostgreSQL from an administrative account.
It should also be noted that very little testing has been done on NT4.
1.3) What about Windows 95/98/ME?
PostgreSQL requires functionality that is not available on these platforms and will not run on them. If you need to run PostgreSQL on these platforms, you can look at the Cygwin port, which has basic support for 9x platforms.
Installation
2.1) What do I need to install PostgreSQL on Windows?
The easiest way to install PostgreSQL on Windows is with the Windows Installer package available from the PostgreSQL FTP site and mirrors. This will install a precompiled version of PostgreSQL along with pgAdmin (a graphical administration and management program), a selection of 'contrib' modules to provide additional specialised functionality, and a choice of procedural languages.
To use the installer, you need a computer running Windows 2000, XP or 2003 with the Windows Installer service installed. The installer will create a service account if required, and initialise the database cluster.
The Installer may be downloaded from here.
2.2) What do I need to compile PostgreSQL from source code?
The Windows compilation FAQ at http://www.postgresql.org/files/documentation/faqs/FAQ_MINGW.html contains full details of compilation of the PostgreSQL source code on Windows systems.
2.3) Why do I need a non-administrator account to run PostgreSQL under?
When a hacker gains entry to a computer using a software bug in a package, she gains the permissions of the user account under which the service is run. Whilst we do not know of any such bugs in PostgreSQL, we enforce the use of a non-administrative service account to minimise the possible damage that a hacker could do should they find and utilise a bug in PostgreSQL to hack the system.
This has long been common practice in the Unix world, and is starting to become standard practice in the Windows world as well as Microsoft and other vendors work to improve the security of their systems.
Note, that with the release of PostgreSQL 8.2, it is possible to run under a administrative account. PostgreSQL 8.2 and above are able to irrevocably give up administrative rights at startup thus ensuring the rest of the system remains secure in the extremely unlikely event that PostgreSQL becomes compromised.
2.4) Can I install PostgreSQL on a FAT partition?
PostgreSQL's number one priority is the integrity of your data. FAT and FAT32 filesystems simply do not offer the reliability required to allow this. In addition, the lack of security features offered by FAT make it impossible to secure the raw data files from unauthorised modification. Finally, PostgreSQL utilises a feature called 'reparse points' to implement tablespaces. This feature is not available on FAT partitions.
The NTFS file system is a journalled filesystem offering much better reliability and crash recovery. In addition, it has a comprehensive access control system and offers the reparse point functionality used by PostgreSQL.
For this reason, the PostgreSQL installer package will not initialise a database cluster on anything but an NTFS partition. The server and utilities may be installed on any partition type.
It is recognised however, that on some systems such as developer's PCs, FAT partitions may be the only choice. In such cases, you can simply install PostgreSQL as normal, but without initialising the database cluster. When the installation has finished, manually run the 'initdb.exe' program on the FAT partition. Security and reliability will be compromised however, and any attempts to create tablespaces will fail.
2.5) What filesystem permissions does PostgreSQL require?
The PostgreSQL service account needs read permissions on all directories leading up to the service directory. It needs write permissions only on the data directory. Specifically, it should not be granted anything other than read permissions on the directories containing binary files. (All directories below the installation directory are set by the installer, so unless you change something, there should be no problem with this).
PostgreSQL also needs read permissions on system DLL files like kernel32.dll and user32.dll (among others), which is normally granted by default, and on the CMD.EXE binary, which may in some scenarios be locked down and need opening.
If you are running PostgreSQL on a multi-user system, you should remove the permissions from all non-administrative users from the PostgreSQL directories. No user ever needs permissions on the PostgreSQL files - all communication is done through the libpq connection. Direct access to data files can lead to information disclosure or system instability!
2.6) Why can't I select Unicode as an encoding?
NOTE! In PostgreSQL 8.1, (UTF8) UNICODE encoding is fully supported on Windows. This entry is only valid for version 8.0.
In PostgreSQL, "UNICODE" means "UTF8". UTF8 is not properly supported on Windows, and therefore cannot be used in version 8.0. The installer will allow you to select any encoding that is supported by both PostgreSQL and your version of Windows.
The Unicode problem was explained by Aleksander Kmetec in a post to the pgsql-hackers mailing list:
Because Postgres relies on the operating system for some string related functions, the OS needs to support the same encoding as the one that is used as the database encoding. Unfortunately, Windows does not support some encodings that are available as server-side encodings for PG.
Here is a short example in case the previous paragraph doesn't make much sense: with a UNICODE database (actually UTF8) you need to use a compatible locale when running initdb; in my case that's "sl_SI.utf8" (on Linux) or "Slovenian_Slovenia.65001" (on Windows).
65001 is Windows codepage number for utf8; except it's not a really a valid codepage. The document originally at http://www.sharmahd.com/tm/codepages.html, but unfortunately now removed, stated that:
"65000 (UTF-7) and 65001 (UTF-8) are pseudo codepages. There are no corresponding NLS files. The code page IDs can only be used with WideCharToMultiByte( ) and MultiByteToWideChar( ) API calls."
This means that UPPER(), LOWER() and ORDER BY do not work correctly for unicode databases. Currently it's not even possible to run initdb with a locale which uses 65001 encoding. A small change to initdb enabled me to set LC_COLLATE to Slovenian_Slovenia.65001, but the sort order was still badly messed up, which makes sense considering the above quote.
After some checking I came up with this list of encodings which are supported by PG, but not mentioned anywhere as supported by Windows:
- UTF8
- EUC_CN
- EUC_TW
- LATIN6 (ISO 8859-10/ECMA 144)
- LATIN7 (ISO 8859-13)
- LATIN8 (ISO 8859-14)
- LATIN10 (ISO 8859-16/ASRO SR 14111)
2.7) I installed in a non-english language, but all messages show up in english!
The language choice made during the installation only selects which language is used by the installer. To change the language of the messages of the installed product, make sure you have installed the Natural language support feature. Then edit your postgresql.conf file and change the value of the lc_messages parameter to the language you want.
Common installation errors
3.1) PostgreSQL and/or the installer crashes at startup, fails to start or hangs on start
By far most common reasons for this are antivirus and firewalls. If you have any firewall software installed on your machine, try either disabling it or uninstalling it. If you have any antivirus software installed, you must disable it for the directories that are to be used by PostgreSQL. If that still does not help, it may be required to completely uninstall the software from the machine.
Specific issues have been reported with the nod32 antivirus product. If you are using this product, add "postmaster.exe" to the list of excluded processes (available under advanced options). This has been reported to fix the problem.
Specific issues have also been reported with McAfee and Panda anti-virus software and NetLimiter network monitoring software. While some people do have PostgreSQL working with these software packages, there is no specific or even recommend solutions that have not worked in some cases, so the issues would appear to be installation specific, sometimes even requiring uninstallation.
There is also an issue if you have installed cygwin, and the cygwin\bin directory is present in the system PATH variable. There are DLL files in the cygwin directory related to interpreted languages (TCL, perl, python) that contain bugs that can cause the installer or the installed version of PostgreSQL to either hang or crash. Remove the cygwin\bin directory from your path before running the installer!
3.2) The installer claims the specified account is an administrator, but it isn't!
Most likely, the specified account is an administrator or power user, even though you aren't aware of it. The check used by the installer specifically checks for membership in the Administrators or Power Users group. Work your way backwards using Local Users and Groups - open the Administrators group, see who's a member. Then check any groups (domain or local) that are a member of the Administrators group, and any groups members of that group etc. PostgreSQL checks any level of nested groups.
3.3) I get the error "the user has not been granted the requested logon type at this computer" or similar
Make sure the specified PostgreSQL account has the "Log on as a service" and "Log on locally" rights. The "Log on locally" is only required for the install part, and can be removed once the installation is completed if security policies require it. (Rights are granted and revoked using the "Local Security Policy" MMC snapin. "Log on locally" is default, and "Log on as a service" will normally be granted automatically by the installer).
If you still have this problem, enable auditing (also using the "Local Security Policy" snapin) and let us know what other rights were required in your setup.
Note that if your computer is a member of a domain, the settings of the security policies may be controlled at the domain level using Group Policy.
3.4) I'm getting permissions errors when installing/running initdb
Make sure the PostgreSQL service account has permissions on the directories leading up to the one you have installed into. The installer will set permissions on the install directory but not on parent directories of it. See question 2.5 for a list of the required permissions.
3.5) I'm getting an error message that says PostgreSQL cannot be installed from a Terminal Services session
This is unfortunately so. The PostgreSQL backend won't run from a TS session, and in order to do initdb the installer has to start a standalone backend. Therefore, installation has to be performed from the console. Note that if you are using Windows Server 2003, you can get remote access to the actual console and not just an administrative session. To do this, start the Remote Desktop Connection by executing mstsc /console, and then connect as usual. This will lock the local console of the server and give you control over that session. In this scenario, PostgreSQL should install just fine.
3.6) I changed the directory but PostgreSQL was still installed in the default directory
Make sure you changed the directory of the root feature. The PostgreSQL installer permits changing the directory of some individual features. If you change the root feature ("PostgreSQL"), any subfeatures (such as "Database Server") will automatically inherit this value as default, but if you only change a subfeature the rest of the installation will remain in the default location.
3.7) When upgrading the installer says I don't have permissions to install a service, but I'm logged in as administrator
The workaround for this issue is to first uninstall the previous version. Note that this will not remove your data!! Then reinstall the new version, and make sure you use the exact same directories. That should solve the problem. Note that this is only possible in minor version upgrades (such as 8.0.1 to 8.0.2) that do not require a dump/reload.
3.8) I get an error message saying that the installation package cannot be opened
This can be caused by two things. The most likely one is that you double-clicked on the MSI file in the ZIP file. For the installation to work, you must uncompress the whole ZIP file to a temporary directory and then run it. The other reason is that your download is broken. Try to download again, using a different mirror.
Common runtime issues
4.1) I'm getting "dynamic load error" when installing a procedural language
Most commonly this means that the actual language DLL for the procedural language is missing. The PostgreSQL DLLs only contains the language bindings, and require the language distribution DLLs to be present in the system PATH. For a list of the current required DLLs for the different procedural languages, please see the installation instructions.
To find out exactly which DLL files are missing, you can use the depends tool from Microsoft. It is available in the Windows Support Tools, that are on the Windows CD as a separate install. Just run depends plpython.dll (for PL/python) to show which imports are missing.
4.2) I'm seeing a lot of postgres.exe processes even though I only started the server once
This is normal. PostgreSQL uses a multi-process architecture. In an empty system you will see anything from two to five processes. Once clients start to connect, the number of processes will increase.
4.3) How do I set an environment variable
PostgreSQL uses environment variables for several settings. To change an environment variable in most Windows versions, go into Properties for My Computer, then into Advanced. Note that there are two sets of environment variables - one for the system which applies to all users and one for the current user. If you want an environment variable to affect the PostgreSQL service, you must change the system variable. After changing a system variable, you must restart the service.