Postgresql Guide – Getting Started Guide

In this blog, we are going to discuss how to install PostgreSQL, verify the installation, start the database server, create database, create basic table, insert data into the table, see list of databases, see list of tables, see list of users, login as particular user, run sql query on table etc. Basically this blog will help you with PostgreSQL learning. We will discuss only basics. We will keep it simple to help you learn the basics.

First install Postgresql

Installing Postgresql on Linux ( Ubuntu/Debian)

You can run the following command to install PostgreSQL on Ubuntu/Debian based Linus operating system.

sudo apt-get update
sudo apt-get install postgresql

Installing Postgresql on Linux (CentOS/RHEL):

You can follow the instructions on https://www.postgresql.org/download/linux/redhat/ , you can enter details of your operating system and they will give you corresponding commands to run.

Installing Postgresql on macOS

For macOS, you can use the Homebrew package manager to install PostgreSQL.

brew install postgresql

Installing Postgresql on windows

On Windows, you can download the PostgreSQL installer from the official website and follow the installation process: PostgreSQL Downloads for Windows

Add path to environment variable

To log in to PostgreSQL from anywhere in system path, make sure to add PostgreSQL installation path C:\Program Files\PostgreSQL\15\bin to path environment variable. replace 15 with first two digit of version of your installation . This will make it lot easier to log in to PostgreSQL .

Verifying installation

Once installed, you can verify the installation by running psql --version command in the terminal.

Starting the Database Server:

After installation, the PostgreSQL database server may not start automatically. You can start it manually using the following command:

On Linux you can start by running the command sudo service postgresql start

On macOS you can start by running the command brew services start postgresql

On windows you can start the PostgreSQL service using the Services application(search for services in windows search) or by running this below command in the command prompt:

pg_ctl -D "C:\Program Files\PostgreSQL\<version>\data" start

Replace <version> with your PostgreSQL version number. In my case, it was version 15.3 , so command will be pg_ctl -D "C:\Program Files\PostgreSQL\15\data" start , you need to use first two digits of version.

Set pgpass.conf File for auto Login

Go inside the directory C:\Users\codingissimple\AppData\Roaming\postgresql\pgpass.conf , add below line in the file pgpass.conf (create the file if not found)

localhost:5432:postgres:postgres:password

Replace password with actual password for user postgres that you have set at the time of installation.

Database in Postgresql

Create database from command line

To create database from command line run command createdb -U postgres -h localhost new_database , this will create new_database for user postgres .

C:\Users\codingissimple>createdb -U postgres -h localhost new_database

If you have user codingissimple user is available on postgreSQL then you can simply run createdb new_database, this will create new_database for user codingissimple .i.e you need to have the same user in database as your windows user for it to work.

You can also use command psql -U postgres -c "CREATE DATABASE new_database;" to create the database from command line. -c allows to run sql command.

C:\Users\codingissimple>psql -U postgres -c "CREATE DATABASE mydatabase;"
CREATE DATABASE

Login in as User postgres in Postgresql

To login as postgres run the command psql -U postgres

C:\Users\codingissimple>psql -U postgres
psql (15.3)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#

You can login is different user using the same command, just replace postgres with the username. But as you are just starting you won’t have other users right now. We will come back to it again later in this blog.

View list of databases

To view list of all database, run the command \l , you will see the new_database in the list that we had created previously.