Running & Installing PostgreSQL On Native Windows

From PostgreSQL wiki
Jump to navigationJump to search


Supported Platforms

What versions of Windows does PostgreSQL run on?

PostgreSQL is supported on Windows XP and above, at least as of version 9.0. It will run on 32 and 64 bit systems.

Versions of the server are not tested on new operating system versions that are released after a newer major version of the server was released. For example, Windows 7 was released after PostgreSQL 8.4, so PostgreSQL 8.3 will not be supported on it. Similarly, when the upcoming RHEL 6 is released, only PostgreSQL 9.0.x will be supported on it. We aim to support new versions of Windows in the PostgreSQL major version following their release at the latest.

For information about platforms supported by the One-click installer, please see the installer download page, off the main download page for windows.

For information on non-Windows platforms see the main FAQ and the main download page.

What Windows platforms are NOT supported?

The PostgreSQL installers are not tested or supported on:

  • Windows XP Embedded
  • Windows 2000
  • Windows NT 4
  • Windows NT 3.5.x
  • Windows 95/98/ME/3.x
  • Windows CE
  • Windows Mobile

These platforms are not supported. Please do not ask the mailing list for help with any of these platforms.

Please read the troubleshooting notes on installation on embedded versions of windows for some more information about embedded windows.

I heard that NT4 and Windows 2000 are supported. Is that true?

Although not officially supported, PostgreSQL may run on Windows NT4 and Windows 2000 with a few minor issues including:

  • The installer may not work correctly, thus you will need to install PostgreSQL manually from the binary .zip release, or compile it yourself.
  • 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 on NT4.
  • 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.

  • No testing is done on Windows NT 4 or Windows 2000, so newer versions may not work on these platforms.

Please do not ask for help with these obsolete platforms on the mailing list. Some of the professional support companies may be able to assist you, though.

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.

Is there a 64-bit build of PostgreSQL for Windows?

Yes. A 64bit Windows port was released for PostgreSQL 9.0.

32-bit builds of previous versions of PostgreSQL generally run fine on 64-bit Windows. Though they cannot practically use more than about 1GB of shared_buffers directly, they can still benefit from more than 4GB of memory because the Windows kernel will use that memory to cache disk reads.

What about 64-bit ODBC drivers?

At time of writing there was 64-bit support in the psqlODBC source code, but no official binary releases of 64-bit ODBC drivers. Check the psqlODBC website for details.

Installation

What do I need to install PostgreSQL on Windows?

See the PostgreSQL for Windows download page for various ways to download and install PostgreSQL on Windows.

The easiest way to install PostgreSQL on Windows is with the One Click installer package maintained by EnterpriseDB, which you can get from the page linked to above. This will install 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. A program called StackBuilder will be installed to help you download and install any additional components, like ODBC or JDBC drivers, that you may need.

What do I need to compile PostgreSQL from source code?

See the documentation for instructions on how to compile PostgreSQL for windows, including details about supported compilers and tools.

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.

Can I install PostgreSQL on a FAT partition?

Use of PostgreSQL on FAT32 file systems is not supported or tested, because FAT32 is a terrible file system on which to run any database.

PostgreSQL's number one priority is the integrity of your data. FAT and FAT32 filesystems simply do not offer the reliability or crash-safety required. 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 in rare cases 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. Do not use PostgreSQL on FAT32 in production.

The most common reason people ask this is when they have a USB key or external hard drive and they want to put a PostgreSQL database on it. Don't do it. You can format USB keys and external hard drives with NTFS, and you should do so if you want to run a database on them. FAT is not crash-safe, and removing a hard drive without using "Safely remove..." in Windows counts as a crash as far as the hard drive is concerned. Corruption is extremely likely. It is very important to reformat a removable drive with NTFS if it is to be used with PostgreSQL to store any data you care about.

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!

Why can't I select Unicode as an encoding?

Starting with PostgreSQL 8.1, the UTF-8 unicode encoding is fully supported on Windows. The Unicode ODBC driver supports UTF-16, and the JDBC driver also has full unicode support.

The PostgreSQL server does not support the 2-byte UTF-16 or 4-byte UTF-32 Unicode encodings for internal data storage or on-the-wire communication. Beause UTF-16 is the default encoding on Windows, and what people on Windows usually mean when they say "Unicode", you might expect this to be a problem, but in practice it's fine, as the ODBC and JDBC drivers take care of this. Programs using libpq directly need to be aware of this, but not much else does.

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 National 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

PostgreSQL and/or the installer crashes at startup, fails to start or hangs on start

By far most common reasons for installation and runtime problems with PostgreSQL on Windows are Windows Scripting Host issues, antivirus software problems and 3rd-party (non-Microsoft) software firewalls. People also sometimes have trouble with the postgres service account password.

The following articles cover these issues. Please read them and follow their instructions before asking for help with installer problems.

Antivirus software

If you have any antivirus software installed, you must exclude the data directories that are to be used by PostgreSQL and must exclude postgresql.exe process. If that still does not help, it may be required to completely uninstall the antivirus software from the machine.

Antivirus software can interfere with PostgreSQL's operation, because PostgreSQL requires file access commands in Windows to behave exactly as documented by Microsoft, and many antivirus programs contain errors or accidental behavior changes that cause these commands to misbehave subtly. Most programs do not care because they access files in fairly simple ways. Because PostgreSQL is continuously reading from and writing to the same set of files from multiple processes, it tends to trigger programming and design mistakes in antivirus software, particularly problems related to concurrency. Such problems can cause random and unpredictable errors, or even data corruption.

Antivirus software is also likely to dramatically slow down PostgreSQL's operation. For that reason, you should at least exclude postgres.exe and the data directories so the scanner ignores them.

What Anti-Virus software is compatible?

The systems used to build the Windows installers all run either Sophos AV or AVG Free Edition, and those systems pass a full set of PostgreSQL regression tests running those programs. Microsoft Security Essentials is also known to work.

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.

Software firewalls

If you have any 3rd-party firewall software installed on your machine, try either disabling it or uninstalling it. There's really no need for 3rd party firewalls on Windows XP and above, as the built-in firewall provided by Microsoft does an excellent job already. Some badly-written 3rd party firewalls do not uninstall correctly, so after uninstallation you might have to tell Windows to repair its network settings.

If you had a 3rd-party firewall and have now uninstalled it, make sure to turn Windows Firewall back on, as many products turn it off during installation and fail to turn it back on during uninstallation.

The installer exits with a runtime installation error?

The installer may exit with an error like: An error occured executing the Microsoft VC++ runtime installer. This can only happen on Windows.

There are two main reasons why this may occur:

1) The Windows Scripting Host is unable to execute VBscripts. This can occur if the scripting host is disabled (which is unusual), or if the installation is broken. A sign of this problem is a message like CScript Error: Can't find script engine "VBScript" for script "C:\.... It can often be resolved by re-registering the VBscript interpreter - click Start -> Run and enter the following and click OK: 

regsvr32 %systemroot%\system32\vbscript.dll

If that fails, on older versions of Windows you can try updating the scripting host.

Also, make sure that the registry value in HKEY_CLASSES_ROOT\.vbs\(Default) is VBSFile. [1]

2) The installer is unable to properly read and write files to the system TEMP directory. This can occur if the TEMP or TMP environment variables are set to non-standard values, and can be recognised by errors in the logfile indicating that scripts could not be executed or found. To resolve this issue, ensure the TEMP and TMP variables are set to their correct values.

Trouble with passwords for the postgres user

Dave Page wrote a blog post explaining what the different passwords are used for, and how to overcome common problems such as resetting them.

PATH environment variable

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!

Problems have also been observed when the PATH environment variable contains directories containing versions of libssl and/or libintl.

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.

You may also see related errors show up as "The database Cluster initialisation failed" during the One Click installer.

Check your install-postgresql log but it is usually related to permission errors. The following thread may help,

http://forums.enterprisedb.com/posts/list/2044.page#7503

Or alternatively, you can fix up the directory permissions and then manually restart the initcluster.vbs script like this for v9, 

cscript //NoLogo "<install_path>/installer/server/initcluster.vbs" "postgres" "postgres" "<password>" "<install_path>" "<data_path>" 5432 "DEFAULT"

Check the %COMSPEC% environment variable

Some Windows installations seem to land up with an incorrect %COMSPEC% environment variable that prevents the execution of initdb.

In a command prompt, run: 

echo %COMSPEC%

You should get something like: 

   C:\Windows\system32\cmd.exe

(Where C:\Windows is your Windows install location).

If you see something else, check the System control panel, and in the Environment section check for COMSPEC. If its value there is wrong:

  • Make a system restore point; and
  • Back up your computer; then
  • Change it in the system environment settings, so it points to the correct location of cmd.exe

You can test to make sure it's right by:

  • Opening a new cmd.exe prompt (not re-using one that was already open); and
  • Running "%COMSPEC%" /C "echo test ok"

It should print "test ok".

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.

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.

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.


How do I delete the service account - it's not listed under users!

Windows sometimes hide some accounts from the GUI tools, so they cannot be removed from there. This includes the auto-created service account for PostgreSQL (that may be left over from a previous installation). To delete this account, use the NET command on the commandline: 

NET USER <username> /DELETE

Where <username> is the windows login name of the user, for example postgres.

Common runtime issues

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.

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.

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.

I cannot run with more than about 125 connections at once, despite having capable hardware

When running as a service, you may experience failures with more than approximately 125 simultaneous connections. This can happen because some of the libraries that PostgreSQL is dependent on are dependent on user32.dll which allocates memory from an area known as the Desktop Heap. The desktop heap is assigned to each logon session, with non-interactive sessions typically being assigned 512KB. Each postgres process that runs typically consumes approximately 3.2KB of desktop heap, which coupled with other overhead can exhaust the allocated heap at somewhere around 125 connections. This doesn't happen when running from the command line (or more precisely, happens with much higher numbers of connections) because the interactive logon session is typically allocated 3MB of Desktop Heap.

You can increase the non-interactive Desktop Heap by modifying the third SharedSection value in the registry as described in this Microsoft Knowledgebase article. Note that this should be done with great care as specifying a value that is excessively high may prevent your system from booting.

Windows version-specific issues

Can I install a 32-bit PostgreSQL on 64-bit Windows?

Recent 32-bit versions of PostgreSQL (8.3 and newer) can be installed and used on 64-bit Windows XP and above, though they retain the 32-bit limits on maximum process address space (and thus shared memory).

You can connect to the 32-bit postgresql server from 64-bit programs on the computer the server is running on or other computers if a 64-bit libpq or psqlODBC driver is installed where the program is running.

Because 32-bit versions of the PostgreSQL server only install a 32-bit libpq and pgsqlODBC, only 32-bit programs on the computer the server was installed on can use the database unless a 64-bit ODBC driver or libpq is installed as an add-on.

Where is the PostgreSQL ODBC driver? I'm running 32-bit PostgreSQL on 64-bit Windows

You need to use the 32-bit ODBC administrator to set up data sources for 32-bit applications using 32-bit drivers.

Unless you've also installed a 64-bit version of psqlODBC, a 32-bit install of PostgreSQL will only have a 32-bit ODBC driver. The 32-bit ODBC driver may only be used by 32-bit programs, and will not show up in the 64-bit ODBC administrator.

This gets confusing because on 64-bit windows c:\windows\system32\odbcad32 is the 64-bit ODBC driver administrator, despite the name. This is a historical artifact of Windows development. Apparently many apps and installers depend on odbcad32.exe having that name and path, so Microsoft landed up being stuck with it despite the now-stupid name, which made sense back in the 16- to 32-bit transition but makes none now. It's the same reason the "system32" directory is still called that on 64-bit Windows. PostgreSQL can't do anything about this.

See: http://support.microsoft.com/kb/942976

You'll see in that article that the 32-bit ODBC administrator on 64-bit Windows is: 

%systemdrive%\Windows\SysWoW64\odbcad32.exe

You can launch this from "Start->Run" using the path above. You will see the PostgreSQL ODBC drivers in the 32-bit ODBC administrator.

You 'can not' use a 32-bit ODBC driver in a 64-bit application. That means that you'll still only be able to use the PostgreSQL ODBC driver with 32-bit applications unless you install a 64-bit ODBC driver as well.

Can I use a 64-bit ODBC program with a 32-bit PostgreSQL server?

Only if you have a 64-bit [2] driver installed. See the installation section.

Retrieved from "https://wiki.postgresql.org/index.php?title=Running_%26_Installing_PostgreSQL_On_Native_Windows&oldid=21567"