Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / Windows 10 / Install and Configure PostgreSQL on Windows

October 11, 2022 Windows 10Windows Server 2019

Install and Configure PostgreSQL on Windows

PostgreSQL is a free object-relational database management system able to compete with proprietary databases, like Microsoft SQL Server or Oracle. PostgreSQL supports user data, functions, operations, domains, and indexes. In this article, we’ll walk through the installation and brief overview of PostgreSQL database management. We will install PostgreSQL on Windows 10 computer, create a new database, add tables, and grant user access permissions. Also, we will show how to manage PostgreSQL using SQL shell and the visual PgAdmin tool. I hope this article will be a good starting point to learn PostgreSQL and use it in development or test projects.

Contents:
  • How to Install PostgreSQL on Windows 10?
  • Enable Remote Access to PostgreSQL Database
  • Managing PostgreSQL Database from the Command Prompt
  • PgAdmin: Graphical PostgreSQL Editor
  • Using PostgreSQL Query Tool

How to Install PostgreSQL on Windows 10?

To install PostgreSQL, go to https://www.postgresql.org and download the latest version of the distribution for Windows. Today it is PostgreSQL 14 (it supports 64-bit Windows versions only). Then run the installer.

download postgresql server installer

Check the following options during the installation:

  • PostgreSQL Server – a database server;
  • PgAdmin 4 – visual SQL editor;
  • Stack Builder – additional developer tools (you may need them later);
  • Command Line Tools.

select postrgresql components to install

Set a password for postgres user (it is created by default and has superuser privileges).

postgresql set superuser postgres password

By default, the PostgreSQL database instance listens on port TCP 5432. You must add it to the exceptions list in your firewall.

postgresql default port number 5432

Click Next, Next, and the installation of PostgreSQL is over.

Enable Remote Access to PostgreSQL Database

To allow network access to your PostgreSQL instance from remote computers, you need to create firewall rules. You can create a rule using a command line or PowerShell.

Run the command prompt as administrator. Enter the command below:

netsh advfirewall firewall add rule name="PostgreSQL" dir=in action=allow protocol=TCP localport=5432

  • where rule name is a rule name
  • localport is the allowed port number

Or, you can create a firewall rule allowing access to your PostgreSQL instance on port 5432 using PowerShell:

New-NetFirewallRule -Name 'POSTGRESQL-In-TCP' -DisplayName 'PostgreSQL (TCP-In)' -Direction Inbound -Enabled True -Protocol TCP -LocalPort 5432

After the command is applied, a new inbound rule for PostgreSQL port will appear in the Microsoft Defender Firewall.

Tip. To change the port number for an installed PostgreSQL instance, edit postgresql.conf in C:\Program Files\PostgreSQL\14\data.

Change the value in port = 5432. Then restart your postgresql-x64-14 server service. You can restart the service using PowerShell:

Restart-Service -Name postgresql-x64-14

Learn more about how to configure PostgreSQL options in postgresql.conf using tuners.

Managing PostgreSQL Database from the Command Prompt

Let’s see how to manage PostgreSQL using the command prompt tools. The main PostgreSQL management tools are located in bin folder, so we will run all commands from this directory.

Run the command prompt and change to the bin directory:

CD "C:\Program Files\PostgreSQL\14\bin"

Basic PostgreSQL commands:

  • Check the PostrgreSQL version: psql –V
  • Use the createdb command to create a new database: createdb -U postgres testdb (where postgres is a super-user, testdb is a new database name). Enter the super-user password;
  • To view a list of active databases: Psql -U postgres –l (password)
  • Create a new user: createuser –U postgres myuser1 (where myuser1 is a new user name);
  • Grant superuser privileges to the new user (don’t do this in a production deployment). Run the interactive PostgreSQL management shell: psql –U postgres . Grant the privileges: ALTER ROLE operator SUPERUSER CREATEROLE CREATEDB; Now your user can create roles and databases;
  • To display a list of users and roles in PostgreSQL, run the command: \du

PgAdmin: Graphical PostgreSQL Editor

PgAdmin makes it easier to manage a PostgreSQL database in an intuitive visual mode.

To run the editor, click PgAdmin 4 in the Start menu and enter postgres (super-user) password.

Expand a list of active databases in the Servers pane.

pgadmin connect to postgresql database

You can quickly create a new user or group and grant them some privileges. To do it, open Object -> Create -> Create Login/Group.
pgAdmin create login or role

To create a new database, just select Database in Object -> Create. Enter the database name and the owner.

By default, all PostrgeSQL databases are stored in base directory located in C:\Program Files\PostgreSQL\14\data\base.

Each database has a sub-directory inside PGDATA/base named by the database OID in pg_database. By default, the database files are stored in the sub-directory. Each table and index are stored in a separate file.

To backup and restore the database, it is better to use the Backup option in the Tools menu. To backup PostgreSQL automatically, use the pg_dump.exe command prompt tool.

Using PostgreSQL Query Tool

To create SQL queries in the graphical editor, pgAdmin has the built Query Tool. For example, you want to create a new table in a database using Query Tool.

  1. Select a database and open Query Tool in the Tools menu.
  2. Create an employee table:CREATE TABLE employee
    (
    Id SERIAL PRIMARY KEY,
    FirstName CHARACTER VARYING(30),
    LastName CHARACTER VARYING(30),
    Email CHARACTER VARYING(30),
    Age INTEGER
    );

    Run sql query with pgAdmin

    Id is the number of employee the SERIAL key is assigned to. This field contains a numerical value (1, 2, 3, etc.) that is increased by one with each new line. The next fields contain an employee’s first name and last name, their email address, and have a CHARACTER VARYING(30) type (30 characters max). The Age field contains an employee’s age and has an INTEGER type since it stores numbers.
  3. After you have written the SQL query code in the Query Tool, press F5, and a new table (employee) will be created in the database;
  4. To fill in the fields in the table, select the employee table in Schemas -> Tables. Click View/Edit Data in the Object menu. Here you can fill in the data in the table;
    add data to postgresql table
  5. Then run a simple select SQL query using the Query Tool: select Age from employee;run sql select query in postgresql

0 comment
0
Facebook Twitter Google + Pinterest
previous post
Creating Symbolic Links (Symlinks) in Windows
next post
How to Find Duplicate Files Using PowerShell?

Related Reading

Using Previous Command History in PowerShell Console

January 31, 2023

How to Install the PowerShell Active Directory Module...

January 31, 2023

Enable Internet Explorer (IE) Compatibility Mode in Microsoft...

January 27, 2023

How to Disable or Uninstall Internet Explorer (IE)...

January 26, 2023

How to Delete Old User Profiles in Windows?

January 25, 2023

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023
  • Adding Trusted Root Certificates on Linux

    January 9, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • How to Allow Multiple RDP Sessions in Windows 10 and 11?
  • How to Repair EFI/GPT Bootloader on Windows 10 or 11?
  • How to Restore Deleted EFI System Partition in Windows?
  • Network Computers are not Showing Up in Windows 10/11
  • How to Run Program without Admin Privileges and to Bypass UAC Prompt?
  • How to Create a Wi-Fi Hotspot on your Windows PC?
  • How to Sign an Unsigned Device Driver in Windows?
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top