How to connect PostgreSQL database

In this comprehensive article, I’ll guide you through various approaches to connecting to a PostgreSQL database and share best practices I’ve gained experience with.

How to connect PostgreSQL database

Case 1: In Linux

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

use sudo -i -u postgres A 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.
The next step in the process is to create a database. We have created a database named ‘users‘.
Connect to the PostgreSQL database in Linux using \c the following command, followed by the database name.
Here is the implementation of all the steps we have learned so far in the Linux RM package.

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

Case 2: In Windows

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

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 tool to write Postgres queries.

Create a database using the command’ CREATE DATABASE users; ‘ here, ‘ users’ is the name of the database we are creating.
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 a database in PostgreSQL

Case 3: In Ubuntu Terminal

In this section, we will cover how to connect to a PostgreSQL Database in the Ubuntu operating system using the Terminal.
We assume that you have installed PostgreSQL on your machine. If not, then check out the PostgreSQL installation on Linux step by step
Postgres by default has 3 databases, out of them one is named ‘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.

how to connect to postgresql database in windows

The next step in the process is to start psql, the PostgreSQL command-line interface. 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.

how to connect to postgresql database using psql

You can not connect to the database directly by passing a 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’ll pass is to create a new database, and then we’ll connect to that database. Follow the code snippet below 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 see a message confirming that you are connected to the database, you will also notice that postgres # has changed into users #. Here, ‘users’ is the database name we are connected to.

Case 4: In AWS

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

Here are the steps. We have highlighted the critical 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)
The database screen will appear. Click on the “Create Database” button, highlighted in orange.
Select the database creation mode screen that appears, and then select the 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 the DB instance identifier; in our case, it is sqlserverguides.
Generate a 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 resources.
For storage, we’ll leave it to default (20GB SSD).
Leave the Storage autoscaling section unchanged.
Since it is a Free tier, we won’t be able to make changes in the 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 the 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 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 is clicking on the identifier. In our case, it is SQLServerGuides.
Copy the text below the endpoint and go to pgAdmin.
Create a server by clicking ‘Add new server’ and pasting the endpoint in the ‘Host’ field.
Provide the database name (sqlguides) under the maintenance database. Also, provide the login ID and password, and then click 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.

Case 5: In Azure

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

Here are the steps to connect a 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’ and 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 the name under the Resource group. In our case, SQL Server Guides.
Provide the 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 the US East US
select version to the latest, 13 is the latest in our case.
In Compute + Storage, click ‘Configure Server’ and choose ‘Basic.’ Adjust the core, storage, and Backup Retention Period according to your preference. (1 core, 20 GB, 15 days)
Under the Administrator account, create a username and password.
Click on the Next: Additional Settings > button at the bottom of the page.
Click on the 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 a 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 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 that you supplied 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)

Case 6: 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 on your system.

Click on the plus (+) icon in 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 select the database.
This is how you can connect to a PostgreSQL Database using SQL Developer

Case 7: Using Putty

PuTTY is software that facilitates connections with servers using the SSH client. A server could be AWS, Azure, DigitalOcean, or any Virtual Private Server (VPS). In this section, we will use PuTTY to connect to the PostgreSQL database running on a server.

We have set up a virtual machine with Ubuntu Server installed, and then we have installed PostgreSQL on top of it.
Using Putty, we’ll connect to the server, and then further we’ll connect to the PostgreSQL database present on the 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.

how to connect to postgresql database using pgadmin

We assume that you have successfully installed 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, it will ask for the Host Name (or IP address). The hostname is the combination of the username and password. Provide the username you want to use, followed by the IP address, separated by an @ sign.

main@192.168.0.103

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

how to connect azure postgresql database

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

how to connect postgresql database in visual studio

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

how to connect to postgresql database in python

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

sudo -i -u postgres psql

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

# create database
CREATE DATABASE users;

# connect to the database
\c users

Case 8: Connecting via pgAdmin

pgAdmin is a popular graphical user interface for PostgreSQL that simplifies database management with its intuitive interface.

Installing pgAdmin

Visit the official pgAdmin website to download the latest version for your operating system. It’s available for Windows, macOS, and Linux.

Connecting to PostgreSQL using pgAdmin

Launch pgAdmin on your computer
Right-click on “Servers” in the left sidebar and select “Create” > “Server…”
In the “General” tab, provide a name for your connection
Switch to the “Connection” tab and enter:
- Host name/address (e.g., localhost or IP address)
- Port (default is 5432)
- Maintenance database (usually “postgres”)
- Username
- Password
Click “Save.”

PgAdmin is a powerful web-based interface that significantly simplifies database management tasks. Its visual nature makes it particularly useful for those who prefer a GUI over command-line interfaces.

Case 9: Connecting to Remote PostgreSQL Databases

In many production environments, you’ll need to connect to PostgreSQL databases running on remote servers. This requires additional configuration to ensure secure access.

Enable Remote Connections

First, you need to configure PostgreSQL to accept remote connections by editing two files:

postgresql.conf: Set listen_addresses = ‘*’ to listen on all available IP addresses
pg_hba.conf: Add entries to allow connections from specific IP addresses

Connecting to a remote PostgreSQL database requires proper configuration of these files to ensure both accessibility and security.

Using SSH Tunneling

For added security, you can use SSH tunneling to connect to a remote PostgreSQL server:

ssh -L 5433:localhost:5432 username@remote_server

This command creates a secure tunnel from your local port 5433 to the PostgreSQL port (5432) on the remote server. You can then connect to your local port 5433 as if it were a local PostgreSQL instance.

Best Practices

Based on my experience managing production PostgreSQL deployments, here are some best practices I recommend:

Connection Pooling

For applications with many concurrent users, implement connection pooling to manage database connections efficiently. Tools like PgBouncer and Pgpool-II are excellent choices.

Security Considerations

Never expose your PostgreSQL server directly to the internet
Use strong, unique passwords for database users
Implement SSL for all connections
Use least-privilege principles when creating database users
Regularly audit database access logs

Performance Optimization

Monitor connection counts and resource usage
Optimize connection parameters like max_connections and work_mem
Consider using prepared statements to reduce parsing overhead
Close connections when they’re no longer needed

Conclusion

Connecting to a PostgreSQL database is a fundamental skill for anyone working with data.

By following the techniques and best practices outlined in this article, you’ll be able to establish reliable, secure connections to your PostgreSQL databases.