BLOG POSTS

MangoHost Blog / How to Install PostgreSQL on Ubuntu 24 Quickstart

How to Install PostgreSQL on Ubuntu 24 Quickstart

PostgreSQL has earned its reputation as one of the most robust and feature-rich open-source relational database management systems available today, and getting it running on Ubuntu 24 is thankfully straightforward. Whether you’re setting up a development environment, migrating from another database system, or deploying production infrastructure, this guide will walk you through the complete installation process, from the basic apt package installation to advanced configuration tweaks. You’ll learn multiple installation methods, essential security configurations, and troubleshooting techniques that’ll save you headaches down the road.

How PostgreSQL Works on Ubuntu 24

PostgreSQL operates as a client-server database system where the main server process (postgres) manages database files, handles client connections, and executes queries. Ubuntu 24 includes PostgreSQL packages in its default repositories, but you can also install newer versions directly from the PostgreSQL APT repository for bleeding-edge features.

The typical PostgreSQL installation on Ubuntu creates a dedicated ‘postgres’ system user, initializes a data directory (usually /var/lib/postgresql/), and sets up systemd services for automatic startup. The server listens on port 5432 by default and uses peer authentication for local connections, meaning your Ubuntu username needs to match a PostgreSQL role name.

Installation Methods Comparison

Installation Method	PostgreSQL Version	Maintenance	Best For
Ubuntu Default Repository	16.x (as of Ubuntu 24)	Automatic updates	Production stability
PostgreSQL Official APT	Latest (17.x+)	Manual version management	New features, development
Docker Container	Any version	Container orchestration	Microservices, isolation
Source Compilation	Custom build	Full manual control	Custom optimizations

Step-by-Step Installation Guide

Method 1: Default Ubuntu Repository (Recommended for Most Users)

This method installs PostgreSQL from Ubuntu’s default repositories, giving you a stable version that receives security updates automatically.

# Update package lists
sudo apt update

# Install PostgreSQL and additional contrib package
sudo apt install postgresql postgresql-contrib

# Check installation and version
sudo -u postgres psql -c "SELECT version();"

Method 2: PostgreSQL Official APT Repository

Use this method when you need the latest PostgreSQL features or a specific version not available in Ubuntu’s default repos.

# Install required packages for repository management
sudo apt install curl ca-certificates

# Add PostgreSQL official APT repository
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc

# Add repository to sources
echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

# Update package lists
sudo apt update

# Install specific PostgreSQL version (e.g., 17)
sudo apt install postgresql-17 postgresql-contrib-17

Initial Configuration and Security Setup

After installation, PostgreSQL needs some basic configuration to be usable and secure. The default installation creates a ‘postgres’ superuser account, but you’ll want to set up proper authentication and create application-specific users.

# Switch to postgres user and access PostgreSQL shell
sudo -u postgres psql

# Set password for postgres user (run inside psql shell)
\password postgres

# Create a new database user for your applications
CREATE USER myapp WITH PASSWORD 'your_secure_password';

# Create a database owned by your new user
CREATE DATABASE myapp_db OWNER myapp;

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

# Exit PostgreSQL shell
\q

Essential Configuration Files

PostgreSQL’s behavior is controlled by several configuration files located in /etc/postgresql/[version]/main/. Here are the key files you’ll need to modify:

# Find your PostgreSQL configuration directory
sudo -u postgres psql -c "SHOW config_file;"

# Main configuration file
sudo nano /etc/postgresql/16/main/postgresql.conf

# Client authentication configuration
sudo nano /etc/postgresql/16/main/pg_hba.conf

Important postgresql.conf Settings

# Listen on all addresses (be careful in production)
listen_addresses = '*'

# Increase shared memory for better performance
shared_buffers = 256MB

# Adjust based on your RAM (25% of total RAM is a good starting point)
effective_cache_size = 1GB

# Enable logging for debugging
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

pg_hba.conf Authentication Setup

# Allow local connections with password authentication
local   all             all                                     md5

# Allow network connections (adjust IP range as needed)
host    all             all             192.168.1.0/24          md5

# IPv4 local connections
host    all             all             127.0.0.1/32            md5

Service Management and Auto-Start

PostgreSQL uses systemd for service management on Ubuntu 24. Here are the essential commands for managing the PostgreSQL service:

# Check PostgreSQL service status
sudo systemctl status postgresql

# Start PostgreSQL service
sudo systemctl start postgresql

# Stop PostgreSQL service
sudo systemctl stop postgresql

# Restart PostgreSQL service
sudo systemctl restart postgresql

# Enable auto-start on boot
sudo systemctl enable postgresql

# Reload configuration without restart
sudo systemctl reload postgresql

Real-World Use Cases and Examples

Web Application Backend

PostgreSQL excels as a backend for web applications, especially when you need complex queries, JSON support, or strict data consistency. Here’s a typical setup for a Python Django application:

# Create dedicated user and database for Django app
sudo -u postgres createuser --interactive djangoapp
sudo -u postgres createdb -O djangoapp django_production

# Install Python PostgreSQL adapter
pip install psycopg2-binary

# Django settings.py database configuration
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'django_production',
        'USER': 'djangoapp',
        'PASSWORD': 'your_password',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

Analytics and Data Warehousing

PostgreSQL’s advanced features like window functions, CTEs, and JSON operations make it suitable for analytical workloads:

# Create a database optimized for analytics
sudo -u postgres createdb analytics_db

# Connect and create sample analytics table
sudo -u postgres psql analytics_db

CREATE TABLE user_events (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    event_type VARCHAR(50),
    properties JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

# Create indexes for better query performance
CREATE INDEX idx_user_events_user_id ON user_events(user_id);
CREATE INDEX idx_user_events_created_at ON user_events(created_at);
CREATE INDEX idx_user_events_properties ON user_events USING GIN(properties);

Performance Tuning and Best Practices

Memory Configuration

PostgreSQL performance heavily depends on proper memory configuration. Here’s a starting configuration for a server with 4GB RAM:

# postgresql.conf memory settings for 4GB RAM server
shared_buffers = 1GB                    # 25% of RAM
effective_cache_size = 3GB              # 75% of RAM
work_mem = 4MB                          # Per operation memory
maintenance_work_mem = 256MB            # For maintenance operations

Connection and Performance Settings

# Connection settings
max_connections = 100                   # Adjust based on your needs
max_prepared_transactions = 100

# Query performance
random_page_cost = 1.1                  # Lower for SSDs
effective_io_concurrency = 200          # Higher for SSDs

Common Issues and Troubleshooting

Authentication Problems

The most common issue newcomers face is authentication errors. PostgreSQL uses different authentication methods that can be confusing:

# Error: "psql: FATAL: Peer authentication failed for user"
# Solution: Use sudo to switch to postgres user first
sudo -u postgres psql

# Or modify pg_hba.conf to use md5 authentication instead of peer
# Change this line in pg_hba.conf:
# local   all             all                                     peer
# To:
# local   all             all                                     md5

Connection Refused Errors

# Check if PostgreSQL is running
sudo systemctl status postgresql

# Check listening ports
sudo netstat -tlnp | grep 5432

# Check PostgreSQL logs for errors
sudo tail -f /var/log/postgresql/postgresql-16-main.log

Permission Issues

# Fix data directory permissions if needed
sudo chown -R postgres:postgres /var/lib/postgresql/
sudo chmod 700 /var/lib/postgresql/16/main/

Comparing PostgreSQL with Alternatives

Database	ACID Compliance	JSON Support	Full-text Search	Licensing	Best For
PostgreSQL	Full	Native JSONB	Built-in	Open Source	Complex applications, analytics
MySQL	Yes (InnoDB)	Basic JSON	Limited	Dual (GPL/Commercial)	Web applications, read-heavy
SQLite	Limited	JSON1 extension	FTS extension	Public Domain	Embedded, single-user apps
MongoDB	Limited	Native (BSON)	Text indexes	SSPL	Document-heavy applications

Advanced Configuration for Production

When deploying PostgreSQL in production, especially on a VPS or dedicated server, additional security and performance configurations are essential:

SSL/TLS Configuration

# Generate SSL certificates
sudo openssl req -new -x509 -days 365 -nodes -text -out /etc/postgresql/16/main/server.crt -keyout /etc/postgresql/16/main/server.key -subj "/CN=your-server-name"

# Set proper permissions
sudo chown postgres:postgres /etc/postgresql/16/main/server.crt /etc/postgresql/16/main/server.key
sudo chmod 600 /etc/postgresql/16/main/server.key

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

Backup Configuration

# Create automated backup script
sudo nano /usr/local/bin/pg_backup.sh

#!/bin/bash
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR

# Backup all databases
sudo -u postgres pg_dumpall > $BACKUP_DIR/all_databases_$DATE.sql

# Backup specific database with compression
sudo -u postgres pg_dump -Fc myapp_db > $BACKUP_DIR/myapp_db_$DATE.backup

# Clean up old backups (keep 7 days)
find $BACKUP_DIR -name "*.sql" -mtime +7 -delete
find $BACKUP_DIR -name "*.backup" -mtime +7 -delete

Monitoring and Maintenance

Regular maintenance ensures optimal PostgreSQL performance. Here are essential maintenance tasks:

# Check database statistics
sudo -u postgres psql -c "SELECT datname, numbackends, xact_commit, xact_rollback FROM pg_stat_database;"

# Analyze table statistics for query optimization
sudo -u postgres psql -d myapp_db -c "ANALYZE;"

# Vacuum to reclaim storage space
sudo -u postgres psql -d myapp_db -c "VACUUM;"

# Check for long-running queries
sudo -u postgres psql -c "SELECT query, state, query_start FROM pg_stat_activity WHERE state = 'active';"

Integration with Development Tools

PostgreSQL integrates well with various development tools and frameworks. Here are some popular combinations:

Docker Integration

# docker-compose.yml for development environment
version: '3.8'
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp_dev
      POSTGRES_USER: developer
      POSTGRES_PASSWORD: devpassword
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

volumes:
  postgres_data:

Connection Pooling with PgBouncer

# Install PgBouncer for connection pooling
sudo apt install pgbouncer

# Basic PgBouncer configuration
sudo nano /etc/pgbouncer/pgbouncer.ini

[databases]
myapp_db = host=localhost port=5432 dbname=myapp_db

[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 100
default_pool_size = 25

PostgreSQL on Ubuntu 24 provides a solid foundation for modern applications, from simple web backends to complex analytical systems. The combination of Ubuntu’s stability and PostgreSQL’s advanced features makes it an excellent choice for both development and production environments. Remember to regularly update your system, monitor performance metrics, and maintain proper backups to ensure your database remains reliable and performant.

For additional information and advanced configuration options, check the official PostgreSQL documentation and the Ubuntu Server Guide.

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.