Install PostgreSQL Without Admin Rights on Windows 10 OS

Install PostgreSQL Without Admin Rights on Windows 10 OS - TutLinks
Install PostgreSQL Without Admin Rights on Windows 10 OS – TutLinks

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.

Steps to install PostgreSQL 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 12.2.

Feel free to choose the latest possible version available.

After the download completes, locate the archive postgresql-12.2-1-windows-x64-binaries.zip, right click on the downloaded archive postgresql-12.2-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.

Check the PostgreSQL version

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) 12.2 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) 12.2 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\mydb -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/mydb ... 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^\mydb^" -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 super user 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\mydb" -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.

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.

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.

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

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\mydb" -l logfile stop

Create Shortcuts to Start 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\mydb" -l logfile stop > "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\mydb" -l logfile stop > "stoppgsql.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.

Summary

In this tutorial we have learnt

  • how to install PostgreSQL without admin rights for a normal user.
  • how to initialize a PostgreSQL database server.
  • how to start a PostgreSQL database server using command prompt.
  • to a PostgreSQL server in our server group and then created a database.
  • how to stop the PostgreSQL database server using command prompt.

In case you have admin privileges the installation is quite easy. It is recommended to go through the video recording on how to install PostgreSQL on a Windows 10 PC for a user with Admin privileges.

Navule Pavan Kumar Rao

A Full Stack Software Consultant in the day, a Data Scientist in the evening, an Innovative thinker in the nights. Did I forget to say I am a powerful dreamer during the Mid nights? 😉Education: Executive M.Tech in Data Science, Indian Institute of Technology, Hyderabad. B.Tech in Electronics and Communication Engineering.Hobbies: Games, Music, Creative Thinking
Subscribe
Notify of
guest
2 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
trackback
2 months ago

[…] Install PostgreSQL Without Admin Rights on Windows 10 OS […]

trackback
2 months ago

[…] If you do not have PostgreSQL on your PC you may want to install it by following the instructions mentioned in this article Install PostgreSQL Without Admin Rights on Windows 10 OS […]