PostgreSQL Installation and Administration

Overview

• What you’ll learn: How to install PostgreSQL on Ubuntu Server, understand its process architecture, configure client authentication with pg_hba.conf, manage roles and databases, tune essential configuration parameters, and perform routine administration tasks.
• Prerequisites: Lesson 40 (Relational Database Fundamentals), Module 1 (Linux Fundamentals)
• Estimated reading time: 25 minutes

Introduction

PostgreSQL is an advanced open-source relational database known for its standards compliance, extensibility, and robust feature set. It supports advanced SQL features including window functions, Common Table Expressions (CTEs), JSONB for semi-structured data, full-text search, and custom data types. PostgreSQL is the database of choice for applications that require complex queries, strict data integrity, and extensibility.

On Ubuntu Server, PostgreSQL is available from both the default Ubuntu repositories and the official PostgreSQL Global Development Group (PGDG) repository, which provides the latest versions. In this lesson you will install PostgreSQL, understand its multi-process architecture, configure authentication, create and manage roles and databases, and tune the server for your workload.

By the end of this lesson, you will have a production-ready PostgreSQL instance and the knowledge to manage it confidently from the command line.

PostgreSQL Architecture

PostgreSQL uses a multi-process architecture rather than a multi-threaded one. The main components are:

• Postmaster: The main daemon process. It listens for incoming connections and forks a new backend process for each client connection.
• Backend processes: Each handles a single client connection, parsing queries, creating execution plans, and returning results.
• Background workers: Processes such as the autovacuum launcher, WAL writer, checkpointer, and stats collector run in the background to maintain the database.

# View PostgreSQL processes
$ ps aux | grep postgres
postgres  1234  ... /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main
postgres  1235  ... postgres: checkpointer
postgres  1236  ... postgres: background writer
postgres  1237  ... postgres: walwriter
postgres  1238  ... postgres: autovacuum launcher
postgres  1239  ... postgres: stats collector

Data Directory Layout

/var/lib/postgresql/16/main/
├── base/              # Database files (one subdirectory per database)
├── global/            # Cluster-wide tables (pg_database, pg_authid)
├── pg_wal/            # Write-Ahead Log segments
├── pg_xact/           # Transaction commit status
├── postgresql.auto.conf  # Settings altered via ALTER SYSTEM
└── postmaster.pid     # PID file for the running instance

Installing PostgreSQL

From the Default Ubuntu Repository

# Install PostgreSQL
$ sudo apt update
$ sudo apt install postgresql postgresql-client -y

# Check the service
$ sudo systemctl status postgresql

# Check the installed version
$ psql --version
psql (PostgreSQL) 16.2 (Ubuntu 16.2-1ubuntu4)

From the PGDG Repository (Latest Version)

# Add the PGDG repository
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Import the repository signing key
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

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

Client Authentication (pg_hba.conf)

PostgreSQL controls client authentication through the pg_hba.conf file (Host-Based Authentication). Each line defines a rule specifying which users can connect from which hosts to which databases, and the authentication method to use.

# /etc/postgresql/16/main/pg_hba.conf
# TYPE   DATABASE   USER       ADDRESS          METHOD

# Local connections (Unix socket)
local    all        postgres                    peer
local    all        all                         peer

# IPv4 local connections
host     all        all        127.0.0.1/32     scram-sha-256

# IPv4 connections from the application subnet
host     myapp_db   appuser    10.0.1.0/24      scram-sha-256

# Reject everything else
host     all        all        0.0.0.0/0        reject

Authentication Methods

• peer: Uses the OS username to authenticate local (Unix socket) connections. The OS user must match the PostgreSQL role name.
• scram-sha-256: Password-based authentication using the SCRAM-SHA-256 mechanism. Recommended for all password-based connections.
• md5: Older password-based method. Use scram-sha-256 instead on new installations.
• cert: SSL client certificate authentication.
• reject: Unconditionally reject the connection.

# After editing pg_hba.conf, reload the configuration
$ sudo systemctl reload postgresql

# Or from within psql
postgres=# SELECT pg_reload_conf();

Role Management

PostgreSQL uses roles instead of separate “users” and “groups.” A role can have login capability (making it a user) and can be a member of other roles (making it act as a group).

# Connect as the postgres superuser
$ sudo -u postgres psql

-- Create a login role (user)
postgres=# CREATE ROLE appuser WITH LOGIN PASSWORD 'Str0ng_P@ss!' VALID UNTIL '2027-12-31';

-- Create a read-only role (group)
postgres=# CREATE ROLE readonly NOLOGIN;
postgres=# GRANT CONNECT ON DATABASE myapp_db TO readonly;
postgres=# GRANT USAGE ON SCHEMA public TO readonly;
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Add a user to the read-only group
postgres=# GRANT readonly TO analyst_user;

-- List all roles
postgres=# du

-- Alter a role
postgres=# ALTER ROLE appuser WITH CREATEDB;

-- Drop a role
postgres=# DROP ROLE appuser;

Database Management

-- Create a new database
postgres=# CREATE DATABASE myapp_db
           OWNER appuser
           ENCODING 'UTF8'
           LC_COLLATE 'en_US.UTF-8'
           LC_CTYPE 'en_US.UTF-8'
           TEMPLATE template0;

-- List databases
postgres=# l

-- Connect to a database
postgres=# c myapp_db

-- Show tables in the current database
myapp_db=# dt

-- Show table structure
myapp_db=# d users

-- Get database size
postgres=# SELECT pg_database.datname,
           pg_size_pretty(pg_database_size(pg_database.datname))
           FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;

-- Drop a database
postgres=# DROP DATABASE myapp_db;

Using Command-Line Tools

# Create a database from the shell
$ sudo -u postgres createdb -O appuser myapp_db

# Create a user from the shell
$ sudo -u postgres createuser --interactive appuser

# Drop a database from the shell
$ sudo -u postgres dropdb myapp_db

PostgreSQL Configuration

The main configuration file is /etc/postgresql/16/main/postgresql.conf. Key parameters to tune include:

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

# Connection settings
listen_addresses = 'localhost'          # '*' to listen on all interfaces
port = 5432
max_connections = 100

# Memory settings
shared_buffers = 512MB                  # ~25% of total RAM
effective_cache_size = 1536MB           # ~75% of total RAM
work_mem = 16MB                         # Per-operation sort/hash memory
maintenance_work_mem = 256MB            # For VACUUM, CREATE INDEX, etc.

# WAL settings
wal_level = replica                     # Needed for replication
max_wal_size = 1GB
min_wal_size = 80MB

# Query planner
random_page_cost = 1.1                  # Lower for SSD storage (default 4.0)
effective_io_concurrency = 200          # Higher for SSD

# Logging
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = 1000       # Log queries taking > 1 second
log_line_prefix = '%m [%p] %u@%d '

# Apply configuration changes
$ sudo systemctl restart postgresql

# Or for settings that support reload
$ sudo systemctl reload postgresql

# Check a specific setting
postgres=# SHOW shared_buffers;
postgres=# SHOW max_connections;

Essential Administration Commands

VACUUM and ANALYZE

PostgreSQL’s MVCC (Multi-Version Concurrency Control) creates dead tuples when rows are updated or deleted. VACUUM reclaims this space, and ANALYZE updates table statistics used by the query planner.

-- Manual vacuum and analyze
postgres=# VACUUM VERBOSE myapp_db;
postgres=# ANALYZE;

-- Check autovacuum activity
postgres=# SELECT relname, last_vacuum, last_autovacuum, last_analyze
           FROM pg_stat_user_tables;

Monitoring Connections

-- Show active connections
postgres=# SELECT pid, usename, datname, client_addr, state, query
           FROM pg_stat_activity
           WHERE state != 'idle';

-- Count connections by database
postgres=# SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;

-- Terminate a specific connection
postgres=# SELECT pg_terminate_backend(12345);

System-Level Commands

# Check PostgreSQL service
$ sudo systemctl status postgresql

# View PostgreSQL logs
$ sudo tail -50 /var/log/postgresql/postgresql-16-main.log

# Check data directory size
$ sudo du -sh /var/lib/postgresql/16/main/

Key Takeaways

• PostgreSQL uses a multi-process architecture with a postmaster, backend processes for each client connection, and background workers for maintenance tasks.
• Install PostgreSQL with apt install postgresql or use the PGDG repository for the latest version.
• Client authentication is controlled by pg_hba.conf — use scram-sha-256 for password-based connections and peer for local Unix socket access.
• PostgreSQL uses roles instead of separate users and groups — a role with LOGIN capability acts as a user.
• Key tuning parameters: shared_buffers (~25% RAM), effective_cache_size (~75% RAM), work_mem, and random_page_cost (lower for SSDs).
• VACUUM reclaims dead tuples from MVCC, and ANALYZE updates statistics for the query planner — autovacuum handles both automatically.

What’s Next

In Lesson 44, you will explore PostgreSQL replication and performance tuning, including streaming replication, logical replication, connection pooling with PgBouncer, and query optimization techniques.

# 安裝 PostgreSQL
$ sudo apt update
$ sudo apt install postgresql postgresql-client -y

# 檢查服務
$ sudo systemctl status postgresql

# 檢查版本
$ psql --version

# /etc/postgresql/16/main/pg_hba.conf
# 類型   資料庫     使用者     位址              方法
local    all        postgres                    peer
local    all        all                         peer
host     all        all        127.0.0.1/32     scram-sha-256
host     myapp_db   appuser    10.0.1.0/24      scram-sha-256

# 以 postgres 超級使用者連線
$ sudo -u postgres psql

-- 建立登入角色（使用者）
postgres=# CREATE ROLE appuser WITH LOGIN PASSWORD 'Str0ng_P@ss!';

-- 建立唯讀角色（群組）
postgres=# CREATE ROLE readonly NOLOGIN;
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- 列出所有角色
postgres=# du

-- 建立新資料庫
postgres=# CREATE DATABASE myapp_db
           OWNER appuser
           ENCODING 'UTF8';

-- 列出資料庫
postgres=# l

-- 連線到資料庫
postgres=# c myapp_db

-- 顯示表
myapp_db=# dt

-- 取得資料庫大小
postgres=# SELECT datname, pg_size_pretty(pg_database_size(datname))
           FROM pg_database ORDER BY pg_database_size(datname) DESC;

listen_addresses = 'localhost'
max_connections = 100
shared_buffers = 512MB              # 約 25% 的總 RAM
effective_cache_size = 1536MB       # 約 75% 的總 RAM
work_mem = 16MB
maintenance_work_mem = 256MB
random_page_cost = 1.1              # SSD 儲存降低此值

-- 手動清理和分析
postgres=# VACUUM VERBOSE;
postgres=# ANALYZE;

-- 檢查自動清理活動
postgres=# SELECT relname, last_vacuum, last_autovacuum
           FROM pg_stat_user_tables;

-- 顯示活動連線
postgres=# SELECT pid, usename, datname, state, query
           FROM pg_stat_activity WHERE state != 'idle';

-- 終止特定連線
postgres=# SELECT pg_terminate_backend(12345);

# PostgreSQL のインストール
$ sudo apt update
$ sudo apt install postgresql postgresql-client -y

# サービスの確認
$ sudo systemctl status postgresql

# バージョンの確認
$ psql --version

# /etc/postgresql/16/main/pg_hba.conf
# タイプ  データベース  ユーザー    アドレス           方法
local    all          postgres                      peer
local    all          all                           peer
host     all          all          127.0.0.1/32     scram-sha-256
host     myapp_db     appuser      10.0.1.0/24      scram-sha-256

# postgres スーパーユーザーとして接続
$ sudo -u postgres psql

-- ログインロール（ユーザー）の作成
postgres=# CREATE ROLE appuser WITH LOGIN PASSWORD 'Str0ng_P@ss!';

-- 読み取り専用ロール（グループ）の作成
postgres=# CREATE ROLE readonly NOLOGIN;
postgres=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- すべてのロールを表示
postgres=# du

-- 新しいデータベースの作成
postgres=# CREATE DATABASE myapp_db
           OWNER appuser
           ENCODING 'UTF8';

-- データベースの一覧
postgres=# l

-- データベースに接続
postgres=# c myapp_db

-- テーブルの表示
myapp_db=# dt

-- データベースサイズの確認
postgres=# SELECT datname, pg_size_pretty(pg_database_size(datname))
           FROM pg_database ORDER BY pg_database_size(datname) DESC;

listen_addresses = 'localhost'
max_connections = 100
shared_buffers = 512MB              # 総 RAM の約 25%
effective_cache_size = 1536MB       # 総 RAM の約 75%
work_mem = 16MB
maintenance_work_mem = 256MB
random_page_cost = 1.1              # SSD ストレージでは低く設定

-- 手動 vacuum と analyze
postgres=# VACUUM VERBOSE;
postgres=# ANALYZE;

-- autovacuum の活動を確認
postgres=# SELECT relname, last_vacuum, last_autovacuum
           FROM pg_stat_user_tables;

-- アクティブな接続を表示
postgres=# SELECT pid, usename, datname, state, query
           FROM pg_stat_activity WHERE state != 'idle';

-- 特定の接続を終了
postgres=# SELECT pg_terminate_backend(12345);