Instalando y Ejecutando PostgreSQL en Windows de manera nativa

Plataformas Soportadas

¿Qué versiones de Windows se ejecutan en PostgreSQL?

PostgreSQL es soportado en Windows 2000, XP, 2003 y Vista. Se ejecuta en sistemas de 32 y 64 bits.

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.

Que hay acerca de 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?

The short answer at the time of writing is 'no'. However, the 32bit build of PostgreSQL works on 64bit platforms, and in fact there are good reasons why a 64bit build is less important with PostgreSQL than for some other software:

• PostgreSQL relies on the operating system to do much of the caching of data. Since the main limitation of running a 32-bit process is the total amount of memory being addressable, a system that is based on the database engine doing all the caching cannot address all the memory on a system with say 16Gb memory. For PostgreSQL, we leave much of that caching to the operating system which can address it, as long as the OS is 64-bit.
• PostgreSQL uses a multi-process architecture, not multi-threaded. In a multi-threaded database server, all client connections share the memory space, and are again limited by the total addressable memory region. With PostgreSQL, you could easily have more than 1Gb per backend (if necessary) without running out of memory, which significantly pushes away the point when you need to go to 64-bit.
• In some cases it may actually be better to run a 32-bit build of PostgreSQL to reduce memory usage. In a 64-bit server, every pointer and every integer will take twice as much space as in a 32bit server. That overhead can be significant, and is most likely unnecessary.

Instalación

¿Qué necesito para instalar PostgreSQL en 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, 2003 or Vista with the Windows Installer service installed. The installer will create a service account if required, and initialise the database cluster.

El instalador puede ser descargado desde here.

What do I need to compile PostgreSQL from source code?

The Windows compilation FAQ at FAQ_MINGW contains full details of compilation of the PostgreSQL source code on Windows systems using MinGW. PostgreSQL 8.3 and above can be built using Microsoft Visual C++ 2005 (2008 should also work, but is currently untested). See the documentation for details.

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.

Puedo instalar PostgreSQL en una partición FAT ?

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.

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, (UTF8) UNICODE encoding is fully supported on Windows. 8.0 is no longer supported.

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

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

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.

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

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.

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

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.