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.
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
- Download PostgreSQL Binaries
- Extract the PostgreSQL binaries
- Add PostgreSQL to the User Environment Variables
- Check the PostgreSQL version
- Create a DB and associate an user in PostgreSQL
- Demystifying the initdb command in PostgreSQL
- Start the PostgreSQL database server
- Open pgAdmin4 browser
- Create a PostgreSQL Server
- Create a database in PostgreSQL via pgAdmin4
- Restart the PostgreSQL server
- Stop the PostgreSQL server
- Create Shortcuts to Start, Restart and Stop PostgreSQL database server
- Video
- Summary
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/address | localhost |
| Port | 5432 |
| Maintenance database | postgres |
| Username | postgres |
| Password | The same password you provided earlier. |
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.
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
Pingback: Minimal web API with CRUD on PostgreSQL: A RESTful Microservice implementation in ASP.NET Core for .NET 6 – TutLinks
Pingback: Install PostgreSQL 12 on Ubuntu – TutLinks
Pingback: Implementing Async REST APIs in FastAPI with PostgreSQL CRUD – TutLinks
Pingback: Deploy FastAPI on Azure App Service – TutLinks
Pingback: Working with Google Cloud Storage for ASP.NET Core 6 MVC Application using Entity Framework Core 6 – TutLinks
Thank you! Many unsuccessful attempts before this one saved me.
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
can you try restarting it?