Setup PostgreSQL

Introduction

This guide will walk you through installing PostgreSQL on Ubuntu, creating users and databases, and configuring it for remote connections. I've made sure to provide clear steps that work on the latest Ubuntu versions.

Installing PostgreSQL

Update your package lists and install PostgreSQL:

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Verifying Installation

Check that PostgreSQL is running properly:

sudo systemctl status postgresql

You should see output with "active (running)" in green text.

Creating a Database and User

Log in as the PostgreSQL admin user

sudo -u postgres psql

You'll now be at the PostgreSQL prompt (postgres=#).

Create a new database

CREATE DATABASE your_database_name;

Create a new user (role) with a password

CREATE USER your_username WITH PASSWORD 'your_password';

Grant privileges to the user

GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;

Exit the PostgreSQL prompt

\q

Verify your new database and user

To check if everything was created correctly, log back in:

sudo -u postgres psql

List all databases:

\l

List all users:

\du

You should see your new database and user in these lists.

Configuring Remote Access

By default, PostgreSQL only accepts connections from the local machine. To allow remote connections:

Edit the PostgreSQL configuration file

Find your PostgreSQL version first:

ls /etc/postgresql/

You'll see a version number like 12, 13, or 14.

Now edit the configuration file (replace 14 with your version):

sudo nano /etc/postgresql/14/main/postgresql.conf

Find the line:

#listen_addresses = 'localhost'

Change it to:

listen_addresses = '*'

Save and exit (Ctrl+O, then Ctrl+X).

Configure client authentication

Edit the client authentication file:

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

Add these lines at the end of the file:

# Allow remote connections
host    all             all             0.0.0.0/0               md5

Note: For PostgreSQL 14+, you can use scram-sha-256 instead of md5 for stronger password encryption.

Restart PostgreSQL to apply changes

sudo systemctl restart postgresql

Open the firewall for PostgreSQL

If you have a firewall enabled, allow connections to the PostgreSQL port:

sudo ufw allow 5432/tcp

Connection String Format

To connect to your PostgreSQL database from applications, use this connection string format:

postgres://username:password@hostname:port/database_name

Example:

postgres://your_username:your_password@192.168.1.100:5432/your_database_name

Where:

• username: Your PostgreSQL user

• password: Your user's password

• hostname: Your server's IP address or domain name

• port: PostgreSQL port (default is 5432)

• database_name: Your database name

Note: Some applications use postgresql:// instead of postgres:// at the beginning.

Basic PostgreSQL Commands

Here are some essential PostgreSQL commands:

Connecting to a database

psql -h hostname -U username -d database_name

If you're on the local machine:

psql -U username -d database_name

Inside psql:

Command	Description
\l	List all databases
\c database_name	Connect to a specific database
\dt	List all tables in the current database
\du	List all users (roles)
\d table_name	Describe a table
\?	Show help
\q	Quit psql

Troubleshooting

Can't connect remotely

1. Verify PostgreSQL is listening on all interfaces:

   sudo netstat -tuln | grep 5432

   You should see 0.0.0.0:5432 in the output.

   If netstat is not available, install it:

   sudo apt install net-tools

2. Check your firewall settings:

   sudo ufw status

3. Ensure the client authentication file (pg_hba.conf) contains the correct entry for remote connections.

Authentication failed

1. Double-check username and password

2. Ensure you've granted correct privileges to the user

3. Check if the user is allowed to connect from your client's IP address in pg_hba.conf

Database doesn't exist or permission denied

1. Make sure the database exists:

   \l

2. Check that your user has the necessary permissions:

   SELECT datname, datacl FROM pg_database WHERE datname = 'your_database_name';

Permission denied for schema public

1. Example database_url: postgres://root:gradient@127.0.0.1:5432/gradient

2. Commands for fixing:

sudo -u postgres psql
\c gradient
GRANT ALL ON SCHEMA public TO root;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO root;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO root;
ALTER SCHEMA public OWNER TO root;

Quick Reference: Common Commands

Service management

# Start PostgreSQL
sudo systemctl start postgresql

# Stop PostgreSQL
sudo systemctl stop postgresql

# Restart PostgreSQL
sudo systemctl restart postgresql

# Check status
sudo systemctl status postgresql

Database operations

# Backup a database
pg_dump -U username database_name > backup.sql

# Restore a database
psql -U username database_name < backup.sql