Installing and configuring PostgreSQL on Ubuntu Linux involves a series of steps. Here’s a step-by-step guide:

Step 1: Update Package List

Make sure your package list is up-to-date.

sudo apt update

Step 2: Install PostgreSQL

Run the following command to install PostgreSQL

sudo apt install postgresql postgresql-contrib

This will install the PostgreSQL database server and additional components.

Step 3: Verify Installation

PostgreSQL should start automatically after installation. You can check its status using:

sudo systemctl status postgresql

Step 4: Access PostgreSQL

By default, PostgreSQL creates a user named postgres. To access the PostgreSQL prompt, switch to this user and use the psql command:

sudo -u postgres psql

Step 5: Create a Database and User

Inside the PostgreSQL prompt, you can create a new database and user by running the following commands one at a time on your terminal:

CREATE DATABASE your_database_name;
CREATE USER your_username WITH PASSWORD 'your_password';
ALTER ROLE your_username SET client_encoding TO 'utf8';
ALTER ROLE your_username SET default_transaction_isolation TO 'read committed';
ALTER ROLE your_username SET timezone TO 'UTC';
GRANT ALL PRIVILEGES ON DATABASE your_database_name TO your_username;

Make sure to replace your_database_name, your_username, and your_password with your desired values.

Step 6: Exit PostgreSQL Prompt

Exit the PostgreSQL prompt:

\q

Step 7: Adjust PostgreSQL Configuration (Optional)

If needed, you can adjust PostgreSQL’s configuration located under this path:

sudo nano /etc/postgresql/{version}/main/postgresql.conf

The postgresql.conf file will be opened by the above command and you should be able to see something like this:

# PostgreSQL configuration file

# -----------------------------
# CONNECTIONS AND AUTHENTICATION
# -----------------------------

# - Connection Settings -
listen_addresses = 'localhost'   # comma-separated list of addresses

# - Security and Authentication -
#authentication_timeout = 1min  
#password_encryption = md5     
#ssl = on                      

# ...

# -----------------
# RESOURCE USAGE (TO BE SET IN SESSIONS)
# -----------------

# - Memory -
shared_buffers = 128MB          # min 128kB
#temp_buffers = 8MB             
#work_mem = 4MB                 

# ...

# ----------------------
# WRITE AHEAD LOG
# ----------------------

# - Write Ahead Log -
#wal_level = minimal           
#fsync = on                    
#synchronous_commit = on       
#wal_sync_method = fsync        
#...

# ...

# -----------------
# QUERY TUNING
# -----------------

# - Planner Method Configuration -
#enable_seqscan = on           
#enable_indexscan = on         
#...

# ...

# -----------------
# ERROR REPORTING AND LOGGING
# -----------------

# - Where to Log -
#log_destination = 'stderr'    
#...

# ...

# -----------------
# CUSTOMIZED OPTIONS
# -----------------

# Add custom options here

# ...

# -----------------
# Miscellaneous
# -----------------

# ...

# Include files from directory etc.
#include_dir = 'conf.d'

Enable Remote Access (Optional)

If you want to enable remote access to your PostgreSQL server, you need to modify the postgresql.conf file. Open the file in a text editor:

sudo nano /etc/postgresql/{version}/main/postgresql.conf

Add the following line to allow connections from any IP address:

host    all             all             0.0.0.0/0               md5

The line “host all all 0.0.0.0/0 md5” in the PostgreSQL postgresql.conf file specifies a host-based authentication rule, allowing connections to all databases (all) from any IP address (0.0.0.0/0). The authentication method is set to “md5,” indicating that a password will be required for authentication. This rule is often used to enable remote access to the PostgreSQL server, allowing connections from any IP address while ensuring authentication through password verification.

Step 8: Restart PostgreSQL

After making changes to the configuration, restart PostgreSQL:

sudo systemctl restart postgresql

Step 10: Firewall Configuration (Optional)

If you are using a firewall, you may need to open the PostgreSQL port (default is 5432). Use the following command to allow traffic on port 5432:

sudo ufw allow 5432