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 systemctl start postgresql

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.

Connecting to PostgreSql server

Checking PostgreSQL Server Status

Before connecting to the PostgreSQL server, ensure that the server is running by checking its status. Use the following commands based on your operating system:

• Windows:

  pg_ctl -D "C:\Program Files\PostgreSQL\15\data" status

• Linux:

  sudo systemctl status postgresql

• macOS:
  You can use either of the following commands:

  brew services list | grep postgresql

  ps -x | grep postgresql

Connecting to the PostgreSQL Database

or Login in as User postgres in Postgresql

Once you have confirmed that the PostgreSQL server is running, you can connect to the database server using the psql command. Here are two ways to do it:

1. Basic Connection: psql -U postgres

2. Specifying the Database: psql -U postgres -d postgres

If the database name and the username are the same, you can omit the database name from the command.

C:\Users\sneha>psql -U postgres
psql (17.0)
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=# \q

C:\Users\sneha>psql -U postgres -d postgres
psql (17.0)
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=# \q

You can login as 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.

You can disable the Console code page (437) warning by using the following command: chcp 1252 This change will only apply to the current session.

C:\Users\sneha>chcp 1252
Active code page: 1252

C:\Users\sneha>chcp
Active code page: 1252

C:\Users\sneha>psql -U postgres
psql (17.0)
Type "help" for help.

postgres=# \q

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

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.