Install PostgreSQL 14 Without Admin Rights on Windows 11 OS

In this tutorial we will see how to Install and Set up PostgreSQL on Windows for a normal user without Admin Privileges. Most of the IT corporations doesn’t grant Admin privileges to install software. But being a software engineer, we would always like to explore new technologies. Admin privileges should not become a barrier to install new tech stack or a software package and thus hamper the limitation to explore it. So let’s see how to install PostgreSQL database without actually needing admin privileges.

Installing PostgreSQL latest Without Admin Rights on Windows 11 OS 1
Install PostgreSQL Without Admin Rights on Windows 11 OS – TutLinks

We focus on non-admin users and see how to install PostgreSQL without Admin Rights. We will also setup a database server and create a database using pgAdmin4.

The {username} has been mentioned at various places in this tutorial needs to be replaced with the name of your user directory.

Table of Contents

Steps to install PostgreSQL 14 without Admin Rights

We will understand how to install and configure a PostgreSQL database by a very detailed walk-through.

Download PostgreSQL Binaries

Download PostgreSQL Binaries from the official url https://www.enterprisedb.com/download-postgresql-binaries

Choose the latest binaries from installer version for Win x86-64 for Windows Operating System. Current latest version available is 14.5.

Feel free to choose the latest possible version available.

After the download completes, locate the archive postgresql-14.5-1-windows-x64-binaries.zip, right click on the downloaded archive postgresql-14.5-1-windows-x64-binaries.zip select Properties and check the Unblock option and click OK.

Extract the PostgreSQL binaries

Extract the PostgreSQL binaries to C:\Users\{username}\AppData\Roaming\

{username} is the name of the active user logged in. This ensures that you have full rights for all the files and directories beneath C:\Users\{username}\ location.

Once the binaries are extracted successfully, you will find a directory named pgsql in the C:\Users\{username}\AppData\Roaming\ directory.

Add PostgreSQL to the User Environment Variables

Add the directory C:\Users\{username}\AppData\Roaming\pgsql\bin to the User Environment Variables for {username}. Ensure that you do not add it to the System Variables. After adding the pgsql bin directory’s path to User Environment variables, click OK.

  • In Windows 11, this can be done by searching for and selecting “Edit environment variables for your account” (found under Systems in the Control Panel)
  • Once the Environmental Variables window is open, select the “Path” in the User variables for {username} box and click the “Edit…” button located just below.
  • Click the New button in the Edit environment variable window and add the specified directory (i.e. “C:\Users\{username}\AppData\Roaming\pgsql\bin”)
  • Click OK on the Edit environment variable window
  • Click OK on the Environment Variables window

Check the PostgreSQL version

After we add the PostgreSQL binary paths to the environment variables, we will proceed further and verify the PostgreSQL Server and PostgreSQL Client version from the command prompt.

Check PostgreSQL Server Version

To check the version of PostgreSQL Server installed on Windows OS, open the fresh command prompt window and type the following command.

postgres -V

It should show the installed version of the PostgreSQL Client. On my PC, I see postgres (PostgreSQL) 14.5 as installed version.

Check PostgreSQL Client Version

To check the version of PostgreSQL Client installed on Windows OS, open the fresh command prompt window and type the following command.

psql -V

It should show the installed version of the PostgreSQL Client. On my PC, I see psql (PostgreSQL) 14.5 as installed version.

Create a DB and associate an user in PostgreSQL

Launch a new command prompt (win+r then type cmd and hit enter). Type the following command to create a database and associate a super user for the database being created.

initdb -D C:\Users\{username}\AppData\Roaming\pgsql\mydata -U postgres -W -E UTF8 -A scram-sha-256

The output will show with prompt for password and reconfirming the password as follows.

The files belonging to this database system will be owned by user "{username}".This user must also own the server process.
The database cluster will be initialized with locale "English_United States.1252".The default text search configuration will be set to "english".
Data page checksums are disabled.
Enter new superuser password:
Enter it again:
creating directory C:/Users/{username}/AppData/Roaming/pgsql/mydata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... windows
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Calcutta
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Success. You can now start the database server using:
    pg_ctl -D ^"C^:^\Users^\{username}^\AppData^\Roaming^\pgsql^\mydata^" -l logfile start

Demystifying the initdb command in PostgreSQL

initdb – This is the PostgreSQL application used to initialize the database.

-D path/to/db/server/ – with this argument, we are telling the initdb application to initialize the database server to a directory followed by a path that has full access rights to the user who executes the command. Automatically, a new directory will be created and all the PostgreSQL server related files and data will be placed in that new directory for the database server being initialized.

-U superusername – This argument tells the db initialize application to create an user with name of our choice with superuser privileges. The super user privileges mean the user with complete control over the database server being initialized.

-W – This argument enforces the prompt for password that acts as a credential for the superuser being created.

-E – This argument will ensure the database is having encryption of specified standard. In this case we enforced our data to have UTF-8 encoding.

-A – This argument is used to indicate which cryptographic function has to be used for hashing password.

Start the PostgreSQL database server

Once we successfully create the database server, we will start the PostgreSQL server by issuing the following command in the command prompt.

pg_ctl -D "C:\Users\{username}\AppData\Roaming\pgsql\mydata" -l logfile start

Open pgAdmin4 browser

The pgAdmin4 is a browser based graphical user interface to interact with the PostgreSQL database server.

Navigate to the following directory, locate and launch pgAdmin4 application.

C:\Users\{username}\AppData\Roaming\pgsql\pgAdmin 4\bin

Set master password in order to secure and later unlock saved passwords and other credentials. Notice that this password can be different than the password set on the mydata database server. The current password is to access pgAdmin 4.

Create a PostgreSQL Server

Open Create-Server Wizard

Under the Servers tree node to the extreme left, right click on the node, choose create and click on Server… to create a new server. You can also click Add New Server under Quick Links that appears in the center of the page.

Specify a Server Name

Under General tab of the Create – Server wizard, specify the name of the server you want to create. This could be any noticeable name of your choice or something that might indicate the logical name of the data that the database server holds. In our case it could be mydata.

Specify Connection Details

Under Connections Tab of Create – Server Wizard, type Host name to have value as localhost.

Input the username and password with the credentials you gave while initializing the database server using initdb command.

Click on Save.

The new server will appear in the left pane under the Servers tree as a child node.

Under the databases you will find the default postgres database.

Create a database in PostgreSQL via pgAdmin4

Locate and right click on the database node of the PostgreSQL server. Choose create and then click on Database

In the General tab of the Create – Database wizard, give a name of your choice to be set for the new database being created. Feel free to modify the encoding in the Definition tab of the wizard. By default it will be set to UTF-8 as per the definition of our initdb command arguments.

In the Connections tab, enter necessary details such as

Hostname/addresslocalhost
Port5432
Maintenance databasepostgres
Usernamepostgres
PasswordThe same password you provided earlier.
Connection params in Register -Server tab of pgAdmin 4

Click on Save to create the database and you should see the new database under the mydata database server we have created.

Restart the PostgreSQL server

After you are done with the interaction with the database, you might want to stop the PostgreSQL database server to save the CPU utilization. To do that, issue the following command to Stop the PostgreSQL server

pg_ctl -D "C:\Users\{username}\AppData\Roaming\pgsql\mydata" -l logfile restart

Stop the PostgreSQL server

After you are done with the interaction with the database, you might want to stop the PostgreSQL database server to save the CPU utilization. To do that, issue the following command to Stop the PostgreSQL server

pg_ctl -D "C:\Users\{username}\AppData\Roaming\pgsql\mydata" -l logfile stop

Create Shortcuts to Start, Restart and Stop PostgreSQL database server

Instead of typing the commands to start and stop the PostgreSQL server every-time, its better you save them in separate batch files.

In the command prompt, run the following command to save PostgreSQL database startup command to a batch file named startpgsql.bat

echo pg_ctl -D "C:\Users\{username}\AppData\Roaming\pgsql\mydata" -l logfile start > "startpgsql.bat"

Similarly save the stop PostgreSQL database server command to the batch file named stoppgsql.bat by running the following command.

echo pg_ctl -D "C:\Users\{username}\AppData\Roaming\pgsql\mydata" -l logfile stop > "stoppgsql.bat"

Similarly save the restart PostgreSQL database server command to the batch file named restartpgsql.bat by running the following command.

echo pg_ctl -D "C:\Users\{username}\AppData\Roaming\pgsql\mydata" -l logfile stop > "restartpgsql.bat"

Add the two batch files startpgsql.bat and stoppgsql.bat to the desktop as shortcut icons. These shortcut files will become handy when you want to start or stop the PostgreSQL just double click those shortcuts and you are done.

Video

It is recommended to go through the video recording on how to install PostgreSQL on a Windows 11 OS for a user without Admin privileges.

How to install PostgreSQL (latest) without Admin Rights on Windows 11 OS – TutLinks

Summary

In this tutorial we have learnt

  • how to install PostgreSQL without admin rights for a normal user
  • how to add PostgreSQL binaries to the user Environment Variables
  • how to initialize a PostgreSQL database server
  • how to start a PostgreSQL database server using command prompt.
  • how to connect to a PostgreSQL server on localhost using pgAdmin 4
  • how to create a database in PostgreSQL server from pgAdmin4
  • how to restart the PostgreSQL database server using command prompt
  • how to stop the PostgreSQL database server using command prompt

In case you have admin privileges the installation is quite easy. You can go through this video tutorial Installing Postgres SQL on Windows PC

If you have Linux distro, then Install PostgreSQL 12 on Ubuntu

Navule Pavan Kumar Rao

I am a Full Stack Software Engineer with the Product Development experience in Banking, Finance, Corporate Tax and Automobile domains. I use SOLID Programming Principles and Design Patterns and Architect Software Solutions that scale using C#, .NET, Python, PHP and TDD. I am an expert in deployment of the Software Applications to Cloud Platforms such as Azure, GCP and non cloud On-Premise Infrastructures using shell scripts that become a part of CI/CD. I pursued Executive M.Tech in Data Science from IIT, Hyderabad (Indian Institute of Technology, Hyderabad) and hold B.Tech in Electonics and Communications Engineering from Vaagdevi Institute of Technology & Science.

This Post Has 8 Comments

  1. mike

    Thank you! Many unsuccessful attempts before this one saved me.

  2. V

    I cannot start the server. Could you please help me? This is the error after the code “pg_ctl -D “C:\Users\{username}\AppData\Roaming\pgsql\mydata” -l logfile start”:
    waiting for server to start…. stopped waiting
    pg_ctl: could not start server
    Examine the log output.

    This is what was in the log file:
    LOG: starting PostgreSQL 16.1, compiled by Visual C++ build 1937, 64-bit
    LOG: could not bind IPv6 address “::1”: Permission denied
    LOG: could not bind IPv4 address “127.0.0.1”: Permission denied
    WARNING: could not create listen socket for “localhost”
    FATAL: could not create any TCP/IP sockets
    LOG: database system is shut down

    1. Navule Pavan Kumar Rao

      can you try restarting it?

Leave a Reply