How to connect to PostgreSQL database [Complete tutorial]

In this PostgreSQL tutorial, we will learn how to connect to PostgreSQL database. Also, we will cover these topics.

How to Connect to PostgreSQL Database in Linux
Connect to PostgreSQL Database in Windows
How to connect PostgreSQL Database in Ubuntu Terminal
How to connect to PostgreSQL Database in AWS
How to connect to PostgreSQL Database in Azure
How to connect to PostgreSQL Database using SQL Developer
How to connect to PostgreSQL Database using Putty

Table of Contents

How to connect to PostgreSQL database in Linux

In this section, we’ll learn how to connect to the PostgreSQL database in the Linux RPM package. We’ll be discussing Terminal based connection. How to connect to PostgreSQL Database in Linux Debian Package.

use sudo -i -u postgres command to connect with the postgres user in PostgreSQL.
type psql to start the PostgreSQL terminal, once this window is open we can start typing queries.
Next step in the process is to create a database. We have created a database with the name ‘users‘.
Connect to PostgreSQL database in linux using \c followed by database name.
Here is the implementation of all the steps we have learned so far in RM package of Linux.

postgreSQL connect to database in rpm — How to connect to PostgreSQL database

Read: How to create a table in PostgreSQL [Terminal + pgAdmin]

How to Connect to PostgreSQL Database in Windows

In this section, we will learn how to connect to PostgreSQL Database in Windows. We will be using SQL Shell(psql) to create a database and then connect with the database.

We assume that you have already installed PostgreSQL on your machine. If not please refer to our blog Install PostgreSQL on Windows step by step
Open SQL Shell(psql) which is a command-line to write Postgres queries.

create database using command CREATE DATABASE users; here users is the name of the database we are going to create.
To connect to the PostgreSQL database in windows type, \c users here users is the name of the database we have created in the previous step.
This is how we can connect to the Postgres Database in Windows.

Read How to create database in PostgreSQL

How to Connect PostgreSQL Database in Ubuntu Terminal

In this section, we will cover how to connect to PostgreSQL Database in Ubuntu operating system using Terminal.
We assume that you have installed that PostgreSQL on your machine. If not then check out PostgreSQL installation on Linux step by step
Postgres by default has 3 databases, out of them one is with the name ‘Postgres’, and the user account is also created with the same name.
First, we’ll show you how to connect with a user account in PostgreSQL.
Open Terminal on your Ubuntu Linux distribution (Ctrl+Alt+T) and type the following command.

sudo -i -u postgres

Here -u is the username in Postgres. If you are performing this step for the first time then you will also have Postgres as the user account name.

PostgreSQL connect to postgres user account — Connect to PostgreSQL User Account

The next step, in the process, is to start psql which is the PostgreSQL command line. This will allow you to write and execute the PostgreSQL queries.
Type psql on the terminal, you will see postgres=#, Now you can start typing the queries.

PostgreSQL connect to postgres database — Connect to the PostgreSQL Database

You can not to the database directly by passing psql command after the user account. In the below syntax, Postgres is the name of the user account.

sudo -i -u postgres psql

The first query we are going to pass is to create a new database and then we’ll connect to that database. Follow the below code snippet to create a database in PostgreSQL, users is the name of the database we are creating.

CREATE DATABASE users;

If the database already exists then, PostgreSQL will show a message database "<database name>" already exists.
You can connect to the database using a command \c users; here ‘users’ is the name of the database.
If you will see a message confirming that you are connected with the database also you will notice that postgres=# has changed into users=# here ‘users‘ is the database name we are connected to.

PostgreSQL connect to database — connect to the Postgres Database

How to Connect to Postgres Database in AWS

In this section, we will learn how to connect to Postgres Database in AWS. We assume that you have a basic understanding of installing and using pgAdmin also you are familiar with the AWS interface.

Here are the steps to connect Postgres Database in AWS, we have highlighted the important tags, labels, and buttons that appear in the process.

From the aws Resource Groups, navigate to the database section and select RDS.
Resources screen will appear, Click on DB instances (0/40)
Database screen will appear, Click on the create database button highlighted in orange.
Choose a database creation mode screen that will appear, and select Standard Create radio button.
From the Engine Options, Select PostgreSQL.
In the Templates, you can choose as per your preference. In our case, we’ll go with the Free tier
Under settings, provide a unique name for DB instance identifier, in our case, it is sqlserverguides.
Generate login ID and Password under Credentials Settings.
Since we have selected the Free tier, in the DB instance size section we’ll be able to see the allocated resource.
For storage, we’ll leave it to default (20GB SSD).
Leave the Storage autoscaling section as it is.
Since it is a Free tier, so we won’t be able to make changes in Availability & durability section.
Leave connectivity as default
Under the Additional Connectivity configuration select yes for Public access. This will allow us to connect the server with any device. leave remaining things to default in this section.
In Database authentication select Password authentication.
Under Additional configuration, provide the database name (initial database name). Leave the rest as it is. Our database name is “serverguides”
Click on the create database button at the bottom of the page, It may take some time.
meanwhile, Download & install PgAdmin on your computer.
once the status changed from creating.. to db.something then click on the identifier. In our case, it is sqlserverguides.
copy the text below endpoint and go to pgAdmin.
create a server by clicking on Add new server and paste the endpoint in the host.
provide database name (sqlguides) under maintenance database. Also, provide the login Id and password and click on save.
If all the information is correct then the database will be connected.
Now using the pgAdmin interface you can create tables and execute queries.
This is how we can connect to the PostgreSQL database in AWS.

How to Connect to PostgreSQL Database in Azure

In this section, we will learn how to connect to Postgres Database in Microsoft Azure. We assume that you have a basic understanding of installing and using pgAdmin also familiar with the Azure interface.

Here are the steps to connect Postgres Database in Azure, we have highlighted the important tags, labels, and buttons that appear in the process.

From the Azure portal search for Postgres, click on Azure Database for PostgreSQL servers under services.
From the Azure Database for PostgreSQL servers window, click on Add.
Two options will appear Single server and Hyperscale (citus) server group. We’ll click on create for the Single server.
Provide name under Resource group. In our case, Sqlserverguides.
provide server name ( it should be lowercase and have more than 3 characters).
Leave the data source to None
Provide the server location, In our case, it is (US) East US
select version to the latest, 13 is the latest in our case.
In Compute + storage click on the configure server and choose Basic, adjust the core, storage, and Backup Retention Period as per the preference. (1 core, 20 GB, 15 days)
Under Administrator account, create username and password.
Click on the Next: Additional Settings > button at the bottom of the page.
Click on Next: Tags> button
Click on the Review + create button at the bottom of the page.
Click on the create button at the bottom of the page. It may take few minutes to complete.
Once deployment is complete, click on the Resource button.
by default, the server is not publicly accessible so we have to set it up manually.
Click on the Connection security from the left menu bar under settings.
Set Allow access to Azure services to Yes and save it.
Once the Connection Security is updated click on the overview from the left menu.
You will see the necessary information on the screen.
copy the server name and create a database in pgAdmin (provide the same database name which you provided in Azure) and under host paste the server name. Provide the Admin Username and Password.
If all the information is correct, you will be able to connect to the PostgreSQL database in Azure.
Now using the pgAdmin interface you can create tables and execute queries.
This is how we can connect to the PostgreSQL database in Azure.

Read: How to Restart PostgreSQL (Linux, Windows, Mac)

How to Connect to PostgreSQL Database using SQL Developer

In this section, we will learn how to connect to a PostgreSQL database using SQL Developer. We are using Oracle developer and assume that PostgreSQL and JDK are installed in your system.

Click on the plus (+) icon on the top left corner
A new/Select Database connection window will appear. Fill in the name for the database, Select database type as PostgreSQL, provide a username, password, Hostname (localhost or IP address), provide SJD, and click on the Test button.
If the status shows Success click on the connection or you choose the database
This is how you can connect to PostgreSQL Database using SQL Developer

How to Connect to PostgreSQL Database using Putty

PuTTY is software that facilitates connection with the server using the SSH client. A server could be AWS, Azure, Digital Ocean, or it could be any Virtual private Server (VPS). In this section, we will use PuTTy to connect to the PostgreSQL database that is present on a server.

Connect to PostgreSQL Database using Putty

We have set up a virtual machine with Ubuntu Server on it then we have installed PostgreSQL on Ubuntu Server.
Using Putty we’ll connect to the server and then further we’ll connect to the PostgreSQL database present on Ubuntu Server Machine.
Download Putty on the windows operating system.
Linux users can install Putty by using the following command on the terminal:

sudo apt install putty -y

Here is the picture of the Ubuntu Server running on the Oracle VirtualBox.
Note: This machine is destroyed after publishing this blog.

Connect to PostgreSQL Database using Putty — Ubuntu Server Running on Oracle VirtualBox

We assume that you have successfully installed the Putty in your respective software. We are performing connections using Linux but the process and commands will be the same for both Windows & Mac users.
Once you have opened Putty then it will ask for the Host Name (or IP address). The hostname is the combination of username and password. Provide the username you want to use and then IP address with a separated @ sign.

main@192.168.0.103

Make sure the port is set to 22 and SSH radio button is selected. Click on the Open button.

How to connect to PostgreSQL Database using Putty — Connect to PostgreSQL Database using Putty

A window will prompt for the server password. Provide the password for that user. Like in our case we are connecting to the user: main, so we’ll provide the password for this user.

If the password is correct then you will be connected to the sever and this screen will appear.

Connect to postgres database command line linux

Once you have successfully connected to the Ubuntu Server now it’s time to connect with the PostgreSQL. Type the following command to connect with Ubuntu.

sudo -i -u postgres psql

Create database using CREATE DATABASE <DB_NAME>; statement in PostgreSQL.
In our case, we have created a database with the name of users. Now to connect with the database use \c followed by database name.

# create database
CREATE DATABASE users;

# connect to the database
\c users

Here is the implementation of how to connect to database using Putty.