Relational Database Management Systems (RDBMS) can store a large amount of data using the tabular arrangement of a database. RDBMS are widely used to perform database operations like creating, administering, and managing small and large workloads.

PostgreSQL is a fantastic tool to use, but it can be a little daunting to get it up and running in Windows. As such, let us guide you through how to set up PostgreSQL on Windows and get started with your database as soon as possible.

What You Need to Know About PostgreSQL

PostgreSQL is a database management software based on SQL. This enterprise-level software is known for its versatility and scalability. Its flexibility allows it to handle different levels of workloads from single and multiple machines simultaneously. Even better, it can function seamlessly with an entire warehouse of concurrent users.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature sets, extensibility. The dedication of the open-source community behind the software allows this software to deliver performant and innovative solutions consistently.

How to Install PostgreSQL on Windows

The PostgreSQL installation process on Windows is slightly different from its Linux counterparts. You need to install the Postgre Database Server and a graphical tool to administer the database.

While you can download both of them separately, you would still need to configure them together, which can be a challenge of its own. It is, therefore, best to download and install a bundled installer.

To kickstart the installation, visit the official PostgreSQL website and select Download. On the next page, select Windows since we are downloading a compatible version for Windows OS.

PostgreSQL download page

On the Windows Installer page, click on Download the Installer. Under the Platform Support section, you will notice some relevant information for each of the released versions. It’s best to note the latest version available for Download.

Interactive installer by EDB

Clicking on Download the Installer brings you to the PostgreSQL Database Download page. Depending on the version of your computer, you can choose between Windows x86-64 or Windows x86-32.

Select the latest PostgreSQL version from the dialogue box and click on the download button next to it. This should start the setup download for you.

PostgreSQL Database Download

Once the EXE file downloads, click on it to begin the setup. The setup will ask you about the destination directory and component details.

Destination directory

From the list of components, you can choose from the following:

  • PostgreSQL Server
  • pgAdmin4
  • Stack Builder
  • Command Line Tools

Related: How to Install and Set Up Microsoft SQL Server on Ubuntu

It's a good idea to check all four boxes, as each application will be useful in the near future.

Components PostgreSQL

On the next screen, you will be to set up a super password for the database superuser. Create a password and then click Next.

Setup superuser password

On the next screen, leave the port number unchanged and click Next. You should see a pre-installation summary that lists all the details you've set up. Review each aspect of the installation, and if everything looks fine, click on Next.

Pre installation summary

The Ready to Install dialogue box will appear. Click on Next to begin the installation.

Installation PostgreSQL

Connecting to PostgreSQL with pgAdmin4

There are two ways to connect PostgreSQL to a server. You can either use the conventional command-line method or the pgAdmin tool that comes preloaded after the installation process on Windows.

Connecting to PostgreSQL Using the pgAdmin Application

  • Launch the pgAdmin application from the program files folder or using the Windows Search feature.
  • Log in to the pgAdmin client using the master password that you used during the installation process.
  • Click on the Create Server option and fill in necessary details like Host, Port, Maintenance Database, Username and Password.
  • Click on the Save option. The created server is now visible on the left side tab.
  • Double click on the server’s name and enter the password to connect to the PostgreSQL server.

Connecting to PostgreSQL Using the Command Window

Post-installation, you can search for the SQL shell (PSQL) in the Start menu. This is where you will enter any relevant SQL commands.

To list all the available databases with PSQL​​​​, type in \l and hit Enter.

List-databases-in-PSQL

How to Create a New Database in PostgreSQL

To Create a New Database, type CREATE DATABASE test, where test is the name of the database.

Create database PSQL

To access the new database, close the PSQL terminal and reopen it again. The application will remember the server name, port, user name, and password you used last time.

Before you reconnect, change the Postgres name to your set databases' name, then press Enter.

How to Create and List Tables in PostgreSQL

To create a table within an existing database, use the following command:

        CREATE TABLE PERSON (
ID BIGSERIAL NOT NULL PRIMARY KEY,
NAME VARCHAR(100) NOT NULL,
COUNTRY VARCHAR(50) NOT NULL );

This command will create a table person within the database test and add a few variable names to it as well. Tweak these variables to suit your own needs.

Create table PSQL

To list all tables in a database, use the \dt command. If you use this command with the above example, you will notice there is only one table called Person in the database Test.

Related: How to Create a Table in SQL

How to Modify the Root User Credentials

You can change the Postgres password after logging in as the root user. To do this, use the following command:

        ALTER USER postgres PASSWORD 'newpassword';
    

Change newpassword to the password of your choice.

Change root password

Creating and Removing a User Role in PostgreSQL

Many people work simultaneously on a project with different roles. You can create different roles that have different accesses in PostgreSQL by using the Windows console. You can also choose whether to grant a superuser status to the newly created role.

To grant someone access, run the Windows console and change the default directory to the PostgreSQL bin directory (For instance, C:\Program Files\PostgreSQL\9.0\bin) or add this directory to the Path environment variable.

Now use the following code in the console:

        createuser.exe --createdb --username postgres --no-createrole --pwprompt openpg
    

You can modify the commands to change the role privileges. You will be prompted to choose the superuser status for the role. Enter y for Yes or n for No and then assign a password to create the new role.

You can remove a user role from the list of other users using the following command:

        DROP USER name [, ...];
    

Working With PostgreSQL in Windows

PostgreSQL is an incredible tool to manage databases reliably and in a foolproof manner. The Windows installation process is relatively simple and requires only a few clicks to get set up and running.