If you are a part of a corporation, it is highly unlikely that you have the admin privileges to install any external software. But the curious souls that all software developers are, in this article, we will see the detailed process of installation of PostgreSQL without having administrator rights on our Windows machine.
Installation:
Follow the below steps to install PostgreSQL:
- Step 1: For setting up PostgreSQL, we need to download the PostgreSQL Binaries. To download the binaries, visit this link. Choose the appropriate binary file as per the required PostgreSQL version and download them.
- Step 2: Now create a new folder at a place where you have full access rights to this folder and extract these binary zip files there. (You can preferably extract these binary files at locations such as D:\ drive or E:\ drive).
- Step 3: After extraction, the folder structure will look as shown below.
PostgreSQL zip binaries after extraction
- Step 4: Now add this location of your extraction in the Path variable in the User Environment Variables of your computer.
Configuring User Environment Path Variable
Well, Congratulations!!. At this point, we have successfully configured PostgreSQL in your windows system.
Verify Installation:
To verify if it is installed properly, use the following commands.
The below command checks the PostgreSQL server version:
postgres -V
Check PostgreSQL Server Version
The below command checks the PostgreSQL client version:
psql -V
Check PostgreSQL Client Version
Create a DB and associate a user in PostgreSQL:
Now it is time to initialize the database and associate a user to this. The database will be initialized at the location given by us (data folder in this case). The command for this is as follows:
initdb -D D:\PostgreSQL\pgsql\data -U postgres -E utf8
The following are the references for the command line arguments which will help for better understanding –
- -D path/to/db/server/ – This option instructs the initdb to initialize the database at a particular location specified by user. After specifying the location a new directory will implicitly be created and all the PostgreSQL files and their related data will be kept here.
- -U name – This option is used to create a user with the specified name and with all the superuser privileges.
- -W – It is used to explicitly prompt a password for superuser.
- -E – It specifies the encoding to be used for the database.
- -A – It is used to specify the encrypting of the superuser password.
PostgreSQL initdb
- Step 1: Start the database by running the following command.
pg_ctl -D D:\PostgreSQL\pgsql\data -l logfile start
Start Database Server
- Step 2: Now navigate to the location of your PostgreSQL binary folder and traverse to the following path as shown in the figure.
- Step 3: Double click on pgAdmin4 application. Now a pgAdmin4 instance will load into the system’s default browser.
pgAdmin4 Home Page
- Step 4: Set a master password to secure the server.
- Step 5: Now click on Servers on the right-hand side to create a new server for your database. Fill in the required details.
- Step 6: Click on the Database section to create a new database for your work purpose and begin using it.
- Step 7: To stop the database, use the same command used for start database as used in Step 7 and replace start by stop.
pg_ctl -D D:\PostgreSQL\pgsql\data -l logfile stop
Your basic setup of PostgreSQL is now done. However, if you want to use additional PGSQL functions like vacuuming, upgrade, restore etc then you might need to setup the binary paths for it . For this, go to File -> Preferences. Now scroll down to Paths and click Binary Paths. Specify all the three paths as the directory of PgSQL installation bin folder. Refer to the below figure for setting up the binary paths.