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:
- Download and install the Microsoft Visual C++ 2008 redistributable package.
- Download and unpack the PostgreSQL binary distribution.
These steps are described with more details in the following.
Nevertheless, 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.
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
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.
I get the following output. It looks as though there is a problem with "OpenNMS:Name=Trapd" service” ? Is there a way to fix this ?
C:\Program Files\OpenNMS\bin>start.bat
C:\Program Files\OpenNMS\bin>"C:\Program Files\Java\jdk1.8.0_25\bin\java" -Xm
x512m -XX:MaxPermSize=256m -XX:+HeapDumpOnOutOfMemoryError -DisThreadConte
xtMapInheritable=true -Dopennms.home="C:/Program Files/OpenNMS" -Djava.end
orsed.dirs="C:/Program Files/OpenNMS/lib/endorsed" -Djava.io.tmpdir="C:/Progr
am Files/OpenNMS/data/tmp" -Dcom.sun.management.jmxremote.port=18980 -Dcom
.sun.management.jmxremote.ssl=false -Dcom.sun.management.jmxremote.authentica
te=false -jar "C:/Program Files/OpenNMS/lib/opennms_bootstrap.jar" start
Java HotSpot(TM) Client VM warning: ignoring option MaxPermSize=256m; support wa
s removed in 8.0
[DEBUG] System property 'opennms.library.jicmp' set to 'C:/Program Files/OpenNMS
/lib\jicmp.dll. Attempting to load jicmp library from this location.
[INFO] Successfully loaded jicmp library.
[DEBUG] System property 'opennms.library.jicmp6' set to 'C:/Program Files/OpenNM
S/lib\jicmp6.dll. Attempting to load jicmp6 library from this location.
[INFO] Successfully loaded jicmp6 library.
Caused by: org.springframework.beans.factory.BeanCreationException: Error creati
ng bean with name 'daemon' defined in class path resource [META-INF/opennms/appl
icationContext-trapDaemon.xml]: Invocation of init method failed; nested excepti
on is java.lang.reflect.UndeclaredThrowableException
at org.springframework.beans.factory.support.AbstractAutowireCapableBean
Factory.initializeBean(AbstractAutowireCapableBeanFactory.java:1512)
Caused by: java.net.BindException: Address already in use: Cannot bind
at java.net.DualStackPlainDatagramSocketImpl.socketBind(Native Method)
at java.net.DualStackPlainDatagramSocketImpl.bind0(DualStackPlainDatagra
mSocketImpl.java:80)
at java.net.AbstractPlainDatagramSocketImpl.bind(AbstractPlainDatagramSo
C:\Program Files\OpenNMS\bin>
Update: I removed the irrelevant parts from the stack trace because it was too long and made the comment unreadable - Petri
I don't have any experience from OpenNMS (I don't know what it is), but it seems that it cannot create a bean called
daemonbecause theBindExceptionis thrown. Its error message indicates that OpenNMS want to use a port that is already taken by another process. Check the configuration files and see if you can configure it to use another port.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.
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?
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.
@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?
@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.
@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.
Hi Ank,
Good to hear that this entry was useful to you.
Got stuck 2 days trying to install pgsql in a Windows 7 Home Premium x64 before finding your precise information. Thanks a lot !
It is great to hear that you find this tutorial useful!
Excelente tutorial.
should not it be "-D POSTGRESQL_ROOT\data" instead of "-d POSTGRESQL_ROOT\data". -d is debug
Vitalii,
You are right! I updated the command. Thank you for letting me know about this mistake!
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.
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 ;)
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.
@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.
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.
Hi Dan,
I am glad that you liked this blog entry. About your problem, check out this article: Using SrvStart to Run Any Application as a Windows Service.
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.
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.
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.
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?
I have not experienced this error myself. I will investigate and see if I can find something.
thanks petri!!! after spending 4-5hrs, finally i found this page and i did it.. incomplete oneclick installer made my day worst.. thanku.. :)
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
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.
It is getting stucked at
"LOG: autovacuum launcher started"
Could you be a bit more specific? What is getting stucked at? What PostgreSQL version are you trying to install?
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
@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?
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
Thank you for the tip! I really appreciate it. I will update the blog post and add this information to it.
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!!
You are welcome. I am happy to hear that I could help you out.
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
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:
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!
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.
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?
Hi,
Unfortunately I had no idea what could be wrong. That is why I am happy to find out that you managed to resolve your problem. I decided to add links to the relevant resources to this comment because I feel that they might be useful to other people who encounter this problem.
If the initialization of your database cluster fails, take a look at the following web pages:
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!
Hi Daniel,
Thank you for your comment! It is great to hear that I could help you out.
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
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.
Hi Petri,
Thanks for your prompt answer.
Cheers,
Hassib
You are welcome!
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
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.
Petri,
Thanks a lot for this . you have saved me.
You are welcome!
Thanx Mate for the perfect explanation. Exactly what I needed :D
You are welcome!
I am happy to hear that this blog post was useful to you.
i don't understand the part of folders, where create the "data" folder and "log" ty.
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
I hope that this answered to your question.
What about PostgreSQL 9.3.0?
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.
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.
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?
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?
Yes, I ended up downloading the manual installer and it worked for me.
Great!
God bless you.
Thanks! I assume that you liked this blog post :)
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
Hi Manna,
I am happy to hear that I could help you out!
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.
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!
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.
Thanks, Petri. It works well
Lan,
you are welcome!
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.
THANK YOU so much. This post was very helpful for me.
You are welcome! I am happy to hear that this blog post was helpful to you.
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
Hi Ravi,
Have you already read the Detailed installation guides wiki page of the PostgreSQL wiki? The articles mentioned on this page seem to cover most operating systems.
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.
Hi,
What command are you using when you try to connect to your database by using the psql.exe?
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.
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:
-Woption to the command?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.
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:
pga_hba.conffile)?pga_hba.conffile 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:
Hi,
After reading your
pg_hba.conffile, 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 thepg_hba.conffile: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.
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.
Thanks - after trying various other methods (without success) your's worked first time - even for version 8.4.19
You are welcome! It is nice to hear that this blog post was useful to you.
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.
Hi,
What error message do you get when you try to start your database?
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.
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.
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.
Hi Petri,
The issue is now resolved. Started the process from scratch and is now working without any issues.
Thanks for your help.
Sam.
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.
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.
Thank you very much my friend!
I'll implement it...Good Job!
You are welcome!
Excellent article! Works like a charm!
Thanks. I am happy to hear that this blog post was useful to you.
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..
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.
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
Hi Michel,
Unfortunately I don't know a way to set the password of the created database without using the
-Wor--passwordoption (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 :(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
Also granting BatchLogOn privileges to the DBUSER via ntrights.exe
I have to confess that I might not be able to help you because I have never seen this error, and I don't use Windows anymore (I have never been a Windows expert). However, I found a few web pages which might help you out:
I hope that this was helpful.
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.
You are welcome!
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
Unfortunately I haven't run into this problem before and that is why I have no idea how to fix it. You could try the approach described in this blog post.
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.
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.
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.
I got no answers to my tweet :( But if I happen to find some information about this, I will let you know.
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.
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!
YOU ARE A GOD!!!
Thank you for kind words! I really appreciate them.
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
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!
Thank you where are you weeks ago hahahah i finally can up my server all thanks to you i appreciate it man
You are welcome! I am happy to hear that this blog post was useful to you.
hii...i m using windows 7 ultimate.
and try to installing psql 8.2 on it....
it ask me a password...when i enter password it says,"secondary logon is failed".
what it means
Does this happen during the installation or after it?
I have a hunch that this might be related to the Windows secondary logon service. Is this service running? If it is not running, you have to enable it.
have you got a minute? please, help me.
I'm running on win7. build failed JAVA RETURNED :1
I would love to help you, but I have no idea what is wrong. Could you tell me what you were trying to do when you got this error message?
Hi Petri,
A very impress post - and super helpful! I've been trying to install postreSQL version 9.4.1-1 on a windows 7 platform and with one-click and your most helpful instructions above, continue to run into a point in initdb that aborts the instillation process. Using your binary-based installation approach above - i get the following exception in the "initializing dependencies" portion of initdb. Any ideas what might be going on here? Something that could be causing an issue is that i'm running this installation with a Windows 7 VM on VMware workstation - but can't imagine that would be causing an issue....
Cheers!
-john
C:\pgsql\bin>initdb -U postgres -A password -E utf8 -W -D "C:\pgsql\data"
The files belonging to this database system will be owned by user "JR".
This user must also own the server process.
The database cluster will be initialized with locale "English_United States.1252
".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory C:/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... windows
creating configuration files ... ok
creating template1 database in C:/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
Enter new superuser password:
Enter it again:
setting password ... ok
initializing dependencies ... child process was terminated by exception 0xC00000
1D
initdb: removing contents of data directory "C:/pgsql/data"
C:\pgsql\bin>
Hi John,
It was nice to hear that you liked this blog post.
Unfortunately I haven't run into your problem myself. I tried to find an answer from Google, but I found only one StackExchange question titled: Problem installing postgres 9.4 on Windows R2 (the question has been removed). Unfortunately it did not shed any light to your problem. :(
One thing came to my mind though: Did you uninstall PostgreSQL before you tried the manual installation? If you didn't do this, it might cause some problems (I have no idea if it really does this).
Hi Petri,
Thank you for such a quick reply! And your pointer above was very helpful. Reading through the comments on the post provided some ideas - in particular, that there is something likely up with incompatibility between my libraries and the latest version of PostgreSQL. Our local VM expert pointed out that the VM installed a raw Windows 7 instance - so, installed SP1 and the latest important patches. This didn't immediately solve the problem. However, when i pulled back from PostgreSQL version 9.4.1-1 to version 9.3.6-1 - the Windows 7 x64 one-click installer worked! So, am now up and running with PostgreSQL 9.3 on a Windows 7 SP1 VM! Yay!
And let me echo the other comments on this blog - many, many thanks for your thorough explanations and great answers to questions. This is definitely the best resource i could find on the web to help me through the PostgreSQL installation.
Bravo!
-john
Hi John,
Thank you for your kind words. I really appreciate them. Also, thank you for providing the solution for your problem (it might be useful to someone else).
Thanks :)
You are welcome.
Thanks!
You are welcome!
Your article is excellent!
Please note that as of PostgreSQL 9.5rc1 the required Visual C++ Redistributable package is version 13 (MSVCR120.dll), which can be downloaded from https://www.microsoft.com/en-US/download/details.aspx?id=40784
Thank you for your kind words. I really appreciate them. Also, thank you for reporting the required Visual C++ Redistributable package version which works with PostgreSQL 9.5. I will add this information to the blog post.
failed to load sql modules into the database cluster postgresql inwindows 7 professional how to reslove please any body solutions please replay
Check out this StackOverflow question.
Thank you so much for putting this together! Worked like a charm.
You are welcome. I am happy to hear that this blog post was useful to you.
Hi,
First of all, thank you for writing such detailed installation process. While uninstalling the postgresql, following error occured:
Element can only contain alphanumeric or '_' characters, but its current value is 'FrenchxxCOMMAxxxxSPxxCoteXXSPxxd`Ivoire'
Only thing that I did was change the Log on as service right to Local System using pg_ctl postgres utility. Service is working file, only the uninstallation process is not working.
Operation System : Windows
Help me out please.
Hi,
Unfortunately I have no idea what is wrong. :(
i have the same problem like you mayank, i dont not what i have ganna do..
anyone can help us please ?
Hi Petri,
Thanks for this article,
I am new to postgresql,could u help me know how do i proceed after installation,
on how to use it on command prompt as it cannot find psql anywhere
Hi,
Did you install it by following to the instructions given in this blog post? If so, you need to add the POSTGRESQL_ROOT/bin directory into the
PATHenvironment variable.Hi,
Well yes,everything works fine except ,after installation a new user is created named postgres and when i am trying to login using psql it is taking my windows user "Anubha" as the default user and the passwords dont match.I have tried everything but it doesnt work,can u recommend a solution for the same ?
Thanks,
Anubha
Please can you also state how to uninstall your installation?
Well, it seems that you are using the installer that is provided by EnterpriseDB. I have never used it because it didn't work (at least on Windows). That is why I wrote this blog post that explains how you can unzip the binary distribution and configure it yourself.
That being said:
Hi Petri,
I want to include the binaries of postgres sql in our product's installer. For that I need to run the initdb command from bat file. Is there a cmd command that will include the password as well and do the process. The initdb line that you mentioned in the post has no option to add password. the cmd is asking for password at next stage. But i want that I provide the password in single line. Is that possible. Please replay fast.
Regards,
Amjad Hussen Shekh
Hi,
You can also specify the password in a password file and configure the path of the password file by using the
--pwfilecommand line option. Note that if you decide to use this approach, the first line of the password file contains the password of the superuser.how configuration script in inno setup ? please help
Hi
I am getting this error while doing the fail over ...i manually stopped the master server services and after that in slave server i am getting this error..i can't understand why i am getting...please help me any one.(for testing the slave server is up or not at the time of master fail....)
Error:-
the system cannot find the file specified.
thanks a lot ...very helpful!!!
Installing PostgreSQL 12 on Windows 10...
“Problrm running post-install step, Installation may not complete crrectly
The database cluster initialisation failed.”
i am gettin this erro
please help me with this.
thank you