Skip to content
Mahmudul Hasan
LinkedInGitHub

Complete guide to install postgresql on Linux server and connect from ASP.NET

— ASP.NET, PostgreSQL, Linux, WSL2, Entity Framework Core — 5 min read

Complete guide to install postgresql on Linux server and connect from ASP.NET banner

Postgresql is a powerful, open source object-relational database system, which is getting popular day by day. As a dot net developer, we tend to use Microsoft SQL Server due to it's easy integration with dotnet tool-set. But people are picking up Postgresql as their database of choice more and more day by day. Thanks to Entity Framework Core's robust and extensible design, it's very easy to switch between different database providers. But since we are so familiar with SQL Server, sometimes people like me, willing to use linux vms in production, suffer to install and configure Postgresql. So in this article, I will step by step guide you to install, configure, and connect from a client. I will use Ubuntu running in WSL2 as the server and ASP.NET Core running in Windows as the client.

Agenda

We want to install postgresql in a linux machine, create a database named blog with a user named bloguser who has a password bloguserpwd. After this we want to connect to the database from ASP.NET Core application running in another machine.

Here, we will use Ubuntu running in WSL2 as the server and ASP.NET Core running in Windows as the client. This will allow us to demostrate the two separate machines scenario.

Work through

We will skip the information dump and will focus on the steps to achieve our goal. So let's get started.

Install PostgreSQL

First, update the package list

1sudo apt update

Then install the postgresql package

1sudo apt install postgresql postgresql-contrib

Start and enable the postgresql service

1sudo service postgresql start

Create a database and a user

When we install postgresql, it creates a user named postgres. We will use this user to create our database and user. To do this, we need to login as postgres user. Type

1sudo -u postgres psql

Create a database named blog

1CREATE DATABASE blog;

Create a user named bloguser with password bloguserpwd

1CREATE USER bloguser WITH PASSWORD 'bloguserpwd';

Grant all privileges to the user bloguser on the database blog (and make him the owner) 

1GRANT ALL PRIVILEGES ON DATABASE blog TO bloguser;
2ALTER DATABASE blog OWNER TO bloguser;

Notice the semicolon at the end of each command. It's important.

Now, to exit the postgresql shell, type

1\q

Configure postgresql to accept connections from outside

By default, postgresql is configured to accept connections from localhost only. If you host your ASP.NET application in the same machine, then you don't need to change this. But if you want to connect to the database from outside, you need to change this to accept connections from outside. To do this, we need to edit the configuration.

First, get the installed postgresql version

1psql --version

Or you can use

1ls /etc/postgresql/

to get the list of installed versions. In my case, it's 14.10. So my postgres files are located at /etc/postgresql/14/main/postgresql.conf. You may have a different version. So change the version number accordingly.

Edit PostgreSQL Configuration

Open the configuration file in vim if you are comfortable with it. I prefer vim over nano because it's very simple and easy to use. Press INSERT button to start editing. When you are done, press ESC button and type :wq to save and exit. Simple as that.

1sudo vi /etc/postgresql/14/main/postgresql.conf

or you can use nano editor if you are not comfortable with vim. I don't know how to use nano. So I will use vim. 🥱🥱

1sudo nano /etc/postgresql/14/main/postgresql.conf

Now, find the line that says

1#listen_addresses = 'localhost'

and change it to

1listen_addresses = '*'

This will allow postgresql to accept connections from any ip address. If you want to allow connections from specific ip addresses, you can specify them here. For example, if you want to allow connections from an ip address IP_ADDRESS1, you can specify it like this

1listen_addresses = 'IP_ADDRESS1'

or if you want to allow connections from multiple ip addresses, you can specify them like this

1listen_addresses = 'IP_ADDRESS1, IP_ADDRESS2, IP_ADDRESS3/SUBNET_MASK'

Notice the single quotes. It's important. Also, don't forget to remove the # from the beginning of the line. It's important too.

Edit pg_hba.conf

Now, we need to edit the pg_hba.conf file. This file is located at /etc/postgresql/14/main/pg_hba.conf. Open the file in vim or nano and add the following line at the end of the file

1host    all             all             0.0.0.0/0               md5

This will allow all users to connect to all databases from all ip addresses. If you want to allow connections from specific ip addresses, you can specify them here. For example, if you want to allow connections from an ip address IP_ADDRESS1, you can specify it like this

1host    all             all             IP_ADDRESS1/SUBNET_MASK     md5
2host    all             all             IP_ADDRESS2                 md5

Here md5 means that the password will be encrypted using md5 algorithm. You can use password instead of md5 if you want to store the password in plain text. But it's not recommended. Also, providing the subnet mask is optional. But there may be some cases where you want to allow connections from a range of ip addresses. In that case, you can specify the subnet mask. This rule applies to both pg_hba.conf and postgresql.conf files.

Now, save and exit the file.

Restart postgresql service

Now, we need to restart the postgresql service to apply the changes we made. To do this, type

1sudo service postgresql restart

Now we are done with the server side configuration. Let's move on to the client side configuration. But before that, let's get the ip address of the server. To do this, type

1ip addr show eth0 | grep inet | awk '{ print $2; }' | sed 's/\/.*$//'

This will give you the ip address of the server. This maybe not necessary if you already know the ip address of the server. But it's good to know. Also, postgresql listens on port 5432 by default. So we need to make sure that postgresql is listening on port 5432. To do this, type

1sudo lsof -i :5432

If you see something like this

1COMMAND   PID   USER        FD  TYPE    DEVICE  SIZE/OFF    NODE NAME
2postgres  999   postgres    3u  IPv4    12345   0t0         TCP *:postgresql (LISTEN)
3postgres  999   postgres    4u  IPv6    12346   0t0         TCP *:postgresql (LISTEN)

then you are good to go. If you don't see anything, maybe you have configured postgresql to listen on a different port. In that case, you need to change the port number in the client side configuration. To get the port number, type

1sudo ss -nlp | grep postgres

Connect to the database from outside

Now, let's create a simple ASP.NET Core application to connect to the database from outside. This is not a tutorial on ASP.NET Core. So I will not go into details. Just I'll use the CLI to create a simple ASP.NET Core application and configure it to connect to the database.

Create the application

First, create a directory named blog and navigate to it

1mkdir blog
2cd blog

Then create a new ASP.NET Core application using the CLI

1dotnet new webapi

Install the required packages

Now, we need to install the required packages to connect to the database. To do this, type

1dotnet add package Microsoft.EntityFrameworkCore
2dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Configure the application

Now, we need to configure the application to connect to the database. To do this, open the appsettings.json file and add the following lines

1"ConnectionStrings": {
2    "DefaultConnection": "Server=IP_ADDRESS;Port=PORT_NUMBER;Database=blog;Username=bloguser;Password=bloguserpwd"
3}

Here, IP_ADDRESS is the ip address of the server and PORT_NUMBER is the port number of the server. If you have configured postgresql to listen on port 5432, then you don't need to specify the port number. You can just remove the Port=PORT_NUMBER; part from the connection string.

Configure the database context

Now, we need to configure the database context. To do this, open the Program.cs file and add the following lines

1builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

Assuming you are using .NET 6 or later. Create ApplicationDbContext class in a separate file and add the following lines

1public class ApplicationDbContext : DbContext
2{
3    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
4    {
5    }
6}

Add migration and update database

Now, to check if everything is working fine, we will add a migration and update the database. To do this, type

1dotnet ef migrations add InitialCreate
2dotnet ef database update

If everything is fine, you will see a message like this

1Build started...
2Build succeeded.
3# There are some logs here
4Done.

Run the application

Now, we are ready to run the application. To do this, type

1dotnet run

If everything is fine, you will see a message like this

1info: Microsoft.Hosting.Lifetime[14]
2      Now listening on: https://localhost:5001
3info: Microsoft.Hosting.Lifetime[14]
4        Now listening on: http://localhost:5000
5info: Microsoft.Hosting.Lifetime[14]
6        Application started. Press Ctrl+C to shut down.

Conclusion

So, we have successfully installed postgresql in a linux machine, created a database named blog with a user named bloguser who has a password bloguserpwd and connected to the database from ASP.NET Core application running in another machine. I hope this article will help you to install and configure postgresql in your linux machine and connect to it from outside.

If you have any questions or suggestions, please feel free to comment below. If you like this article, please share it with your friends. Thanks for reading. 😊😊😊

References

© 2024 by Mahmudul Hasan. All rights reserved.