Using PostgreSQL in dev: Part 1 – Installing PostgreSQL on Windows

I started work on a library that uses a PostgreSQL, which means I need a PostgreSQL database to test it against. The best solution is to run a PostgreSQL server on my Windows development PC as that gives me a fast, free access to PostgreSQL database.

There are three good ways to run a PostgreSQL server on Windows: running a Windows version of PostgreSQL, using docker or installing PostgreSQL in Windows Subsystem for Linux, known as WSL. I used the WSL approach because it’s supposed to the quicker than the Windows version and this article described what I has to do to make PostgreSQL run properly.

I didn’t think installing PostgreSQL would be simple, and it wasn’t, and I had to do a lot of Googling to find the information I needed. The information was scattered, and some were out of date, which is why decided to write an article listing the steps to do get PostgreSQL working on Windows. The second article will then cover how to access a PostgreSQL with Entity Framework Core and unit test against a PostgreSQL database.

• Using PostgreSQL in dev: Part 1 – Installing PostgreSQL on Windows (this article)
• Using PostgreSQL in dev: Part 2 – Testing against a PostgreSQL database

TL;DR; – The steps to installing PostgreSQL on Windows 10

1. Installing WSL2 on Windows, which provides a Linux subsystem running on Windows. This was easy and painless.
2. Installing PostgreSQL in the Linux subsystem. This was easy too.
3. Starting / stopping the PostgreSQL software on Linux. You just have to remember about five Linux commands.
4. Getting the Windows pgAdmin app up and running. This is where I had a problem and it took quite a while to work out what was wrong and how to fix it.
5. Creating a PostgreSQL server via pgAdmin. Had to be careful to get the correct values set up, but once I had done that once it remembers it for next time.
6. See the second article on how to access a PostgreSQL database from a .NET application running on Windows, and some suggestion on how to unit test against a real PostgreSQL database.

Setting the scene – why am I installing PostgreSQL

I’m a software developer, mainly working on ASP.NET Core and EF Core, and I use Windows as my development system. Up until now I have only used PostgreSQL via Azure, but I wanted a local PostgreSQL server to unit test for my library AuthPermissions.AspNetCore which will support SQL Server or PostgreSQL.

I haven’t used Linux before and I don’t really want to learn Linux unless I need to, so I wanted to find ways to use Windows commands as much as possible. Using WSL does need me to learn a few Linux commands, such as how to start / stop the PostgreSQL code and there is a PostgreSQL Linux app called psql which uses terminal commands to manage the PostgreSQL server etc. But I found a application that is like Microsoft’s SSMS (SQL Server Management Studio) application with a nice GUI front-end called pgAdmin and it even has a Windows version. That means I only need to learn a few Linux commands and manage PostgreSQL from Windows, which is great.

Here are the steps I took to install PostgreSQL using Windows Subsystem for Linux.

1. Installing WSL2 Linux (Ubuntu) on Windows 10/11

Installing WSL was easy and painless for me, and the Microsoft WSL documentation was excellent. You must be running Windows 10 version 2004 and higher (Build 19041 and higher) or Windows 11, and the command wsl –install via PowerShell or Windows Command Prompt run as administrator.

You then need to restart your PC, and after a long time the Linux subsystem will ask for a username and password. You will need the password every time you start the Linux system, so make sure you remeber it.

Couple of extra information:

• I didn’t need it, but there is a best practices for setting up a WSL development environment document which has useful things in it, such as how reset the Linux password if you forgot it.
• The wsl –install command will install WSL2, which is much better than the old WSL version – see Comparing WSL 1 and WSL 2 for more info.

2. Installing PostgreSQL on Linux

Again, I found a great Microsoft document on how to install PostgreSQL (and other databases) in your Linux Subsystem. The list below shows the things I did to install PostgreSQL.

1. Update your Ubuntu packages: sudo apt update
2. Install PostgreSQL (and the -contrib package which has some helpful utilities) with: sudo apt install postgresql postgresql-contrib
3. Set the PostgreSQL password: sudo passwd postgres.
   The nice thing about that is you can change the password any time if you forget it. This password is used whenever you access PostgreSQL’s servers and databases.

TIP: Linux doesn’t use ctrl-c / ctrl-v for cut/paste. I found that I could use the normal ctril-c to cut on Windows and then if I right-clicked in Linux it would paste – that allowed me to copy commands from the docs into Linux.

3. Starting / stopping PostgreSQL on Linux

The previous Microsoft document on how to install PostgreSQL also provided the 3 commands you need once PostgreSQL is installed:

• Checking the status of your database: sudo service postgresql status
  It says online if running, or down if stopped.
• Start running your database: sudo service postgresql start
• Stop running your database: sudo service postgresql stop

TIP: You don’t need to have the Linux subsystem window open to access PostgreSQL once you have started – the Linux subsystem will stay running in the background.

4. Getting the Windows pgAdmin app up and running

I found the Windows version of pgAdmin here and installed it. The pgAdmin app started OK, but I immediately had a problem of connecting to the PostgreSQL on the Linux subsystem. When I tried to create a PostgreSQL server, I got the error “password authentication failed for user “postgres” – see below

This was frustrating and took quite a while to work out what the problem is. I tried a number of things but in the end it turns out you that the authentication mode of PostgreSQL doesn’t work with the connection to Windows (see this stack overflow answer) and you need to edit PostgreSQL’s pg_hba.conf file to change what is known as the auth-method (see PostgreSQL docs on the pg_hba.conf file) from md5  to trust for the IP local connections. See code below which shows the new version – NOTE you need to scroll to the right to see the change from md5 to trust.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

The problem with the stack overflow answers I found is they were written before WSL was around, so they assume you are running PostgreSQL directly from Windows. To get to the files in the Linux subsystem I needed to use the \\wsl$ prefix to access the WSL files. In the end I found the PostgreSQL’s pg_hba.conf at \\wsl$\Ubuntu\etc\postgresql\12\main (note that the \12\ part refers to the version of PostgreSQL application).

NOTE: Editing the pg_hba.conf file needs a high level of access, but I found VSCode could edit it, or you can edit the file via NotePad run as administrator.

5. Creating a PostgreSQL server

Once the password trust problem was fixed, I tried again to create a PostgreSQL server and it worked. When the pgAdmin application is started it asks for a password, which can be anything you like (and you can change it easily). This password lets you save PostgreSQL passwords, which is useful.

To create a PostgreSQL server, I clicked the Quick Link -> Add New Server link on the main page, which then showed a Create – Server popup with various tabs. I had to fill the following tabs/field

1. On the General tab you need to fill the Name field with your chosen name. I used PostgreServer
2. On the Connection tab you need to set
   • Host name/address: 127.0.0.1
   • Password: the same password as given in step 2 when setting the PostgreSQL password
   • Save password: worth turning on

You now have a PostgreSQL Server, but no databases other than the system database called postgres. You can create database using pgAdmin, but in the next article I show how your application and unit tests can create databases.

NOTE: I found this article by Chloe Sun which has pictures of the pgAdmin pages, which might be useful.

Conclusion

Most of the steps were fairly easy until I hit step 4, and which point I had to do a lot of digging to find the solution. The Microsoft documents were great, but they didn’t provide all the information I needed. Thanks to all the people that answered stock overflow questions and people who write articles about this, especially Chloe Sun who wrote a similar article to this.

The next article shows you how to access a PostgreSQL database using EF Core and describes the unit test methods I added to my EfCore.TestSupport 6.0.0-preview001. These unit test helpers allow you to create a unique PostgreSQL database for each xUnit test class.

Happy coding.