January 27, 2026Guides

How to Install PostgreSQL on Linux Server

Complete guide on installing, configuring, and securing PostgreSQL database server on Ubuntu and CentOS.

PostgreSQL is a powerful, open-source relational database management system known for its reliability, feature richness, and standards compliance. This guide will help you install and configure PostgreSQL on your Hiddence Linux server.

Installing PostgreSQL on Ubuntu/Debian

PostgreSQL is available in the default repositories. Install the latest version:

bash

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

# Start and enable PostgreSQL
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verify installation
sudo systemctl status postgresql
psql --version

Installing PostgreSQL on RHEL / CentOS / AlmaLinux / Rocky Linux

For RHEL-based distributions, you need to add the PostgreSQL repository first:

bash

# Install PostgreSQL repository
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# For CentOS 8/Rocky Linux 8, use:
# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL
sudo dnf install -y postgresql15-server postgresql15

# Initialize database
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

# Start and enable PostgreSQL
sudo systemctl start postgresql-15
sudo systemctl enable postgresql-15

# Verify installation
sudo systemctl status postgresql-15
psql --version

Verifying Installation

bash

# Check PostgreSQL status
sudo systemctl status postgresql

# Check PostgreSQL version
psql --version

# Connect to PostgreSQL (Ubuntu/Debian)
sudo -u postgres psql

# Connect to PostgreSQL (CentOS/RHEL)
sudo -u postgres psql -d postgres

Securing PostgreSQL

Set a password for the postgres user:

bash

# Connect to PostgreSQL
sudo -u postgres psql

# Set password for postgres user
ALTER USER postgres PASSWORD 'your_strong_password';

# Exit PostgreSQL
\q

Creating Database and User

bash

# Connect as postgres user
sudo -u postgres psql

# Create database
CREATE DATABASE myapp_db;

# Create user
CREATE USER app_user WITH PASSWORD 'strong_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO app_user;

# Exit
\q

Managing Users

bash

# Connect to PostgreSQL
sudo -u postgres psql

# List all users
\du

# Create new user
CREATE USER newuser WITH PASSWORD 'password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO newuser;

# Revoke privileges
REVOKE ALL PRIVILEGES ON DATABASE myapp_db FROM newuser;

# Delete user
DROP USER newuser;

Enabling Remote Connections

To allow remote connections, edit PostgreSQL configuration files:

bash

# Edit pg_hba.conf (Ubuntu/Debian)
sudo nano /etc/postgresql/15/main/pg_hba.conf

# Edit pg_hba.conf (CentOS/RHEL)
sudo nano /var/lib/pgsql/15/data/pg_hba.conf

# Add line:
host    all             all             0.0.0.0/0               md5

# Edit postgresql.conf (Ubuntu/Debian)
sudo nano /etc/postgresql/15/main/postgresql.conf

# Edit postgresql.conf (CentOS/RHEL)
sudo nano /var/lib/pgsql/15/data/postgresql.conf

# Change:
listen_addresses = '*'

# Restart PostgreSQL
sudo systemctl restart postgresql

Basic PostgreSQL Commands

bash

# Connect to database
psql -U app_user -d myapp_db

# List databases
\l

# Connect to database
\c database_name

# List tables
\dt

# Describe table
\d table_name

# Execute SQL file
psql -U app_user -d myapp_db -f script.sql

# Backup database
pg_dump -U app_user myapp_db > backup.sql

# Restore database
psql -U app_user -d myapp_db < backup.sql

Useful Tips

Always use strong passwords for database users
Limit remote access to specific IP addresses in pg_hba.conf
Regularly backup your databases using pg_dump
Monitor PostgreSQL logs: /var/log/postgresql/ (Ubuntu) or /var/lib/pgsql/15/data/log/ (CentOS)
Use connection pooling (pgBouncer) for high-traffic applications
Keep PostgreSQL updated to the latest stable version for security patches