DEV Community

Sharon Ndubai
Sharon Ndubai

Posted on

PostgreSQL Installation on a Linux Server (Ubuntu/Debian)

PostgreSQL is a powerful, open-source, object-relational database system known for its reliability, robust feature set, and adherence to SQL standards. It's a popular choice for various applications — from web development to data warehousing — due to its stability, data integrity, and extensibility.

Unlike Windows, where you use a graphical installer and manage services through the Control Panel, Linux installation involves using the terminal, package managers, and manual configuration of system services.

✅ Step-by-Step: Install PostgreSQL on a Linux Server

Prerequisites

  • A Linux server (Ubuntu/Debian)
  • Terminal access with sudo or root privileges
  • Internet connection

Step 1: Update the System

Run the following command to update your system's package list and upgrade existing packages:

sudo apt update && sudo apt upgrade -y
Enter fullscreen mode Exit fullscreen mode

Step 2: Install PostgreSQL and Contrib Package

Install PostgreSQL along with additional useful features (postgresql-contrib):

sudo apt install postgresql postgresql-contrib -y
Enter fullscreen mode Exit fullscreen mode

Step 3: Check PostgreSQL Version and Service Status

Confirm the installation and ensure the service is running:

psql --version
sudo systemctl status postgresql
Enter fullscreen mode Exit fullscreen mode

Start and enable PostgreSQL to run on boot:

sudo systemctl start postgresql
sudo systemctl enable postgresql
Enter fullscreen mode Exit fullscreen mode

Step 4: Switch to the postgres User

PostgreSQL creates a default superuser named postgres. Switch to it:

sudo -i -u postgres
Enter fullscreen mode Exit fullscreen mode

Access the PostgreSQL shell:

psql
Enter fullscreen mode Exit fullscreen mode

Exit the shell:

\q
Enter fullscreen mode Exit fullscreen mode

Return to your normal user:

exit
Enter fullscreen mode Exit fullscreen mode

Step 5: Set Password for postgres User

Run the following:

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

Inside the shell, set a password:

ALTER USER postgres WITH PASSWORD 'your_secure_password';
\q
Enter fullscreen mode Exit fullscreen mode

Step 6: Enable Remote Access (Optional)

6.1 Edit postgresql.conf 
sudo nano /etc/postgresql/14/main/postgresql.conf
Enter fullscreen mode Exit fullscreen mode

Change:

#listen_addresses = 'localhost'
Enter fullscreen mode Exit fullscreen mode

To:

listen_addresses = '*'
Enter fullscreen mode Exit fullscreen mode

Save and exit.

6.2 Edit pg_hba.conf 
sudo nano /etc/postgresql/14/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

Add this line at the bottom:

host    all             all             0.0.0.0/0               md5
Enter fullscreen mode Exit fullscreen mode

Restart PostgreSQL:

sudo systemctl restart postgresql
Enter fullscreen mode Exit fullscreen mode

Allow PostgreSQL port through firewall:

sudo ufw allow 5432/tcp
Enter fullscreen mode Exit fullscreen mode

Step 7: Create a New Database and User

Switch to the postgres user:

sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

Then run:

CREATE DATABASE testdb;
CREATE USER testuser WITH ENCRYPTED PASSWORD 'testpass';
GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
\q
Enter fullscreen mode Exit fullscreen mode

Step 8: Connect to PostgreSQL as the New User 

psql -U testuser -d testdb -h localhost -W
Enter fullscreen mode Exit fullscreen mode

Top comments (0)