Introduction to PostgreSQL: up and running

If you’re here, I’m hoping you’re familiar with the basics of Database Management Systems and have an idea of how SQL queried and different database system works. This article is to understand and start working with PostgreSQL as a beginner.

What is PostgreSQL?

PostgreSQL, is a free and open source ORDBMS (aka object relational database management). Object oriented relational database means that it directly supports features like classes, inheritance and objects i.e. features of object oriented programming in the structure of the database and it’s querying language.

PostgreSQL, initially called Postgres was a research project started at the University of California, Berkeley. It is a cross platform ORDMS and can runs on a number of OS including windows, linux, mac, FreeBSD, OS X and Solaris.

PostgreSQL features transactions with ACID properties, i.e. atomicity, consistency, isolation, durability. It also manages concurrency control through Multi-Version Concurrency Control or MVCC. MVCC promises isolation and consistency throughout the database when more than one users are trying to make changes at a time.

Naming conventions in PostgreSQL

Since different platforms have their own set of naming conventions, lets go over PostgreSQL’s popular naming conventions as well:

• Tables || Indexes == Relations
• Row == Tuple
• Column == Attribute
• Data Block ||Data cell == Page (on the disk) && Buffer (on memory)

Data block or Data cell or Page is the smallest unit of data storage in PostgreSQL. One can think of it as a cell in a table. Each table can be seen as an array of a fixed page length. The default size of each page is set to 8kbs which can be tuned when required. An important convention to note here is that when these Pages are on the disk, i.e. immediately accessible, they are called Page, while if they are stored and have to be fetched from the memory, they are termed as buffer.

Installing and setting up PostgreSQL on windows

To install PostgreSQL, one can go to the official website of PostgreSQL : https://www.postgresql.org/download/

On opening the website, click on your operating system family (windows in our case), click on Download the installer and then download the latest version of the postgreSQL installer available. At the time of writing this article, 14.2 was the latest version available.

The download can take a time depending on your internet speed at the moment. Now as soon as the installation is complete, go to the folder where the postgreSQL was downloaded and open the .exe file with administrative privileges. (Right click and select Run as administrator)

From here on, installation is pretty neat:

Step 1: Welcome to PostgreSQL Setup Wizard press Next>

Step 2: Specify installation directory for PostgreSQL and press Next>

Step 3: Select components to be installed, deselect components not to install and press Next. We recommend installing all the components if you don’t understand what each is for.

Step 4: Select where your data for the database will be stored and press Next>

Step 5: Type in your super user password. and press Next>. The “super user” is responsible for setting up all the new users to the database. We recommend to make note and remember the password given here.

Step 6: Select the port for postgreSQL and press Next>. By default, the port number is 5432. Note that this port number can be changed in the future but we recommend to use the default port number itself.

Step 7: Select the language of your ORDBMS and click Next>. By default, it is set to <Default locale> which means that it will pick up your system language for the setup.

Step 8: Review the summary of your installation choices, click Next> and sit back and let the installation complete. It may take a few minutes to complete the installation.

Step 9: After the installation is complete, you may uncheck the box to launch “stack builder” if you do not wish to install addons at the moment, if you are comfortable with installing add-on at the moment, you can go ahead and launch it by checking the box.

Finally: Click Finish!

Verifying the installation

There are a number of ways to verify successful installation of postgreSQL.

1. Checking windows services
   Go to search>services: and look for postgreSQL running as a service. If you see a running instance of postgreSQL in the list, you have successfully installed the ORDBMS.
2. PSQL (SQL shell)
   Go to search>psql: open the sql shell. On opening the shell, it will ask a couple of questions.
   Server[localhost]: (since the server is installed in the local host, nothing as to be done here, you can simply press enter)
   Database[postgres]: (By default, postgreSQL creates a database called postgre when we haven’t created any database. nothing has to be done at the moment, you can simply press enter)
   Port[5432]: (press enter)
   Username[postgres]: (press enter)
   Password for user postgres: type your super user password here (press enter)
   you can view databases by hitting \l in the shell. You can see a couple of default database created by postgreSQL even before you create any database. \q is used for quitting the shell.

Now to access postgre sql from anywhere in your system, you need to edit the environment variables of your system. First, open the folder where you’ve installed postgreSQL, by default it might be in C:\ Program Files. Now copy the path to the bin folder of postgreSQL. Also copy the path the the data folder in postgreSQL. Search for advanced system settings in windows settings and click on environment variables (see image below)

Now edit the “path” in your environment variable and add the path to the bin folder that you had copied earlier in the list of paths. Secondly, click on new and add a new variable with variable name PGDATA and variable value the same as the path to data folder copied earlier.

Viola your PostgreSQL setup is complete. Happy Engineering!