BLOG POSTS

MangoHost Blog / How to Install and Use PostgreSQL on Ubuntu 24

How to Install and Use PostgreSQL on Ubuntu 24

PostgreSQL, also known as Postgres, is a powerful open-source relational database management system (RDBMS) that’s become increasingly popular among developers and system administrators for its reliability, feature set, and standards compliance. Ubuntu 24 brings the latest improvements in system stability and performance, making it an excellent platform for running PostgreSQL in production environments. This guide will walk you through the complete process of installing, configuring, and using PostgreSQL on Ubuntu 24, covering everything from basic installation to advanced configuration, common troubleshooting scenarios, and best practices for optimal performance.

Understanding PostgreSQL and Its Benefits

PostgreSQL stands out from other database systems due to its advanced features and robust architecture. Unlike simpler databases like SQLite or even MySQL in some cases, PostgreSQL offers advanced data types, full-text search capabilities, JSON support, and extensibility through custom functions and extensions.

The database engine uses a multi-process architecture where each client connection spawns a separate backend process. This design provides better isolation and stability compared to multi-threaded approaches, though it does consume more memory per connection.

Feature	PostgreSQL	MySQL	SQLite
ACID Compliance	Full	Partial (InnoDB)	Full
JSON Support	Native JSONB	Basic JSON	JSON1 extension
Full-text Search	Built-in	Basic	FTS5 extension
Custom Data Types	Yes	Limited	No
Concurrent Connections	High	Very High	Limited

Step-by-Step Installation Guide

Ubuntu 24 includes PostgreSQL in its default repositories, but you might want to install the latest version directly from the PostgreSQL APT repository for the most recent features and security updates.

Method 1: Installing from Ubuntu Repositories

First, update your package list and install PostgreSQL:

sudo apt update
sudo apt install postgresql postgresql-contrib

This installs PostgreSQL along with additional utilities and extensions. The postgresql-contrib package includes popular extensions like pg_stat_statements and uuid-ossp.

Method 2: Installing Latest Version from PostgreSQL Repository

To get the absolute latest version, add the official PostgreSQL APT repository:

# Install required packages
sudo apt install wget ca-certificates

# Add PostgreSQL signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Add repository
echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# Update and install
sudo apt update
sudo apt install postgresql-16 postgresql-contrib-16

After installation, PostgreSQL automatically starts and enables itself for boot. You can verify the installation:

sudo systemctl status postgresql
psql --version

Initial Configuration and Security Setup

PostgreSQL creates a system user called postgres during installation. This user has administrative privileges for the database cluster. Your first step should be setting up proper authentication and creating application-specific users.

Setting Up the PostgreSQL Superuser

# Switch to postgres user
sudo -u postgres psql

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

# Create a new database user
CREATE USER myapp WITH PASSWORD 'app_password';

# Create a database for your application
CREATE DATABASE myapp_db OWNER myapp;

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

# Exit psql
\q

Configuring Authentication

PostgreSQL uses the pg_hba.conf file to control client authentication. The default configuration only allows local connections, which is secure but might not suit your needs.

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

Common authentication configurations:

# Local connections
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 local connections
host    all             all             127.0.0.1/32            md5

# Allow connections from specific network (replace with your network)
host    all             all             192.168.1.0/24          md5

For remote connections, you’ll also need to modify postgresql.conf:

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

# Find and modify this line
listen_addresses = 'localhost'  # Change to '*' for all interfaces or specific IP

After making changes, restart PostgreSQL:

sudo systemctl restart postgresql

Essential PostgreSQL Operations and Commands

Understanding basic PostgreSQL operations is crucial for daily database management. Here are the most commonly used commands and operations:

Database Connection and Basic Queries

# Connect to database as specific user
psql -U myapp -d myapp_db -h localhost

# Common psql commands
\l          # List databases
\c dbname   # Connect to database
\dt         # List tables
\du         # List users
\q          # Quit

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

# Insert sample data
INSERT INTO users (username, email) VALUES 
('johndoe', 'john@example.com'),
('janedoe', 'jane@example.com');

Backup and Restore Operations

Regular backups are essential for any production database. PostgreSQL provides several tools for this purpose:

# Full database backup
pg_dump -U postgres -h localhost myapp_db > myapp_backup.sql

# Compressed backup
pg_dump -U postgres -h localhost -Fc myapp_db > myapp_backup.dump

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

# Restore from SQL backup
psql -U postgres -d myapp_db < myapp_backup.sql

# Restore from compressed backup
pg_restore -U postgres -d myapp_db -v myapp_backup.dump

Performance Optimization and Configuration

PostgreSQL's default configuration is quite conservative and optimized for compatibility rather than performance. Here are key settings to adjust based on your system resources:

Memory Configuration

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

# Key memory settings (adjust based on available RAM)
shared_buffers = 256MB          # 25% of RAM is a good starting point
effective_cache_size = 1GB      # 50-75% of total RAM
work_mem = 4MB                  # For sort operations
maintenance_work_mem = 64MB     # For maintenance operations

Connection and Performance Settings

# Connection settings
max_connections = 100           # Adjust based on expected concurrent users
superuser_reserved_connections = 3

# Performance settings
random_page_cost = 1.1         # For SSD storage (default 4.0 for HDD)
effective_io_concurrency = 200 # For SSD storage
checkpoint_completion_target = 0.9
wal_buffers = 16MB

After making configuration changes, restart PostgreSQL and monitor performance using built-in statistics:

# Enable query statistics
shared_preload_libraries = 'pg_stat_statements'

# Monitor active queries
SELECT pid, usename, application_name, state, query 
FROM pg_stat_activity 
WHERE state = 'active';

# Check database statistics
SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del 
FROM pg_stat_user_tables;

Real-World Use Cases and Integration Examples

PostgreSQL excels in various scenarios, from simple web applications to complex analytical workloads. Here are practical examples for common use cases:

Web Application Backend

For a typical web application, you might structure your database like this:

-- User management with roles
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    profile JSONB,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Content with full-text search
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    author_id UUID REFERENCES users(id),
    search_vector tsvector,
    published_at TIMESTAMP
);

-- Create index for full-text search
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- Trigger to update search vector
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_search_vector 
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

Analytics and Reporting

PostgreSQL's window functions and JSON capabilities make it excellent for analytical queries:

-- Sales analytics with window functions
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', order_date) as month,
        SUM(total_amount) as monthly_total,
        LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as prev_month
    FROM orders 
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    month,
    monthly_total,
    ROUND(((monthly_total - prev_month) / prev_month * 100), 2) as growth_percentage
FROM monthly_sales;

-- JSON aggregation for reporting
SELECT 
    category,
    JSON_AGG(
        JSON_BUILD_OBJECT(
            'product_name', name,
            'sales', total_sales,
            'rank', rank
        )
    ) as top_products
FROM (
    SELECT 
        p.category,
        p.name,
        SUM(oi.quantity * oi.price) as total_sales,
        ROW_NUMBER() OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity * oi.price) DESC) as rank
    FROM products p
    JOIN order_items oi ON p.id = oi.product_id
    GROUP BY p.category, p.name
) ranked_products
WHERE rank <= 5
GROUP BY category;

Common Issues and Troubleshooting

Even with proper setup, you'll encounter issues. Here are the most common problems and their solutions:

Connection Issues

Connection refused: Check if PostgreSQL is running and listening on the correct port
Authentication failed: Verify pg_hba.conf settings and user passwords
Too many connections: Adjust max_connections or implement connection pooling

# Check PostgreSQL status and connections
sudo systemctl status postgresql
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

# Check listening ports
sudo netstat -tlnp | grep postgres

# Monitor connection usage
SELECT state, count(*) 
FROM pg_stat_activity 
GROUP BY state;

Performance Problems

Slow queries are often the result of missing indexes or suboptimal query plans:

# Enable slow query logging
log_min_duration_statement = 1000  # Log queries taking > 1 second

# Analyze query performance
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'user@example.com';

# Check for missing indexes
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'public' AND n_distinct > 100;

Disk Space Issues

PostgreSQL databases can grow quickly, especially with frequent updates that leave behind dead tuples:

# Check database sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

# Check table sizes and bloat
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
       n_dead_tup
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

# Run VACUUM to reclaim space
VACUUM ANALYZE;

# For heavily updated tables, consider VACUUM FULL (requires downtime)
VACUUM FULL table_name;

Best Practices and Security Considerations

Running PostgreSQL in production requires attention to security and maintenance practices:

Security Hardening

Use strong passwords: Implement password policies and consider certificate-based authentication
Limit network access: Use firewalls and restrict pg_hba.conf entries
Regular updates: Keep PostgreSQL updated with security patches
SSL encryption: Enable SSL for remote connections

# Enable SSL in postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

# Force SSL connections in pg_hba.conf
hostssl all all 0.0.0.0/0 md5

# Create SSL certificates
sudo openssl req -new -x509 -days 365 -nodes -text -out server.crt -keyout server.key -subj "/CN=your-server-name"
sudo chown postgres:postgres server.crt server.key
sudo chmod 600 server.key

Monitoring and Maintenance

Set up automated monitoring and maintenance tasks:

#!/bin/bash
# Daily maintenance script (save as /usr/local/bin/pg_maintenance.sh)

# Update table statistics
psql -U postgres -d myapp_db -c "ANALYZE;"

# Clean up old WAL files
psql -U postgres -c "SELECT pg_switch_wal();"

# Check for long-running queries
psql -U postgres -c "SELECT pid, usename, state, query_start, query FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '1 hour';"

# Backup database
pg_dump -U postgres myapp_db | gzip > /backups/myapp_db_$(date +%Y%m%d_%H%M%S).sql.gz

# Keep only last 7 days of backups
find /backups -name "myapp_db_*.sql.gz" -mtime +7 -delete

Add this to crontab for automated execution:

# Edit crontab
sudo crontab -e

# Add daily maintenance at 2 AM
0 2 * * * /usr/local/bin/pg_maintenance.sh >> /var/log/pg_maintenance.log 2>&1

Advanced Features and Extensions

PostgreSQL's extensibility is one of its strongest features. Here are some useful extensions for common scenarios:

# Install and enable useful extensions
sudo -u postgres psql -d myapp_db

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";        -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements"; -- Query statistics
CREATE EXTENSION IF NOT EXISTS "pg_trgm";          -- Trigram matching for fuzzy search
CREATE EXTENSION IF NOT EXISTS "hstore";           -- Key-value pairs
CREATE EXTENSION IF NOT EXISTS "postgis";          -- Geospatial data (if installed)

# Example using trigram search for fuzzy matching
SELECT username, similarity(username, 'johndoe') as sim
FROM users
WHERE username % 'johndoe'
ORDER BY sim DESC;

For high-performance applications running on dedicated infrastructure like dedicated servers, consider setting up connection pooling with PgBouncer and implementing read replicas for load distribution.

PostgreSQL on Ubuntu 24 provides a robust foundation for applications ranging from simple websites to complex analytical systems. The combination of Ubuntu's stability and PostgreSQL's feature set makes this an excellent choice for production deployments, whether you're running on VPS services or larger dedicated infrastructure.

For detailed configuration options and advanced features, consult the official PostgreSQL documentation, which provides comprehensive coverage of all database features and configuration parameters.

This article incorporates information and material from various online sources. We acknowledge and appreciate the work of all original authors, publishers, and websites. While every effort has been made to appropriately credit the source material, any unintentional oversight or omission does not constitute a copyright infringement. All trademarks, logos, and images mentioned are the property of their respective owners. If you believe that any content used in this article infringes upon your copyright, please contact us immediately for review and prompt action.

This article is intended for informational and educational purposes only and does not infringe on the rights of the copyright owners. If any copyrighted material has been used without proper credit or in violation of copyright laws, it is unintentional and we will rectify it promptly upon notification. Please note that the republishing, redistribution, or reproduction of part or all of the contents in any form is prohibited without express written permission from the author and website owner. For permissions or further inquiries, please contact us.