Nisarg Upadhyay
PostgreSQL Server dashboard

How to install PostgreSQL on Windows

September 14, 2021 by

In this article, we are going to learn how we can install and configure PostgreSQL on windows 10. PostgreSQL, also known as Postgres, is a free and open relational database management system.

The PostgreSQL database manages the multi-version concurrency control to manage the concurrency (MVCC). When we run a transaction on PostgreSQL, it gives the snapshot of the database, which allows each transaction to made changes on the database without affecting the other transaction. PostgreSQL has three levels of transaction isolation.

  1. Read committed
  2. Repeatable Read
  3. Serializable

We can install PostgreSQL on the following operating systems:

  1. Windows
  2. Linux
  3. Mac OS Server
  4. Free BSD and Open BSD

In this article, we are going to focus on the step-by-step installation process of PostgreSQL on windows 10. Before the installation, we must download the stable copy of the PostgreSQL 13 server from the location. We can use this installer to install PostgreSQL on windows in graphical and silent mode. The setup file contains the following software packages:

  1. PostgreSQL 13 server
  2. pgAdmin: It is a graphical tool to develop and manage the PostgreSQL server and database
  3. Stack builder: It is a package manager that contains additional tools that are used for management, migration, replication, connectors, and other tools

Once the setup file has been downloaded, double-click on the file. The installation wizard of PostgreSQL on Windows has begun. The first screen is the Welcome screen of the PostgreSQL installation.

Postgresql on windows installation began

On the Installation directory screen, specify the location where you want to install the PostgreSQL.

Specify installtion directory

On the Select component screen, choose the component that you want to install on your workstation. You can choose any of the following:

  1. PostgreSQL Server
  2. pgAdmin4: It is a graphical interface that is used to manage the PostgreSQL database
  3. Stack builder: The stack builder will be used to download and install drivers and additional tools
  4. Command-line tools. The command-line tools and client libraries like pg_bench, pg_restore, pg_basebackup, libpq, pg_dump, and pg_restore will be installed

In our case, we will install all components.

Choose PostgreSQL components

On the Data Directory screen, specify the directory where you want to store the database files. In our case, the data directory is C:\PostgreSQL Data.

Specify Data directory

On the Password screen, specify the database superuser password. This password will be used to connect to the PostgreSQL database server.

Specify password of superuser

On the Port screen, specify the Port number on which the PostgreSQL server will listen to the incoming connections. By default, the PostgreSQL server listens on port number 5432. You can specify the non-default post on this screen. Make sure any other application must not use the port you specify in the Port textbox, and it must allow the incoming and outgoing connections. In our case, I am not changing the port.

specify port

You can choose the locale that you want to use in the database on the advance option screen. In our case, I am choosing the default locale.

specify locale

On the Pre-Installation Summary screen, you can view the settings used for installing the PostgreSQL server.

PostgreSQL on windows installation summary

The Ready to install screen is the notification screen that states that the PostgreSQL installation process will begin.

PostgreSQL on windows installation Ready to install

The installation process of PostgreSQL on windows has begun.

PostgreSQL on windows is installing

The PostgreSQL server has been installed successfully. If you want to install additional components and drivers, you can choose to open the stack builder. In our case, I am not installing additional components. Click on Finish to complete the installation.

PostgreSQL on Windows Installation completed.

Now, reboot the workstation. Let us understand how we can connect to the PostgreSQL server using pgAdmin4 and SQL Shell (pSQL).

Connect to PostgreSQL using pgAdmin4

We can use the pgAdmin4 tool to manage and administrate the PostgreSQL server. We can also use the pgAdmin4 to execute the Adhoc queries and create database objects.

To connect to the PostgreSQL. Launch the pgAdmin4. On the first screen, specify the password of the superuser that can be used to connect to the PostgreSQL Server.

Provide password of superuser

Once you’re connected to PostgreSQL13, you can view the database objects in the Browser pan. To view the installed servers, expand Servers. Under Servers, you can view the list of installed servers. In our case, the installed PostgreSQL is PostgreSQL13. You can view the list of databases, users, and tablespaces under PostgreSQL13.

View PostgreSQL instances

We can view the Server Activities and the configuration of the PostgreSQL server in the Dashboard tab.

PostgreSQL Server dashboard

You can view the list of sessions, locks acquired by the process, prepared transactions, and configuration under the server activity pan.

PostgreSQL Server activity

Now, let us see how we can create a database.

How to create a database using pgAdmin4

Now, let us create a database using pgAdmin4. To create a database, Expand Serves 🡪 Expand PostgreSQL13 🡪 Right-click on Databases 🡪 Hover Create 🡪 Select Database.

Create database

A Create database dialog box opens. In the general tab, specify the database name in the Database Textbox.

Specify the database name

You can specify the Database Encoding template used to create a database, tablespace, database collation, character type, and connection limit on the Definition tab.

Specify the different parameters

In the Security tab, you can configure the privileges and security configuration. In our case, we have not changed anything.

Configure security

In the Parameters tab, you can configure the database-specific parameters. I have not changed any configuration.

Configure advance parameter

In the SQL tab, you can view the CREATE DATABASE statement generated with the configuration defined in the Create – database dialog box.

Create database statement

Click on Save to create the database named EmployeeDB and close the dialog box. You can view the new database in Browser pan.

Database has been created

As you can see, the database has been created successfully.

  • Note: If you do not see the EmployeeDB database in the Browser pane, right-click on the Databases and select Refresh

    Refresh database

We can view the database files under the C:\PostgreSQL Data directory. See the following screenshot.

View database files

Now, let us see how we can execute the queries on PostgreSQL.

Querying the PostgreSQL database using pgAdmin4

To execute the queries using the pgAdmin4, Click on Tools 🡪 Click on Query Tool.

Open query editor

A query editor pan opens. Now, let us create a table named tblEmployeeGrade. The following query creates a table.

Screenshot of the Query Editor:

create table in Query editor

As you can see, the query execution status will be displayed in the messages pan. Now, let us insert some records in the tblemployee. Run the following query to insert data in tblemployee.

Insert statement in Query editor

Run the SELECT statement to populate the data from the tblemployee table.

Select Query output

As you can see, the query output had populated the data in grid view format and can be viewed in the Data output pan.

Connect to PostgreSQL using SQL Shell (pSQL)

We can use the pSQL command-line utility to manage the PostgreSQL database. The SQL Shell is automatically installed with the PostgreSQL server. When we launch the SQL Shell, it prompts for following options.

  1. Server Name: Specify the hostname of the machine on which the PostgreSQL has been installed. If you do not specify the hostname, then pSQL will connect to the localhost
  2. Database: Specify the database name that you want to use. If you do not specify the database name, pSQL will connect to the Postgres database
  3. Port: Specify the port. If you do not specify any port, pSQL will use port number 5432 to connect to the server
  4. Username: specify the username that you want to use to connect to PostgreSQL. If you do not specify the username, the pSQL will use the Postgres user
  5. Password: Specify the password of the user specified in the username parameter

Specify all the parameters and hit enter to connect to the database.

PostgreSQL connected using pSQL.

As you can see, the connection has been established successfully.

Summary

In this article, we learned the step-by-step installation process of PostgreSQL on windows 10. I have also given a high-level overview of the pgAdmin4 tool and how we can connect to the PostgreSQL database using pgAdmin and SQL Shell (pSQL) utility.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)
PostgreSQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

237 Views