PostgreSQL Installation and Basic Management Guide

PostgreSQL is one of the most powerful open-source relational database management systems in the world. With ACID compliance, advanced data types, JSON support, and strong extensibility, it serves a wide range of use cases from enterprise applications to small projects. This guide covers PostgreSQL installation, basic management, and configuration.

Installation on Ubuntu/Debian

Installing from Package Repository

On Ubuntu and Debian systems, PostgreSQL can be installed from official repositories:

hljs bash

# Update package list
apt update

# Install PostgreSQL
apt install -y postgresql postgresql-contrib

# Check service status
systemctl status postgresql

# Enable automatic startup
systemctl enable postgresql

Using the Official PostgreSQL Repository

For a more recent version, you can add the official PostgreSQL repository:

hljs bash

# Install required tools
apt install -y curl ca-certificates

# Add PostgreSQL signing key
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Add repository
echo 'deb https://apt.postgresql.org/pub/repos/apt '$(lsb_release -cs)'-pgdg main' > /etc/apt/sources.list.d/pgdg.list

# Update and install
apt update
apt install -y postgresql-16

Installation on RHEL/CentOS/AlmaLinux

hljs bash

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

# Disable the built-in PostgreSQL module
dnf -qy module disable postgresql

# Install PostgreSQL 16
dnf install -y postgresql16-server postgresql16-contrib

# Initialize the database
/usr/pgsql-16/bin/postgresql-16-setup initdb

# Start and enable the service
systemctl enable --now postgresql-16

# Check service status
systemctl status postgresql-16

Verifying the PostgreSQL Version

hljs bash

psql --version
# Output: psql (PostgreSQL) 16.x

# Service info
psql -U postgres -c 'SELECT version();'

Switching to the postgres User and Connecting

During installation, a system user named postgres is created. To connect to the database:

hljs bash

# Switch to the postgres system user
su - postgres

# Connect to the PostgreSQL console
psql

# Connect directly as root
sudo -u postgres psql

# Connect to a specific database
sudo -u postgres psql -d mydb

Creating Users and Databases

Creating a New User

hljs sql

-- In the PostgreSQL console
-- Create a new user
CREATE USER appuser WITH PASSWORD 'StrongPassword123!';

-- Create a superuser (use with caution)
CREATE USER adminuser WITH PASSWORD 'AdminPass!' SUPERUSER;

-- List users
\du

-- Change user password
ALTER USER appuser WITH PASSWORD 'NewPassword456!';

-- Grant database creation privilege
ALTER USER appuser CREATEDB;

Creating a Database and Granting Privileges

hljs sql

-- Create a database
CREATE DATABASE myapp OWNER appuser ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TEMPLATE template0;

-- List databases
\l

-- Connect to a database
\c myapp

-- Grant full privileges to user
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- Schema privilege (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO appuser;

-- Drop a database
DROP DATABASE old_database;

Basic SQL Commands

hljs sql

-- Create a table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert data
INSERT INTO users (username, email) VALUES ('john', 'john@example.com');

-- Query data
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;

-- Update data
UPDATE users SET email = 'new@example.com' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 5;

-- List tables
\dt

-- View table structure
\d users

-- Create an index
CREATE INDEX idx_users_email ON users(email);

Remote Connection Configuration

By default, PostgreSQL only accepts connections from localhost. To enable remote access, you need to edit two files.

1. Edit postgresql.conf

hljs bash

# Find the config file location
sudo -u postgres psql -c 'SHOW config_file;'

# Edit
nano /etc/postgresql/16/main/postgresql.conf

hljs ini

# Change the listen_addresses line
listen_addresses = '*'

# Port (default 5432)
port = 5432

2. Edit pg_hba.conf

hljs bash

nano /etc/postgresql/16/main/pg_hba.conf

# Add a line for remote connections (at the end of the file)
# TYPE  DATABASE  USER      ADDRESS         METHOD
host    myapp     appuser   192.168.1.0/24  scram-sha-256
host    all       all       0.0.0.0/0       scram-sha-256

3. Restart Service and Configure Firewall

hljs bash

# Restart the service
systemctl restart postgresql

# Open port with UFW
ufw allow 5432/tcp

# With iptables
iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

Exposing the PostgreSQL port (5432) to the internet is a security risk. Use an SSH tunnel or VPN whenever possible. If remote access is required, restrict it to specific IP addresses.

Backup and Restore

Backup with pg_dump

hljs bash

# Backup a single database
pg_dump -U postgres myapp > myapp_backup.sql

# Compressed format (faster restore)
pg_dump -U postgres -Fc myapp > myapp_backup.dump

# Backup all databases
pg_dumpall -U postgres > all_databases.sql

# Backup from a remote server
pg_dump -h 192.168.1.100 -U postgres myapp > myapp_remote.sql

Restoring from Backup

hljs bash

# Restore from SQL format
psql -U postgres myapp < myapp_backup.sql

# Restore from compressed format
pg_restore -U postgres -d myapp myapp_backup.dump

# Restore all databases
psql -U postgres < all_databases.sql

Automated Backup with Cron

hljs bash

# Edit crontab
crontab -e

# Take a backup every night at 02:00
0 2 * * * pg_dump -U postgres myapp | gzip > /backup/myapp_$(date +\%Y\%m\%d).sql.gz

# Delete backups older than 30 days
0 3 * * * find /backup -name 'myapp_*.sql.gz' -mtime +30 -delete

Service Management

hljs bash

# Start the service
systemctl start postgresql

# Stop the service
systemctl stop postgresql

# Restart the service
systemctl restart postgresql

# Reload configuration (no restart needed)
systemctl reload postgresql

# View logs
journalctl -u postgresql -f
tail -f /var/log/postgresql/postgresql-16-main.log

Conclusion

PostgreSQL is a reliable and high-performance database system. After installation, do not neglect security configuration, perform regular backups, and make sure to properly configure authentication methods for remote access. PostgreSQL runs smoothly on REXE servers and delivers reliable performance under high traffic.

PostgreSQL Installation and Basic Management Guide

Table of Contents