Are you looking for an advent calendar? You found it!

Installing PostgreSQL 9.1 to Windows 7 from the Binary Zip Distribution

Washing instructions tag

Today I was helping a colleague of mine to install and configure PostgreSQL 9.1 to his laptop which was running on Windows 7. We had several problems with the one click installer and after the installation process was finished, we could not start the PostgreSQL database.

Since we could not solve this problem in a reasonable amount of time, we had to use the binary zip distribution. The installation process was quite straightforward but it took some time because I could not find a decent tutorial about it. This blog entry is written to fill that void. Also, even though this blog entry describes the installation process of PostgreSQL 9.1, these instructions should be valid for older versions of PostgreSQL as well.

The required steps are described with more details in later Sections of this blog entry.

Preparing the PostgreSQL 9.1 Installation

This process has two steps:

  1. Download and install the Microsoft Visual C++ 2008 redistributable package.
  2. Download and unpack the PostgreSQL binary distribution.

These steps are described with more details in the following.

The first step (installing the Microsoft Visual C++ 2008 distributable package) is not required anymore. However, I will leave it here because you still need to do it if you install older PostgreSQL versions by using the binary zip distribution.

I am not sure which PostgreSQL versions require the Microsoft Visual C++ 2008 distributable package but if I remember correctly, I had to install it when I installed PostgreSQL 9.1.

Downloading the Microsoft Visual C++ 2008 Redistributable Package

The first step is to download and install the Microsoft Visual C++ 2008 redistributable package.

Downloading and Unpacking the PostgreSQL binary distribution

The second step is to download the binary zip distribution.

After you have downloaded the binary zip distribution, you can unzip the binary distribution to the preferred directory. When this is done, the target directory should contain a directory called ‘pgsql’. This directory contains the binaries of PostgreSQL 9.1. I will refer this directory simply as POSTGRESQL_ROOT (Remember to replace the string POSTGRESQL_ROOT with the actual directory path of you installation when following these instructions).

The next step is to create the data and log directories for your PostgreSQL installation. This is done by creating the following directories to the POSTGRESQL_ROOT directory:

  • The ‘data’ directory contains the data files of your PostgreSQL installation.
  • The ‘log’ directory contains the logs of your PostgreSQL installation.

You have now finished the needed preparations and can move on to the next phase of the installation process.

Configuring the PostgreSQL 9.1 Installation

The next step is to create a new PostgreSQL database cluster. You can do this by using the initdb command which is found from the POSTGRESQL_ROOT\bin directory. You can create the database cluster by running the following command from the bin directory of your PostgreSQL installation:

initdb -U postgres -A password -E utf8 -W -D POSTGRESQL_ROOT\data

The command line parameters of the initdb command are described in following:

  • -U postgres means that the superuser account of your database is called ‘postgres’.
  • -A password means that password authentication is used.
  • -E utf8 means that the default encoding will be UTF-8.
  • -W means that you will enter the superuser password manually.
  • -D POSTGRESQL_ROOT\data specifies the data directory of your PostgreSQL installation.

After you have successfully created the database cluster, your PostgreSQL installation is ready to be used. You can start and stop your database instance by using the following commands:

The database can be started by running the following command:

"POSTGRESQL_ROOT/bin/pg_ctl" -D "POSTGRESQL_ROOT/data" -l "POSTGRESQL_ROOT/log/pgsql.log" start

The database can be stopped by running the following command:

"POSTGRESQL_ROOT/bin/pg_ctl" -D "POSTGRESQL_ROOT/data" -l "POSTGRESQL_ROOT/log/pgsql.log" stop

To make things as easy as possible, you should create new shortcuts to your desktop and use them for starting and stopping the database server.

Note: If you want to run PostgreSQL as a service, you should run the following command:

POSTGRESQL_ROOT/bin/pg_ctl.exe register -N "postgresql" -U "NT AUTHORITY\NetworkService" -D "POSTGRESQL_ROOT/data" -w

After you have done this, you can start the service by using the Services panel.

Congratulations

Your PostgreSQL 9.1 installation should now be functional. The next step is to start the database server by using the created shortcut and verify that you can connect to your database instance. You can use the PgAdmin database management tool for this task and congratulate yourself for a job well done.

If you enjoyed reading this blog post, you should follow me on Twitter:

About the Author

Petri Kainulainen is passionate about software development and continuous improvement. He is specialized in software development with the Spring Framework and is the author of Spring Data book.

About Petri Kainulainen →

129 comments… add one

  • I love this article, as I need to do the exact requirements.
    But, I think there is one thing missing from this.
    After I extract the archive, and having running the initdb, it seems that no password is required. So the initdb command should look like this

    initdb -U postgres -E utf8 -W -d POSTGRESQL_ROOT\data

    It make the initdb process able to start.

    cmiiw

    Reply
    • Hi,

      thanks for your comment. It is nice to hear that I could provide information which was useful to you. However, I would like to know if you had problems running the command which I provided in my blog entry?

      The reason why I am wondering this is that your command is almost equal to mine except the fact that you do not specify the used authentication method by using the -A command line parameter. If the authentication method is not given, Trust authentication is used for local users specified in the pg_hba.conf as stated in the PostgreSQL documentation:

      http://www.postgresql.org/docs/9.1/static/app-initdb.html

      This means that local users do not need to use password when connecting to the database. That is why I used -A parameter to declare that all users need to use password authentication when connecting to the database. Of course if you trust all your local users using trust authentication is a good option.

      Did this answer to your question? Feel free to drop me a comment if there is still something which needs to be clarified.

      Reply
  • I am stepping through your instructions here and believe me they are some of the best out there for Postgres installs. I am running into one issue however. When I try to kick up the service I am running into an Access is denied error.

    Does not even create a log file. I am running on Windows 7.

    Reply
    • Hi Delray,

      If this error occurs when you are trying to start the PostgreSQL, the problem might have got something to do with user permissions. If the log file is not created, maybe the user does not have write permissions to the directory in question. Which user are you using?

      Reply
    • First of all, thank you very much Petri for this blog post.
      @DelRay Davis: I was having the same issue (“Access is denied” when trying to start the services from services window) and I’m pretty sure it is caused by the service getting started by a user that has administrative privileges. What I did to get it to start was right click on the service in the Services window and choose properties then I went to the Log On tab and select “Local System account” instead of “This account: NT USER…”. This fixed the issue for me and I was able to log in.

      @Petri: I wonder if the
      POSTGRESQL_ROOT/bin/pg_ctl.exe register -N “postgresql” -U “NT AUTHORITY\NetworkService” -D “POSTGRESQL_ROOT/data” -w
      command should actually be modified for a different type of user. I haven’t actually l tested a solution from the command line, I just know that changing to “Local System account” from the Services window did work for me.

      P.S. I know DelRay has probably since moved on. This is post is mainly for people that end up here because they had a similar problem.

      Reply
      • @BenRobot,

        Thank you for sharing your solution to Delray’s problem. I am sure that it will be helpful to other people who end up here because they have the same problem.

        About your question: To be honest, I don’t know if the command should be modified (I have zero experience from Windows administration). However, if changing the user account type solved, your problem, it is probably the right to do. Does your local user have admin rights?

        Reply
        • @Petri
          Honestly, I’m not sure what it means to select “Local System Account”; I can’t comment on whether this refers to my own local user or some generic “system” account. To answer your question, yes, my local user does have admin rights.

          Reply
          • @benrobot

            I was just wondering if you know how the command in question should be changed. It seems logical but I don’t know how it should be changed (if it should be changed at all).

  • Hi Petri,

    Thanks for wonderful and concise writeup.. great help.

    Reply
    • Hi Ank,

      Good to hear that this entry was useful to you.

      Reply
  • Got stuck 2 days trying to install pgsql in a Windows 7 Home Premium x64 before finding your precise information. Thanks a lot !

    Reply
    • It is great to hear that you find this tutorial useful!

      Reply
  • Excelente tutorial.

    Reply
  • should not it be “-D POSTGRESQL_ROOT\data” instead of “-d POSTGRESQL_ROOT\data”. -d is debug

    Reply
    • Vitalii,

      You are right! I updated the command. Thank you for letting me know about this mistake!

      Reply
  • Thanks for the instructions. I was getting ready to kill myself because the one-click installer kept hanging in “initialising database cluster” step and doing other weirdness. Following these steps resulted in successful installation and I may live to see another day.

    Reply
    • Jansu,

      The PostgreSQL one-click installer seems to be very unstable. I have never managed to get it work and I have tried it with several different computers. That is one reason why I decided to write these instructions down in the first place. I am happy to hear that I could help you to live a little bit longer ;)

      Reply
      • Regarding the unstable installer….TOTALLY AGREE:
        When used the PostgreSQL 9.3.3 installer (or maybe just PostgreSQL 9.3) I got an error during the install. After much search and finally getting to the bottom of the problem (thanks to this page) I found that I could do the following to get the installer to work:
        1. Run PostgreSQL 9.3.3 installer (yes, it gives an error but just let it finish).
        2. Open Services (Run: services.msc), right click the PostgreSQL 9.3 (or whatever) service and go to Properties.
        3. In the Properties window go to the Log On tab and change to “Log on as: ‘Local System account'”
        4. Re-run the installer (it will act as if it is upgrading your version of PostgreSQL) but this time is completes without errors.
        5. Something weird is that if you go back to check on the Services’ Properties the Log On is back to “Log on as: ‘This account: Network Service’ “…but now it works.

        Hope this helps.

        Reply
        • @BenRobot,

          Thanks for sharing this. I haven’t been using Windows for a few years. Now I can check out your instructions if I have install PostgreSQL to a computer which runs Windows.

          Reply
  • Thanks for this post. Very usefull. The only thing missing is how to run as a service. Can you provide some instructions about this? Thank you.

    Reply
  • Hello, I have created mine PostgreSQL DB, but after I do all in the description I use command
    psql “H:\xampp\POSTGRESQL_ROOT/bin”, to connet to mine DB, after command is done I am asked for password, and after I type the password (100% correctly, the password witch I made in the “initdb -U postgres -A password -E utf8 -W -D H:\xampp\POSTGRESQL_ROOT\data” step, I was asked to type new password) I get the error.
    c:\>psql “H:\xampp\POSTGRESQL_ROOT/bin”
    Password:
    psql: FATAL: password authentication failed for user “Metalas”

    What I Am doing wrong? Sorry for my poor English.

    Reply
    • Hi Zhilwinas,

      The password which you set by using the initdb command is the password of the superuser account (in this case postgres). The error message states that you are trying to connect to the database as Metalas. That is why the password authentication fails.

      If you want to connect to your database by using another user account, you have to create a new user account. You can use the createuser command for this purpose.

      Reply
  • Hi Petri,

    i Just read your article. It is really a nice one. We have to do many postgre installations on different machines and os. some of them fails and there is no solution. errors such as access to postgresql.conf, database cluster, non fatal error occurs. i will give a try to such one machine tomorrow morning.

    Thank you very much for such nice article. i am sure it will really solve all our problems.

    Reply
  • Hi,
    The postgresql oneclick installer also kept failing on my windows 7 machine. So I tried your method but it fails during database cluster initialisation with the same error message from the log file.

    initializing pg_authid … REVOKE ALL on pg_authid FROM public;
    FATAL: could not find relation mapping for relation “pg_database”, OID 1262

    Any ideas?

    Reply
    • I have not experienced this error myself. I will investigate and see if I can find something.

      Reply
  • thanks petri!!! after spending 4-5hrs, finally i found this page and i did it.. incomplete oneclick installer made my day worst.. thanku.. :)

    Reply
  • Hi Petri,

    thank you for this article it was very useful. One thing that caught me out was the need for Microsoft Visual C++ 2008 Redistributable Package. The error message from calling the initdb command was not very informative but the log entry in the windows application log did point this out.

    Thanks again,
    Colin

    Reply
    • Hi Colin,

      Actually I missed that one (Microsoft Visual C++ 2008 redistributable package) too at the first time when I installed PostgreSQL to Windows 7 from the binary zip distributable. That is one of the reasons why I wrote this blog entry. It is nice to see that is useful to other people as well.

      Reply
  • It is getting stucked at
    “LOG: autovacuum launcher started”

    Reply
    • Could you be a bit more specific? What is getting stucked at? What PostgreSQL version are you trying to install?

      Reply
  • thank you very useful information for installation.

    i have notice that latest version of pgAdmin III not working with latest version of PostgreSQL (9.2) you need to install old version like (1.16) working without problems.

    @DelRay Davis
    i think your problem maybe you are trying to start the service and there is already same service name you need to register new service with other name or delete old service

    my question what is the minimal files that need to run database i want make very small and i will delete all unnecessary files ?

    Regards

    Reply
    • @Abuabdullah

      You are welcome. It is nice to hear that this blog post was useful to you.

      I tried to find some information about creating a minimal installation of PostgreSQL and the only thing which I was able to find was this StackOverflow question: Making Postgres SQL minimal size. How? @ StackOverflow.

      It is not much but I hope that it is helpful to you. By the way, why do you want to make your PostgreSQL installation as small as possible?

      Reply
  • Hi for those of you who want to run it as a service use:
    POSTGRESQL_ROOT/bin/pg_ctl.exe register -N “postgresql” -U “NT AUTHORITY\NetworkService” -D “POSTGRESQL_ROOT/data” -w

    Reply
    • Thank you for the tip! I really appreciate it. I will update the blog post and add this information to it.

      Reply
  • Great! Crystal clear!!
    It helped me to finish my job within 2 minutes.
    Perfect post.. I haven’t seen such a clear, simple & easy post in years.
    As they say, If you can’t explain simply, you have not understood really!

    Please keep it up.. And never leave this simplicity..
    Thank you very much.
    God bless you!!

    Reply
    • You are welcome. I am happy to hear that I could help you out.

      Reply
  • Hi,
    I did as your said same all the steps.

    When I am trying to start the server, it is not starting.

    log file has the following error message:

    postgres cannot access the server configuration file “C:/Program Files/PostgreSQL/pgsql/data/postgresql.conf”: No such file or directory

    Do I need to create this file (or) copy this file from any where?

    Thanks for your help in advance.
    Kolla

    Reply
    • Hi,

      The problem is that PostgreSQL cannot find its configuration file from the configured data directory which is weird because this file should have been created when you initialized your database cluster.

      I have some questions for you:

      • Is the home directory of your PostgreSQL installation: “c:/Program Files/PostgreSQL/pgsql”?
      • When you initialized the database cluster, was the data directory created under the home directory of your PostgreSQL installation?
      • Did you configure the directory “c:/Program Files/PostgreSQL/pgsql/data” as the data directory of our PostgreSQL instance (either in the startup script or when you configured to PostgreSQL run as a service)?
      • Did you try to start the PostgreSQL instance by using a startup script or did you configure it to run as a service?
      • Which version of PostgreSQL were you trying to install?
      Reply
  • This was fantastic, and saved me so much pain! The installer bombed out trying to start the service. This is a really, really needed resource on the web, and I was lucky to find it.

    Two comments:

    One: your code for starting the service works, but I had to be careful. I copied and pasted into notepad, changed my root directory, then pasted back to the command prompt where it failed. I quickly discovered that you have “smart quotes” in that code snippet, and you don’t in the other code snippets, which worked just fine. Consider editing that to have normal double quotes.

    Two: Once you install the service, I had to start it from the Services panel, as it doesn’t just start up on it’s own, evidently. Consider adding that tidbit as well.

    But this guide took me from no install to a working install. Thank You!

    Reply
    • I am happy to find out that I could help you out.

      Also, I want to thank you for the improvement ideas which you mentioned in your comment. I will update blog post after I finish writing this comment.

      Reply
  • Hi,

    I’m a student and I’m trying to use postgres for one of my academic projects. I did everything in this tutorial but I get the following issue (in the link).

    http://stackoverflow.com/questions/16370489/cannot-start-postgres-server

    Can you give me solution for this?

    Reply
  • Thank you so much Petri! I am new to Postgres. Got some time to try it today and did want to install it using the installer to possible screw up my mysql and sqlserver express. You post and follow-up posts are very helpful to me. Great article from a warm-hearted man! 2 Thumbs up! :)

    Good day!

    Reply
    • Hi Daniel,

      Thank you for your comment! It is great to hear that I could help you out.

      Reply
  • Hi Petri,

    Thanks for this great blog.

    Regarding the Visual C++ Redistributable Package, do I need to install it
    before following your steps to install Postgres.

    Thx in advance
    Hassib

    Reply
    • Hi Hassib,

      You need to download and install the Visual C++ redistributable package before installing PostgreSQL. I should probably update my blog post so that this would be described in it. Thanks for pointing this out.

      Reply
  • Hi Petri,

    Thanks for your prompt answer.

    Cheers,
    Hassib

    Reply
    • You are welcome!

      Reply
  • Hi
    I have installed PostgreSQL 9.1 and installed PostGIS 2.0.3 using Stack Builder..
    When i create new database in pgAdmin using PostGIS_template_20.. I got an error..
    ERROR : source database “template_postgis_20″ is being accessed byother users
    DETAIL :there are one other session using the database

    Reply
    • The website of Boston GIS has a nice tutorial which describes how you can install PostGIS to a computer which runs the Windows operating system.

      I took a quick look at it and it seems that if you are using PostgreSQL 9.1 or newer, you should create your database by using the default template. Check the tutorial for more details about this.

      I hope that the tutorial will help you to solve your problem.

      Reply
      • Petri,

        Thanks a lot for this . you have saved me.

        Reply
        • You are welcome!

          Reply
  • Thanx Mate for the perfect explanation. Exactly what I needed :D

    Reply
    • You are welcome!

      I am happy to hear that this blog post was useful to you.

      Reply
  • i don’t understand the part of folders, where create the “data” folder and “log” ty.

    Reply
    • Hi Rafael,

      you can pretty much create these directories anywhere you want as long as you remember to replace the POSTGRESQL_ROOT placeholder with the correct directory path.

      However, this tutorial assumes that these directories are created to the root directory of your PostgreSQL installation. Let’s assume that you have extracted the binary distribution to the directory c:\postgresql. If you follow the approach used in blog post, it means that

      • The data directory should be c:\postgresql\data
      • The log directory should be c:\postgresql\log

      I hope that this answered to your question.

      Reply
  • What about PostgreSQL 9.3.0?

    Reply
    • I am not sure if these instructions are valid for the version 9.3.0. I can try them out next week and report my findings.

      Reply
      • I tested these instructions by using PostgreSQL versions 9.2.4-1 and 9.3.0 RC1, and I was able to install both versions without any problems.

        Reply
  • Hi,
    I successfully installed version 9.2.4 and registered it as a service. Now I would like to install the spatial extension, but the attached Stackbuilder is not showing my new cluster and I can’t do it. Anyone else had this problem? Is there any configuration file for stackbuilder?

    Reply
    • I haven’t personally ran into this problem and I couldn’t find anything interesting from Google either. Have you tried installing the extension manually?

      Reply
      • Yes, I ended up downloading the manual installer and it worked for me.

        Reply
        • Great!

          Reply
  • God bless you.

    Reply
    • Thanks! I assume that you liked this blog post :)

      Reply
  • Thanks Petri,

    Recently I have installed successfully and started using Postgresql in my windows 7 – 64 bit laptop with your help. No other site could help me and I faced a lot of issues earlier.

    Best Regards,
    Manna
    New Delhi, India

    Reply
    • Hi Manna,

      I am happy to hear that I could help you out!

      Reply
  • Hey Petri first of all thanks for this post!

    Here’s my problem: Well for one, I’m pretty new when it comes to cmd prompt, which makes this a little hard (am setting this up for use of the poker tracker 4, which is a online poker HUD that requires the installation of the postgreSQL), but I’ve successfully managed to create the database cluster but am unable to actually start it…i entered the following in cmd after creating the database:

    “C:\program_files\pgsql\bin\pg_ct1″ -D “C:\program_files\pgsql\data” -1 “C:\program_files\pgsql\log\pgsql.log” start

    and all i got was “the system cannot find the path specified”. I apologize in advance if i’m just doing something really dumb but i honestly have no knowledge even in the basic programming and installing this using the said method is kind of out of desperation, please help.

    Thanks in advance.

    Reply
    • Hi Gordon,

      I don’t have a Windows computer right now so I cannot check this but isn’t the correct directory ‘program files’ (no underscore, just one space between words)?

      If so, you should change the command to this:

      “C:\program files\pgsql\bin\pg_ct1″ -D “C:\program files\pgsql\data” -1 “C:\program files\pgsql\log\pgsql.log” start

      I assume that you unzipped the binary distribution to directory path: c:\program files\pgsql which is the root directory of the installation.

      If this is not the case or changing the command doesn’t solve your problem, drop a new comment and I will see if I can help you out.

      By the way, good luck to the online poker tables!

      Reply
  • Gordon,

    Were you able to find the fix to your problem? I’m having the same issue and getting:
    “Problem running post-install step. Installation may not complete correctly – Error reading file C:/Program Files/PostgreSQL/9.3/data/postgresql.conf”
    warning message.

    Reply
  • Thanks, Petri. It works well

    Reply
    • Lan,

      you are welcome!

      Reply
  • Hi, nice post.
    Im trying to follow your steps (after failing with installer from ED).
    And i cant available for creating database cluster in my computer.
    In the CMD console i get next messages:
    selecting values for max_connections… “‘” is not recognized as internal or external command operable program or batch file.
    And so a lot of lines like this: “‘” is not recognized as internal or external command operable program or batch file.
    What is “‘” ?
    And then

    creating config files….done

    creating database template1 in C:/PostgreSQL/9.3/data/base/1 …
    creando base de datos template1 en C:/PostgreSQL/9.3/data/base/1 … “‘” is not recognized as internal or external command operable program or batch file.

    child process finished with output code 1
    initdb: eliminating content from folder «C:/PostgreSQL/9.3/data»

    Any idea about it?
    Its really strange
    Thanks in advance.

    Reply
  • THANK YOU so much. This post was very helpful for me.

    Reply
    • You are welcome! I am happy to hear that this blog post was helpful to you.

      Reply
  • Hi Petri,

    Thanks for your post!!!

    I am new to PostGre SQL. We have requirement in project to install Pgsql as database server on server machine and all the client machines should have access to this database and have locally installed pgsql client application too to do modifications if any.

    Could you please suggest if you have any idea regarding same or share any useful content link?

    Thanks in advance.

    Regards,
    Ravi

    Reply
  • Hey Petri,

    first thanx for ur very clear and comprehensible blog.

    I´m experiencing a problem with my install of postgresql, hope u can help me.

    I´ve installed postgresql 9.3 for my IDS but can´t connect to the database via cmd in Windows7.
    The connection via pgadminIII works fine, also via the psql-shell, but it won´t connect via normal cmd-shell, I always get the error-message: psql: FATAL: password authentication failed for user “postgres”.

    I tried to reinstall it in my admin-environment, not using UAC and my normal env, but still the same error. I was able to create db´s and new users via the psql-shell but i´m not able to connect to the server via cmd, any hints what I´m doing wrong? I need access to the server via cmd, otherwise my IDS will not work and lots of hours were wasted. I installed postgresql 9.3 with one-click-executable without any problems.

    Reply
    • Hi,

      What command are you using when you try to connect to your database by using the psql.exe?

      Reply
      • Hi,

        Thanx for the quick response :D

        I´m using the command d:\winids\postgresql\bin\psql -U postgres

        This command was also described in the documentation for the WinIDS, but until now I had no luck with it.

        Reply
        • Hi,

          I was able to login to my own PostgreSQL server by using that command (not on Windows though). I have three questions to you:

          • Are you asked to provide the password for user postgres when you use that command?
          • Have you tried to force psql to prompt for password by adding -W option to the command?
          • Have to tried to provide the name of the database?

          Unfortunately I don’t have any Windows computers anymore so all I can do is guess. If you can answer to my questions, maybe we will find an answer to this problem.

          Reply
          • Hi Petri,

            yes it prompts for the password, every time I use this command and it always does not take the password I gave for the user postgres, which is the administrative user.

            I also tried now to give it the database name, which is also postgres and tried it with option -W, but no success. I even tried it with the database snort, but the same problem.

            With the pgadmin it immediately takes the password and logs on to the database, the same when I start the psql-shell through my programs-list, it asks for all the credentials and then prompts for the password and logs on.

            I really don´t know what´s the problem with my cmd-shell.

          • Hi,

            I have three new questions for you:

            • Are you trying to connect to a local database?
            • If you are trying to connect to a local database, which authentication method is used for local users (check the pga_hba.conf file)?
            • can you paste the content of your pga_hba.conf file here (not the comments)?
          • Hi,

            yes ur right, I´m trying to connect to a local db, the auth-method is trust.
            Here´s the contents of my pga_hba.conf:

            # TYPE  DATABASE        USER            ADDRESS                 METHOD
            
            # IPv4 local connections:
            host    all             all             127.0.0.1/32            trust
            host    all             all             192.168.1.0/24          trust
            # IPv6 local connections:
            host    all             all             ::1/128                 md5
            # Allow replication connections from localhost, by a user with the
            # replication privilege.
            #host    replication     postgres        127.0.0.1/32            md5
            #host    replication     postgres        ::1/128                 md5
          • Hi,

            After reading your pg_hba.conf file, I noticed this StackOverflow question. Although the problem of that person is that the Ident authentication doesn’t work, his problem seems very similar than yours. You could try adding the following line to the beginning of the pg_hba.conf file:

            local   all   all   md5

            If you want that the password authentication is used only for specific database or user, tweak the line accordingly.

            Another option is to try to connect by using -h localhost.

            Note: I borrowed this idea from this answer.

            Update: Try following the advice given in this tweet.

          • Hi,

            1st, Thank you for all your efforts, I tried all these advices but, unfortunately, nothing worked.

            I tried it with all the usernames (postgres, snort, base and my Windows-users), no luck.

            Then tried ‘net user’, but only my Windows users are shown, like Admin, 2 personal logins and guest, shouldn´t there be a postgres-user?

            Tried it with ‘-h localhost’ and also tried to connect to several db´s using ‘-d’ command and all different users, no luck.

            Added the line you gave me in your last advice to my pg_hba.conf, but this also had no success.
            Is there anything I wrote in a wrong way?
            Here are the contents of my pg_hba.conf again.
            I´m really thinking about to try it with mysql-database… but I´m not sure if there occur the same problems.
            Hope you can help me in this weird case.

            # TYPE  DATABASE        USER            ADDRESS                 METHOD
            
            # IPv4 local connections:
            local   all             all                                      md5
            host    all             all             127.0.0.1/32            trust
            host    all             all             192.168.1.0/24             trust
            # IPv6 local connections:
            host    all             all             ::1/128                 md5
            # Allow replication connections from localhost, by a user with the
            # replication privilege.
            #host    replication     postgres        127.0.0.1/32            md5
            #host    replication     postgres        ::1/128                 md5
          • I have never managed to install PostgreSQL to a computer which runs Windows by using the one click installer. Nevertheless, I was under the impression that it creates the postgres user. It seems that I was wrong.

            To be honest, I am running out of ideas. You could configure PostgreSQL to use password authentication in every usecase. In other words, replace all instances of the word ‘trust’ with the word ‘md5′ and try if this changes anything (remember to restart the database server before you try to connect to it).

          • Hi Petri,

            I tried multiple things with my install of postgresql, but nothing worked.
            Our corporate admin said, it has something to do with the user rights, but I was fed up with trying then and installed mysql, now all is working fine and my IDS is running :)
            Thank you for your efforts so far.

            Regards, ARTI

          • Hi,

            You are welcome! I am happy to hear that you could solve this problem (even if it means that you cannot use PostgreSQL).

  • Not so far I have found new cool tool to work with postgresql on win – Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it.

    Reply
  • Thanks – after trying various other methods (without success) your’s worked first time – even for version 8.4.19

    Reply
    • You are welcome! It is nice to hear that this blog post was useful to you.

      Reply
  • hi,
    i am not able to start the database. Please give me brief description from beginning to end on how to do this completely. I am able to run init db command but couldnot proceed further. Help me out please. I am using windows 8.

    Reply
    • Hi,

      What error message do you get when you try to start your database?

      Reply
  • Hi Petri,

    I am getting the following error message when trying to connect to localhost:5432 through PGAdmin3:
    Error connecting to the server: could not create socket: Invalid argument (0x0002726/10022)

    When I run the command you suggested above for starting above the starter I get a message that says “server starting”. The server does not seem to be running when I search for it using netstat or when I enter pg_ctl status -D POSTGRESQL_ROOT\data .

    Any assistance would be much appreciated.

    Sam.

    Reply
    • Hi Sam,

      I haven’t run into this problem before but I would check the log file (POSTGRESQL_ROOT/log/pgsql.log) and hope that it reveals the root cause of this problem.

      Reply
      • Hi Petri,

        Thank you for coming back to me.

        The log is showing a few different errors following trying to amend postgresql.conf and pg_hba.conf to fix the problem:

        LOG: could not translate host name “localhost”, service “5432” to address: Unknown server error
        WARNING: could not create listen socket for “localhost”
        FATAL: could not create any TCP/IP sockets
        LOG: could not translate service “5432” to address: Unknown server error
        WARNING: could not create listen socket for “*”
        FATAL: could not create any TCP/IP sockets
        LOG: could not bind IPv4 socket: Invalid argument
        HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
        WARNING: could not create listen socket for “127.0.0.1”
        FATAL: could not create any TCP/IP sockets
        LOG: could not bind IPv4 socket: Invalid argument
        HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
        WARNING: could not create listen socket for “127.0.0.1”
        FATAL: could not create any TCP/IP sockets

        Again, I would really appreciate your assistance in solving this.

        Sam.

        Reply
        • Hi Petri,

          The issue is now resolved. Started the process from scratch and is now working without any issues.

          Thanks for your help.

          Sam.

          Reply
          • Hi Sam,

            I am happy to hear that you were able to solve your problem! How did you solve it?

  • I am using below script for register the windows service
    @ECHO ON
    REM The script sets environment variables helpful for PostgreSQL
    @SET PATH=”%~dp0\bin”;%PATH%
    @SET PGDATA=%~dp0\data
    @SET PGDATABASE=postgres
    @SET PGUSER=postgres
    @SET PGPASSWORD=postgres
    @SET PGPORT=5440
    @SET PGLOCALEDIR=%~dp0\share\locale
    “%~dp0\bin\initdb” -U postgres -A trust
    “%~dp0\bin\pg_ctl” register -N “postgresql-23″ -U “NT AUTHORITY\NetworkService” -D “%~dp0\data” -w
    Exit

    I am getting this error
    windows could not start the “postgresql-23″ service on local computer Access is denied.

    Reply
    • I am not exactly a Windows expert (haven’t used it for ages) but this Stack Overflow question might provide a solution to your problem.

      Reply
  • Thank you very much my friend!
    I’ll implement it…Good Job!

    Reply
    • You are welcome!

      Reply
  • Excellent article! Works like a charm!

    Reply
    • Thanks. I am happy to hear that this blog post was useful to you.

      Reply
  • I have tried to download PostgreSQL several times and keep getting this message when installing, “An existing data directory has been selected but is neither empty or a recgnisable data directory.”
    Any advice on what I can do to fix this problem? And should I hire someone who knows their computers to install this software? Thanks for you time..

    Reply
    • I have never encountered this problem myself but I found a StackOverflow answer which might shed some light to your problem. It seems that the selected data directory must be empty. In other words, there cannot be any other files or sub directories in it.

      If you are trying to install PostgreSQL to Windows, you might have to download the binary distribution and follow the instructions given in this blog post. I haven’t been trying to install PostgreSQL to Windows for a while (I don’t use Windows anymore). When I was still using Windows, I couldn’t install PostgreSQL by using the installer because the installation always failed when I tried to initialize the database cluster.

      Reply
  • Hello Petry,

    I have a little question. When we want create a new database, we will must write the username with the option “-U postgres” for example, and after make the password. Can us put the password in the same time that the username ? Something like this : –createdb -U postgres -w blabla test– doesn’t work

    Thanks you petri

    Reply
    • Hi Michel,

      Unfortunately I don’t know a way to set the password of the created database without using the -W or --password option (which forces the user to enter the password before the database is created). I tried searching the solution from Google, but I couldn’t find anything useful :(

      Reply
  • Hi,

    I am facing some issues while running the initdb.exe command.. I am creating a new db user by the name of DBUSER. Using cacls to grant permissions to to all the folders to DBUSER. I am creating a scheduled task to run the initdb.exe (with all the parameters) once… so that the initdb can /runas DBUSER. Doing everything using a batch file. I create the scheduled job-success… when I try to run it, it gives me a (0x1) message on the task scheduler.. Even if I try to run the initdb.exe as an administrator ..
    initdb.exe -U postgres –locale=C –e
    ncoding=UTF-8 -D C:/TestSoftware/InstallDir/bin/database/PG

    .. it gives me an error saying …..

    The files belonging to this database system will be owned by user “administrator”.
    This user must also own the server process.

    The database cluster will be initialized with locale “C”.
    The default text search configuration will be set to “english”.

    Data page checksums are disabled.

    creating directory C:/TestSoftware/InstallDir/bin/database/PG … initdb: co
    uld not create directory “C:/TestSoftware/InstallDir”: File exists

    Reply
  • Thank you! This was exactly what I was looking for. With your instructions, I had an enterprise-quality open-source database installed, configured, and working in about 10 minutes.

    Reply
    • You are welcome!

      Reply
  • After creating a postgresql service using this command POSTGRESQL_ROOT/bin/pg_ctl.exe register -N “postgresql” -U “NT AUTHORITY\NetworkService” -D “POSTGRESQL_ROOT/data” -w.

    When I start the postgresql service I get the following message “The postgresql service on Local computer started and then stopped. Some services stops automatically if they are not in use by other services or programs.”

    Please help me to get the postgresql services started

    Reply
  • Hi all,
    I try to install Visual Links analytic package with PostgreSQL. But I couldn’t install it in Win7 Home premium version. the error is

    “A problem was encountered while installing PostgreSQL.
    Error Code: 1
    Error Text: Error while running the PostgreSQL installer. RC=1

    Unable to continue. ”

    Can you guys explain what’s the error message about and how to resolve this issue.

    Reply
    • Hi,

      Unfortunately I have never installed that package so I cannot help you. :( I will tweet this on my Twitter account. Let’s see if someone else knows the answer to your question.

      Reply
      • Hi petri,
        thanks for the info, do let me know. if you get the info please post here n send me a email.

        cheers,
        gowtham.

        Reply
        • I got no answers to my tweet :( But if I happen to find some information about this, I will let you know.

          Reply
  • Great instructions, got latest postgres running on my computer easily according to these.
    But isn’t “The first step is to download and install the Microsoft Visual C++ 2008 redistributable package.” unnecessary? At least I did not download it and managed to “install” and run latest postgres easily.

    Reply
    • Hi Antti,

      To be honest, I am not sure if it is necessary anymore (your experience suggests that it is not necessary).

      When I wrote this blog post, the download page mentioned that you need to do this. I took a quick look at the download page and noticed that it doesn’t mention this step anymore.

      I will add a note about this to this blog post. Thank you for letting me know!

      Reply
  • YOU ARE A GOD!!!

    Reply
    • Thank you for kind words! I really appreciate them.

      Reply
  • Dear Petri,

    it’s a very useful post and I will check this again when I need to include binary zip version in my applications.

    Anyway, yesterday I could install PostgreSQL on Windows 7 with more or less ‘normal’ installation process covered by this post:

    http://robertsitandedublog.blogspot.hu/2014/12/how-to-install-postgresql-with-apache-2.html

    Today I’ve checked the ‘official’ installation method and it was also successful (restarting Windows and connecting database was not tested by the time of this comment).

    Honestly, I became a bit confused about PostgreSQL versions; now I’m not sure which version is fully supported on Windows 7 without doing any trick.

    Sincerely,
    Robert

    Reply
    • Hi Robert,

      Thank you for sharing your tutorial! I am sure that it is useful to someone.

      Unfortunately I don’t know what is the current status of the PostgreSQL Windows installer. However, this blog post is almost three years old and I assume that the PostgreSQL project team has published many improvements after I published this blog post.

      I hope that this blog post is obsolete or that it will be obsolete in the near future!

      Reply
  • Thank you where are you weeks ago hahahah i finally can up my server all thanks to you i appreciate it man

    Reply
    • You are welcome! I am happy to hear that this blog post was useful to you.

      Reply

Leave a Comment