Installing PostgreSQL on Windows Server 2003

This Article covers installation of PostgreSQL Version 9.3 32-bit on Windows Server 2003 32-bit OS

Preflight

Item Description OS Platform Windows Server 2003 R2 32-bit PostgreSQL Version 9.3 32-bit PostgreSQL Service Account Username :postgres
Password: postgres Installation Directory
C:\PostgreSQL\9.3 Data Directory C:\PostgreSQL\9.3\data

@!:{This article presents the steps from the command line (cmd) perspective.

Launch PostgreSQL Installation

wpid368-wpid-media_1418426929034.png

Installer Filename: postgresql-9.3.5-1-windows.exe
Launch the installer
Next • Data Directory: C:\PostgreSQL\9.3\data • Password: postgre • Port: 5432 • Locale: Default locale • Next …

Create The PostgreSQL Service User & Initialize Database Instance

wpid369-wpid-media_1418454067590.png

Create the PostgreSQL User Account
[shell]net user postgres /add[/shell]

Set the password for the User Account to ‘postgres’
[shell]net user postgres postgres[/shell]

Register the PostgreSQL Windows Service and set logon as the postgres user
[shell]pg_ctl register -N postgres -U postgres -P postgres -D "C:/PostgreSQL/9.3/data"[/shell]

Set appropriate permissions on the PostgreSQL data directory
[shell]cacls c:\PostgreSQL\9.3\data /e /g postgres:F[/shell]

Initialize the database instance, specifying the data directory from above
[shell]initdb -U postgres -A password -E utf8 -W -D "C:/PostgreSQL/9.3/data"[/shell]

Start the PostgreSQL Service
[shell]net start postgres[/shell]

This covers the installation and default configuration of PostgreSQL on Windows Server 2003 R2

Post-Installation Notes

Ensure the postgreSQL bin folder is in Path environmental variable

[divider]

Appendix

[divider]

Troubleshooting

Troubleshooting Scenarios
Error Possible Cause Possible Solution When you attempt starting the PostgreSQL service, you encounter an error similar to: "The postgres service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service." The PostgreSQL Database Instance has not been initialized Initialize the database instance then try starting the service again
Additionally, you can verify that the PostgreSQL service account has adequate permissions on the data directory When you try executing any arbitrary PostgreSQL queries, you encounter an error similar to: "Execution of PostgreSQL by a user with administrative permissions is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromises. See the documentation for
more information on how to properly start the server.
You registered the PostgreSQL service with an administrative user De-register service
[shell]pg_ctl.exe unregister -N postgres
[/shell]
Register service as a non-administrative user account (e.g. postgres)
[shell]pg_ctl register -N postgres -U postgres -P postgres -D "C:/PostgreSQL/9.3/data"[/shell] You try to initialize the database instance but encount an error similar to: "initdb: directory "C:/PostgreSQL/9.3/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "C:/PostgreSQL/9.3/data" or run initdb
with an argument other than "C:/PostgreSQL/9.3/data".
You are trying to initialize the database instance, but the data directory is not empty Delete the contents of the data directory and retry initializing the database instance Upon initializing the database instance, you encounter errors related to permissions, similar to:
fixing permissions on existing directory C:/PostgreSQL/9.3/data … initdb: could not change permissions of directory "C:/PostgreSQL/9.3/data": Permission denied
The PostgreSQL service account does not have adequate permissions on the data directory Grant full permissions on the data directory to the PostgreSQL service account, e.g.:
[shell]cacls c:\PostgreSQL\9.3\data /e /g postgres:F[/shell]