Install PostgreSQL on Ubuntu server

PostgreSQL is a powerful, open-source object-relational database management system (ORDBMS) that uses and extends the SQL language. It's known for its reliability, data integrity, and robust feature set including ACID compliance, complex queries, foreign keys, triggers, updatable views, transactional integrity, and multiversion concurrency control (MVCC).

System Requirements

Before installation, ensure your Ubuntu server meets these minimum requirements:

Disk Space: At least 100 MB for basic installation, plus space for your databases
RAM: Minimum 256 MB, recommended 1 GB or more for production workloads
CPU: Any modern processor; performance scales with cores for concurrent queries
Supported Ubuntu Versions: 18.04 LTS, 20.04 LTS, 22.04 LTS, 24.04 LTS

bash

1sudo apt update
2sudo apt install postgresql postgresql-client

This command installs:

postgresql: The core database server package
postgresql-client: Command-line client tools for interacting with PostgreSQL
postgresql-contrib: Additional utilities and extensions (automatically included as dependency)

The installation process automatically:

Creates a postgres system user and group
Initializes a database cluster in /var/lib/postgresql/<version>/main/
Starts the PostgreSQL service
Enables the service to start on boot

Verify Installation and Service Status

Check if PostgreSQL service is running:

bash

1sudo systemctl status postgresql

View PostgreSQL service logs for troubleshooting:

bash

1sudo journalctl -u postgresql -n 50 --no-pager

Check PostgreSQL version

bash

1psql --version

You can also check the server version while connected:

bash

1sudo -u postgres psql -c "SELECT version();"

Configure PostgreSQL for Remote Access

By default, PostgreSQL only accepts connections from localhost for security reasons. To enable remote access, you need to modify two configuration files.

Understanding postgresql.conf

The postgresql.conf file contains server-wide configuration parameters. Key sections include:

Connection Settings: Network interfaces, port, max connections
Resource Usage: Memory, disk, kernel resources
Write-Ahead Log: WAL settings for durability and replication
Query Tuning: Planner and execution parameters

bash

1sudo nano /etc/postgresql/14/main/postgresql.conf #check version folder
2listen_addresses = '*'

Important Configuration Parameters to Consider:

bash

1# Connection Settings
2listen_addresses = '*'          # Listen on all network interfaces
3port = 5432                      # Default PostgreSQL port
4max_connections = 100            # Maximum concurrent connections
5
6# Memory Settings (adjust based on available RAM)
7shared_buffers = 256MB          # RAM used for caching data
8effective_cache_size = 1GB      # Estimate of OS + PostgreSQL cache
9work_mem = 4MB                  # Memory per query operation
10maintenance_work_mem = 64MB     # Memory for maintenance operations
11
12# Write-Ahead Log
13wal_level = replica             # Minimal, replica, or logical
14max_wal_size = 1GB             # Maximum WAL size before checkpoint

After editing, restart PostgreSQL to apply changes.

Understanding pg_hba.conf (Host-Based Authentication)

The pg_hba.conf file controls client authentication. Each line specifies:

Connection Type: local (Unix socket), host (TCP/IP), hostssl (SSL/TLS only)
Database: Which database(s) the rule applies to
User: Which user(s) can connect
Address: IP address or CIDR range (for TCP/IP connections)
Authentication Method: How to authenticate (trust, md5, scram-sha-256, peer, etc.)

Authentication Methods Explained:

peer: Uses OS username (local connections only)
md5: MD5-hashed password (legacy, less secure)
scram-sha-256: Modern password authentication (recommended)
trust: No authentication (dangerous, use only for testing)
reject: Always reject connection

Create template

bash

1sudo -u postgres psql template1
2ALTER USER postgres with encrypted password 'password';
3
4sudo nano /etc/postgresql/14/main/pg_hba.conf
5#add line with LAN subnet
6hostssl template1       postgres        0.0.0.0/0        scram-sha-256
7#or
8#allow all to all
9host    all             all             0.0.0.0/0               md5

Security Best Practices for pg_hba.conf:

Instead of allowing all IPs (0.0.0.0/0), restrict access to specific networks:

bash

1# Allow from specific IP
2host    all             all             192.168.1.100/32        scram-sha-256
3
4# Allow from subnet (e.g., local network)
5host    all             all             192.168.1.0/24          scram-sha-256
6
7# Require SSL for remote connections
8hostssl all             all             0.0.0.0/0               scram-sha-256
9
10# Local connections use peer authentication
11local   all             postgres                                peer

The order of rules matters - PostgreSQL uses the first matching rule. Place more specific rules before general ones.

bash

1sudo systemctl restart postgresql

Configure Firewall

If using UFW (Uncomplicated Firewall), allow PostgreSQL traffic:

bash

1sudo ufw allow 5432/tcp
2sudo ufw status

For more specific access control:

bash

1# Allow from specific IP
2sudo ufw allow from 192.168.1.100 to any port 5432
3
4# Allow from subnet
5sudo ufw allow from 192.168.1.0/24 to any port 5432

Check connection to db

bash

1psql --host 10.0.0.144 --username postgres --password --dbname template1

Alternative connection string formats:

bash

1# Using connection URI
2psql postgresql://postgres:[email protected]:5432/template1
3
4# Using environment variables
5export PGHOST=10.0.0.144
6export PGUSER=postgres
7export PGDATABASE=template1
8psql
9
10# Test connection without interactive shell
11psql -h 10.0.0.144 -U postgres -d template1 -c "SELECT current_database(), current_user;"

Connection Troubleshooting

If connection fails, check these common issues:

1. Service not running:

bash

1sudo systemctl status postgresql
2sudo systemctl start postgresql

2. Port not listening:

bash

1sudo ss -tunlp | grep 5432
2# Should show: LISTEN 0.0.0.0:5432

3. Firewall blocking:

bash

1sudo ufw status
2sudo iptables -L -n | grep 5432

4. Check PostgreSQL logs:

bash

1sudo tail -f /var/log/postgresql/postgresql-14-main.log

Create new database with user

bash

1sudo -u postgres psql
2CREATE DATABASE db;
3CREATE USER usr WITH ENCRYPTED PASSWORD '123';
4GRANT ALL PRIVILEGES ON DATABASE db TO usr;

Database and User Management

Understanding PostgreSQL Roles and Privileges

PostgreSQL uses "roles" for authentication. A role can be a user or a group. Key attributes:

LOGIN: Role can connect to database
SUPERUSER: Bypass all permission checks
CREATEDB: Can create databases
CREATEROLE: Can create other roles
REPLICATION: Can initiate streaming replication

Advanced User Creation Examples

Create user with specific privileges:

bash

1# User with database creation privilege
2CREATE USER developer WITH ENCRYPTED PASSWORD 'dev_pass' CREATEDB;
3
4# User with limited connection
5CREATE USER readonly WITH ENCRYPTED PASSWORD 'read_pass' CONNECTION LIMIT 5;
6
7# User with expiration date
8CREATE USER temp_user WITH ENCRYPTED PASSWORD 'temp_pass' VALID UNTIL '2026-12-31';
9
10# Create role (group) and add users to it
11CREATE ROLE app_users;
12GRANT CONNECT ON DATABASE db TO app_users;
13GRANT app_users TO usr;

Grant Specific Schema Privileges

For better security, grant privileges at schema and table levels:

bash

1# Connect to the database
2\c db
3
4# Grant schema usage
5GRANT USAGE ON SCHEMA public TO usr;
6
7# Grant SELECT on all existing tables
8GRANT SELECT ON ALL TABLES IN SCHEMA public TO usr;
9
10# Grant SELECT on all future tables
11ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO usr;
12
13# Grant specific privileges on specific table
14GRANT SELECT, INSERT, UPDATE ON specific_table TO usr;
15
16# Grant all privileges on schema
17GRANT ALL PRIVILEGES ON SCHEMA public TO usr;
18GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO usr;
19GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO usr;

Database Management Commands

Useful commands for database administration:

bash

1# List all databases
2\l
3# or
4SELECT datname FROM pg_database;
5
6# List all users/roles
7\du
8# or
9SELECT rolname, rolsuper, rolcreatedb, rolcanlogin FROM pg_roles;
10
11# Check database size
12SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) 
13FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;
14
15# Check table sizes in current database
16SELECT schemaname, tablename, 
17       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
18FROM pg_tables 
19ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
20
21# Show active connections
22SELECT pid, usename, application_name, client_addr, state, query_start 
23FROM pg_stat_activity 
24WHERE state = 'active';
25
26# Kill a connection
27SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid = 12345;

Backup and Restore

Backup Strategies

1. Logical Backup with pg_dump:

bash

1# Backup single database
2pg_dump -h localhost -U postgres -d db -F c -f /backup/db_backup.dump
3
4# Backup with compression
5pg_dump -h localhost -U postgres -d db -F c -Z 9 -f /backup/db_compressed.dump
6
7# Backup as SQL script
8pg_dump -h localhost -U postgres -d db -f /backup/db_backup.sql
9
10# Backup specific tables
11pg_dump -h localhost -U postgres -d db -t table1 -t table2 -f /backup/tables.sql
12
13# Backup all databases
14pg_dumpall -h localhost -U postgres -f /backup/all_databases.sql
15
16# Backup only schema (no data)
17pg_dump -h localhost -U postgres -d db --schema-only -f /backup/schema.sql
18
19# Backup only data (no schema)
20pg_dump -h localhost -U postgres -d db --data-only -f /backup/data.sql

2. Physical Backup with pg_basebackup:

bash

1# Create base backup for PITR (Point-in-Time Recovery)
2pg_basebackup -h localhost -U postgres -D /backup/base -F tar -z -P

Restore Operations

bash

1# Restore from custom format dump
2pg_restore -h localhost -U postgres -d db -c /backup/db_backup.dump
3
4# Restore from SQL script
5psql -h localhost -U postgres -d db -f /backup/db_backup.sql
6
7# Restore all databases
8psql -h localhost -U postgres -f /backup/all_databases.sql
9
10# Restore with verbose output and error handling
11pg_restore -h localhost -U postgres -d db -v --single-transaction /backup/db_backup.dump

Automated Backup Script

Create a daily backup script:

bash

1# Create backup script
2sudo nano /usr/local/bin/postgres_backup.sh

Add the following content:

bash

1#!/bin/bash
2BACKUP_DIR="/backup/postgresql"
3TIMESTAMP=$(date +%Y%m%d_%H%M%S)
4DAYS_TO_KEEP=7
5
6mkdir -p $BACKUP_DIR
7
8# Backup all databases
9pg_dumpall -U postgres | gzip > $BACKUP_DIR/all_databases_$TIMESTAMP.sql.gz
10
11# Remove old backups
12find $BACKUP_DIR -name "all_databases_*.sql.gz" -mtime +$DAYS_TO_KEEP -delete
13
14# Log completion
15echo "$(date): Backup completed successfully" >> /var/log/postgres_backup.log

Make it executable and schedule:

bash

1sudo chmod +x /usr/local/bin/postgres_backup.sh
2
3# Add to crontab (runs daily at 2 AM)
4sudo crontab -e
5# Add line:
60 2 * * * /usr/local/bin/postgres_backup.sh

Performance Tuning

Memory Configuration

Adjust based on available system RAM:

bash

1# For system with 4GB RAM
2shared_buffers = 1GB                    # 25% of RAM
3effective_cache_size = 3GB              # 75% of RAM
4maintenance_work_mem = 256MB            # For VACUUM, CREATE INDEX
5work_mem = 10MB                         # Per operation
6
7# For system with 16GB RAM
8shared_buffers = 4GB
9effective_cache_size = 12GB
10maintenance_work_mem = 1GB
11work_mem = 20MB

Connection Pool Configuration

bash

1max_connections = 100                    # Maximum concurrent connections
2superuser_reserved_connections = 3       # Reserved for superuser
3
4# Consider using connection pooler (PgBouncer) for high-traffic applications

Query Performance Monitoring

bash

1# Enable slow query logging
2log_min_duration_statement = 1000        # Log queries taking > 1 second
3
4# Show query execution time
5\timing on
6
7# Analyze query performance
8EXPLAIN ANALYZE SELECT * FROM large_table WHERE id = 12345;
9
10# Find slow queries
11SELECT query, calls, total_time, mean_time, max_time
12FROM pg_stat_statements
13ORDER BY mean_time DESC
14LIMIT 10;

Vacuum and Analyze

Regular maintenance is crucial for performance:

bash

1# Manual vacuum
2VACUUM VERBOSE tablename;
3
4# Aggressive vacuum to reclaim space
5VACUUM FULL tablename;
6
7# Analyze table statistics for query planner
8ANALYZE tablename;
9
10# Vacuum and analyze together
11VACUUM ANALYZE tablename;
12
13# Enable autovacuum (should be enabled by default)
14autovacuum = on
15autovacuum_max_workers = 3
16autovacuum_naptime = 1min

Security Hardening

SSL/TLS Configuration

Enable encrypted connections:

bash

1# Generate self-signed certificate (for testing)
2sudo openssl req -new -x509 -days 365 -nodes -text -out /etc/postgresql/14/main/server.crt -keyout /etc/postgresql/14/main/server.key -subj "/CN=postgresql"
3
4sudo chown postgres:postgres /etc/postgresql/14/main/server.{crt,key}
5sudo chmod 600 /etc/postgresql/14/main/server.key
6
7# Enable SSL in postgresql.conf
8ssl = on
9ssl_cert_file = '/etc/postgresql/14/main/server.crt'
10ssl_key_file = '/etc/postgresql/14/main/server.key'
11
12# Require SSL in pg_hba.conf
13hostssl all all 0.0.0.0/0 scram-sha-256

Password Policies

bash

1# Set password encryption
2password_encryption = scram-sha-256
3
4# Enforce strong passwords (install pgcrypto extension)
5CREATE EXTENSION IF NOT EXISTS pgcrypto;
6
7# Check password strength before setting
8SELECT password_hash = crypt('newpassword', gen_salt('bf'));

Audit Logging

bash

1# Enable comprehensive logging
2log_connections = on
3log_disconnections = on
4log_duration = on
5log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
6log_statement = 'all'                    # Log all statements (verbose)
7# or
8log_statement = 'ddl'                    # Log only DDL statements

Extensions and Additional Features

Commonly Used Extensions

bash

1# Connect to database
2sudo -u postgres psql -d db
3
4# Install popular extensions
5CREATE EXTENSION IF NOT EXISTS "uuid-ossp";      # UUID generation
6CREATE EXTENSION IF NOT EXISTS "pg_trgm";        # Trigram text search
7CREATE EXTENSION IF NOT EXISTS "hstore";         # Key-value store
8CREATE EXTENSION IF NOT EXISTS "pgcrypto";       # Cryptographic functions
9CREATE EXTENSION IF NOT EXISTS "pg_stat_statements"; # Query statistics
10
11# List installed extensions
12\dx
13
14# Get extension details
15\dx+ pg_trgm

Full-Text Search Example

bash

1# Create table with full-text search
2CREATE TABLE articles (
3    id SERIAL PRIMARY KEY,
4    title TEXT,
5    body TEXT,
6    search_vector tsvector
7);
8
9# Create index for fast searching
10CREATE INDEX idx_fts ON articles USING gin(search_vector);
11
12# Update search vector automatically
13CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE ON articles
14FOR EACH ROW EXECUTE FUNCTION
15tsvector_update_trigger(search_vector, 'pg_catalog.english', title, body);
16
17# Search example
18SELECT title FROM articles WHERE search_vector @@ to_tsquery('postgresql & database');

Monitoring and Maintenance

System Catalog Queries

bash

1# Check database activity
2SELECT * FROM pg_stat_activity;
3
4# Check table statistics
5SELECT * FROM pg_stat_user_tables;
6
7# Check index usage
8SELECT schemaname, tablename, indexname, idx_scan
9FROM pg_stat_user_indexes
10ORDER BY idx_scan ASC;
11
12# Find unused indexes
13SELECT schemaname, tablename, indexname
14FROM pg_stat_user_indexes
15WHERE idx_scan = 0 AND indexname NOT LIKE 'pg_toast%';
16
17# Check cache hit ratio (should be > 90%)
18SELECT 
19    sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS cache_hit_ratio
20FROM pg_statio_user_tables;

Health Check Script

bash

1# Create health check script
2cat > /usr/local/bin/pg_health_check.sh << 'EOF'
3#!/bin/bash
4echo "=== PostgreSQL Health Check ==="
5echo "Service Status:"
6systemctl status postgresql --no-pager | grep Active
7
8echo -e "\nDisk Usage:"
9df -h /var/lib/postgresql
10
11echo -e "\nDatabase Sizes:"
12sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;"
13
14echo -e "\nActive Connections:"
15sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity WHERE state = 'active';"
16
17echo -e "\nCache Hit Ratio:"
18sudo -u postgres psql -c "SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0) AS cache_hit_ratio FROM pg_statio_user_tables;"
19EOF
20
21chmod +x /usr/local/bin/pg_health_check.sh

Common Operations Reference

Quick Command Reference

bash

1# Start/Stop/Restart service
2sudo systemctl start postgresql
3sudo systemctl stop postgresql
4sudo systemctl restart postgresql
5sudo systemctl reload postgresql      # Reload config without restart
6
7# Check PostgreSQL process
8ps aux | grep postgres
9
10# Connect as postgres user
11sudo -i -u postgres
12psql
13
14# Execute SQL from command line
15psql -U postgres -d db -c "SELECT version();"
16
17# Import CSV data
18\copy tablename FROM '/path/to/file.csv' DELIMITER ',' CSV HEADER;
19
20# Export to CSV
21\copy (SELECT * FROM tablename) TO '/path/to/output.csv' WITH CSV HEADER;
22
23# Change database owner
24ALTER DATABASE db OWNER TO new_owner;
25
26# Rename database
27ALTER DATABASE old_name RENAME TO new_name;
28
29# Drop database (with active connections)
30SELECT pg_terminate_backend(pg_stat_activity.pid)
31FROM pg_stat_activity
32WHERE pg_stat_activity.datname = 'db' AND pid <> pg_backend_pid();
33DROP DATABASE db;

Resources and Further Reading

Official Documentation: https://www.postgresql.org/docs/
PostgreSQL Wiki: https://wiki.postgresql.org/
Performance Tuning: PGTune (https://pgtune.leopard.in.ua/)
Monitoring Tools: pgAdmin, pgBadger, pg_stat_statements
Connection Pooling: PgBouncer, pgpool-II