Connect To a PostgreSQL Database Server

How to Connect to a PostgreSQL Database Server

You can connect to a PostgreSQL database using various methods:

1. psql (Command Line)
2. pgAdmin (Graphical Interface)
3. Using a Programming Language (Python, Node.js, etc.)
4. Remote Connection with psql

1. Connect Using psql (Command Line)

The psql tool is the default PostgreSQL command-line client.

Check if PostgreSQL is Running

Before connecting, ensure PostgreSQL is running:

sudo systemctl status postgresql  # For Linux
brew services list                 # For macOS (Homebrew)

Basic Connection Syntax

psql -h hostname -p port -U username -d database_name

• -h → Host (e.g., localhost or remote server IP)
• -p → Port (default: 5432)
• -U → Username
• -d → Database name

Example: Connect to Local PostgreSQL

psql -U postgres -d mydatabase

If prompted, enter your PostgreSQL password.

Example: Connect to a Remote PostgreSQL Server

psql -h 192.168.1.100 -p 5432 -U myuser -d mydatabase

🔹 Ensure PostgreSQL allows remote connections (see step 4 below).

List All Databases

Once connected, type:

\l

Switch to a Database

\c mydatabase

Exit psql

\q

2. Connect Using pgAdmin (Graphical Interface)

pgAdmin is a GUI for managing PostgreSQL.

Steps to Connect

1. Open pgAdmin and go to "Servers" → "Create" → "Server".
2. Under the General tab, enter a name (e.g., Local PostgreSQL).
3. Under the Connection tab:
   • Host: localhost (or remote IP)
   • Port: 5432
   • Username: postgres
   • Password: (Enter your password)
4. Click Save, then Connect.

3. Connect Using a Programming Language

Python (psycopg2)

First, install psycopg2:

pip install psycopg2

Then, connect:

import psycopg2

conn = psycopg2.connect(
    dbname="mydatabase",
    user="postgres",
    password="mypassword",
    host="localhost",
    port="5432"
)

cur = conn.cursor()
cur.execute("SELECT version();")
print(cur.fetchone())

cur.close()
conn.close()

Node.js (pg package)

Install the package:

npm install pg

Then, connect:

const { Client } = require('pg');

const client = new Client({
  user: "postgres",
  host: "localhost",
  database: "mydatabase",
  password: "mypassword",
  port: 5432
});

client.connect()
  .then(() => console.log("Connected to PostgreSQL"))
  .catch(err => console.error("Connection error", err.stack))
  .finally(() => client.end());

4. Allow Remote Connections to PostgreSQL

By default, PostgreSQL only accepts local connections. To allow remote access:

Step 1: Edit postgresql.conf

Find your PostgreSQL configuration file:

sudo nano /etc/postgresql/15/main/postgresql.conf  # Ubuntu/Debian
sudo nano /var/lib/pgsql/data/postgresql.conf      # RHEL/CentOS

Change:

listen_addresses = 'localhost'

To:

listen_addresses = '*'

Or specify an IP:

listen_addresses = '192.168.1.100'

Step 2: Edit pg_hba.conf (Client Authentication)

Find pg_hba.conf:

sudo nano /etc/postgresql/15/main/pg_hba.conf

Add:

host    all     all     0.0.0.0/0       md5
host    all     all     ::/0            md5

This allows all IPs to connect (use a specific IP range for security).

Step 3: Restart PostgreSQL

sudo systemctl restart postgresql  # Linux
brew services restart postgresql   # macOS

Step 4: Open Port 5432 in the Firewall

For Ubuntu/Debian:

sudo ufw allow 5432/tcp

For CentOS/RHEL:

sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload

5. Troubleshooting Connection Issues

Issue	Solution
Connection refused	Ensure PostgreSQL is running (sudo systemctl status postgresql).
Role does not exist	Create the user (CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';).
Access denied for user	Ensure correct username/password. Check pg_hba.conf.
Remote connection not working	Enable remote access in postgresql.conf and open firewall ports.

6. Summary

Method	Command
Command-line (local)	psql -U postgres -d mydatabase
Command-line (remote)	psql -h 192.168.1.100 -p 5432 -U myuser -d mydatabase
List Databases	\l
Switch Database	\c mydatabase
Exit psql	\q
Python (psycopg2)	conn = psycopg2.connect(...)
Node.js (pg package)	const client = new Client({...}); client.connect();

Would you like a step-by-step guide for a specific operating system? 🚀