Tutorial

PostgreSQL Cluster Management at CosmoTech Inc.

Happy Afopezi
by  Happy Afopezi · on
Linux
Master PostgreSQL cluster initialization, configuration, and maintenance through hands-on database administration scenarios. Install PostgreSQL 16 and set up four clusters for development, UAT, production, and backup environments.
#postgresql#database#cluster#dba

Welcome to CosmoTech Inc.! You've just joined as a Database Administrator. The company is expanding rapidly and needs you to install PostgreSQL 16 and set up multiple database environments.

Your mission:

  1. Install PostgreSQL 16 from the official repository
  2. Set up four PostgreSQL clusters for different environments
ClusterPurposePortData Directory
cosmodevDevelopment5445/pgdata/16/cosmodev
cosmouatUAT Testing5446/pgdata/16/cosmouat
cosmoprodProduction5447/pgdata/16/cosmoprod
cosmobackupBackup/Archive5448/pgdata/16/cosmobackup
Note

📖 THEORY SECTION - DO NOT PRACTICE YET

The next section explains important concepts about PostgreSQL repositories, pg_ctl, and initdb.

Please READ and UNDERSTAND these concepts first.

The hands-on practice section starts later with a clear "START PRACTICING HERE" marker.

Understanding the Basics

Before we begin the installation, let's understand the key concepts and tools we'll be using.

PostgreSQL Repository (PGDG)

What is a Repository? A repository is a storage location from which your system retrieves and installs software packages. Think of it as an app store for Linux.

Why do we need the PostgreSQL Repository?

  • Rocky Linux comes with built-in repositories, but they contain older versions of PostgreSQL
  • The official PostgreSQL Global Development Group (PGDG) repository provides the latest versions
  • PGDG repository is maintained by the PostgreSQL community
  • It ensures you get the most recent features, performance improvements, and security patches

What happens when you install the repository?

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

This command:

  1. Downloads the repository configuration file
  2. Installs it to /etc/yum.repos.d/
  3. Tells your system where to find PostgreSQL packages
  4. Enables access to multiple PostgreSQL versions (12, 13, 14, 15, 16, 17)

Why disable the built-in PostgreSQL module?

sudo dnf -qy module disable postgresql

Rocky Linux 9 has a built-in PostgreSQL module that conflicts with PGDG packages. Disabling it ensures the system uses PGDG versions instead.

pg_ctl - PostgreSQL Control Utility

What is pg_ctl?pg_ctl is PostgreSQL's built-in control utility that directly manages the PostgreSQL server process.

What does it do?

  • Starts and stops the PostgreSQL server
  • Initializes database clusters
  • Reloads configuration
  • Promotes standbys to primary
  • Direct control without systemd

Basic pg_ctl syntax:

/usr/pgsql-16/bin/pg_ctl -D /path/to/data/directory <action>

Common pg_ctl commands:

# Start PostgreSQL
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev start

# Stop PostgreSQL (3 shutdown modes)
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev stop -m smart      # Wait for clients to disconnect
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev stop -m fast       # Disconnect clients gracefully
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev stop -m immediate  # Force shutdown (emergency)

# Restart PostgreSQL
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev restart

# Reload configuration (no restart needed)
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev reload

# Check status
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev status

Shutdown modes explained:

ModeFlagBehaviorUse Case
Smart-m smartWaits for all clients to disconnectSafest, but slowest
Fast-m fastDisconnects clients, rolls back transactionsRecommended for most situations
Immediate-m immediateKills all processes immediatelyEmergency only!

initdb - Database Cluster Initialization

What is initdb?initdb creates a new database cluster. A cluster is a collection of databases managed by a single PostgreSQL server instance.

What does initdb create?

  1. System Catalogs: Internal tables that track databases, users, tables
  2. Template Databases: template0, template1, postgres
  3. Configuration Files: postgresql.conf, pg_hba.conf
  4. Directory Structure: All necessary subdirectories
  5. Initial WAL Files: Write-Ahead Log for transactions

Running initdb:

/usr/pgsql-16/bin/initdb -D /pgdata/16/cosmodev
  • Creates cluster in custom location
  • Uses default port 5432 (we'll change this)
  • Requires manual configuration

Dynamic vs Static Parameters

PostgreSQL has two types of configuration parameters:

TypeReload OK?Restart Required?Examples
Dynamic✅ YesNolog_statement, work_mem
Static❌ NoYesshared_buffers, max_connections, port
Note

🚀 START PRACTICING HERE

You have completed the theory section. Now begin the hands-on installation!

Follow the instructions below step by step.

Part 1: Install PostgreSQL Repository

First, we need to add the official PostgreSQL repository to get the latest version.

Step 1.1: Add the PGDG Repository

# Download and install the PostgreSQL repository configuration
# This tells Rocky Linux where to find PostgreSQL packages
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Step 1.2: Disable the Built-in PostgreSQL Module

# Rocky Linux has a built-in PostgreSQL module that conflicts with PGDG
# We need to disable it to use the newer version from PGDG
sudo dnf -qy module disable postgresql

Step 1.3: Verify the Repository is Installed

# Check that the repository package is installed
rpm -qa | grep pgdg-redhat-repo

You should see something like: pgdg-redhat-repo-42.0-38PGDG.noarch

Part 2: Install PostgreSQL 16 Packages

Now we can install PostgreSQL 16 from the PGDG repository.

Step 2.1: Install PostgreSQL Server and Client

# Install PostgreSQL 16 server, client, and contrib packages
# -server: The database server itself
# (base package): Client tools like psql
# -contrib: Additional useful extensions and utilities
sudo dnf install -y postgresql16-server postgresql16 postgresql16-contrib

Step 2.2: Verify the Installation

# List all installed PostgreSQL 16 packages
rpm -qa | grep postgresql16

You should see packages like:

  • postgresql16-server-16.x
  • postgresql16-16.x
  • postgresql16-contrib-16.x

Step 2.3: Check PostgreSQL Binary Location

# Verify the PostgreSQL binaries are installed
ls /usr/pgsql-16/bin/

You should see tools like: initdb, pg_ctl, psql, postgres

Part 3: Create the Base Directory Structure

Before creating clusters, we need to prepare the directory structure where all our PostgreSQL data will live.

Step 3.1: Create the Parent Directory

# Create the parent directory for all PostgreSQL cluster data
# The -p flag creates parent directories if they don't exist
sudo mkdir -p /pgdata/16

Step 3.2: Set Ownership

# Change ownership to postgres user and group
# PostgreSQL runs as 'postgres' user, so it needs to own these directories
sudo chown postgres:postgres /pgdata/16

Step 3.3: Set Permissions

# Set permissions to 700 (owner can read/write/execute, no one else)
# This is a security requirement - PostgreSQL won't start if permissions are too open
sudo chmod 700 /pgdata/16

Step 3.4: Verify the Directory

# List directory details to confirm ownership and permissions
# Should show: drwx------ postgres postgres
ls -ld /pgdata/16

Step 3.5: Switch to postgres User

# Switch to the postgres user
# All PostgreSQL operations should be done as this user
sudo su - postgres
Note

Important: From this point forward, all commands should be run as the postgres user unless otherwise specified.

Part 4: Cluster Initialization - cosmodev (Port 5445)

Scenario: CosmoTech is setting up a development environment for testing new features.

Step 4.1: Initialize the Cluster

# Initialize a new PostgreSQL cluster in the cosmodev directory
# initdb creates the database cluster structure including:
# - Configuration files (postgresql.conf, pg_hba.conf)
# - System catalogs
# - Template databases
/usr/pgsql-16/bin/initdb -D /pgdata/16/cosmodev

You should see output ending with:

Success. You can now start the database server using:
    /usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev start

Step 4.2: Navigate to the Cluster Directory

# Change to the cluster's data directory
# This is where all configuration files are located
cd /pgdata/16/cosmodev

# List the files to see what initdb created
ls -la

You should see files including postgresql.conf, pg_hba.conf, and directories like base, global, etc.

Step 4.3: Edit postgresql.conf to Change the Port

# Open the main PostgreSQL configuration file in vi editor
vi postgresql.conf

Inside vi, follow these steps:

  1. Press / to enter search mode
  2. Type port and press Enter to find the port setting
  3. You should see a line like: #port = 5432
  4. Press i to enter insert mode
  5. Remove the # at the beginning (this uncomments the line)
  6. Change 5432 to 5445
  7. The line should now read: port = 5445
  8. Press Esc to exit insert mode
  9. Type :wq and press Enter to save and quit
Note

vi Quick Reference:

  • /text - Search for "text"
  • i - Enter insert mode
  • Esc - Exit insert mode
  • :wq - Save and quit
  • :q! - Quit without saving

Step 4.4: Verify the Port Change

# Confirm the port was changed correctly
# grep searches for lines containing "port" in the file
# The ^ means "start of line" to avoid matching commented lines
grep "^port" postgresql.conf

Expected output: port = 5445

Step 4.5: Configure Logging

# Create a directory to store PostgreSQL log files
# Keeping logs organized helps with troubleshooting
mkdir -p /pgdata/16/cosmodev/logs

# Open postgresql.conf again to configure logging
vi postgresql.conf

Inside vi, add these lines at the end of the file:

  1. Press Shift + G to go to the end of the file
  2. Press o to create a new line and enter insert mode
  3. Add these lines:
# Enable the logging collector process
logging_collector = on

# Directory where log files will be stored
log_directory = 'logs'

# Log filename pattern with date
log_filename = 'postgresql-%Y-%m-%d.log'
  1. Press Esc then type :wq to save and quit

Step 4.6: Start the Cluster

# Start the PostgreSQL cluster
# -D specifies the data directory
# -l specifies the log file location
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev -l /pgdata/16/cosmodev/logs/postgresql.log start

You should see: server started

Step 4.7: Verify the Cluster is Running

# Check the status of the cluster
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev status

# Connect to the database and verify the version
# -p specifies the port to connect to
psql -p 5445 -c "SELECT version();"

# Verify we're connected on the correct port
psql -p 5445 -c "SHOW port;"

Part 5: Cluster Initialization - cosmouat (Port 5446)

Scenario: The UAT environment requires a cluster with slow query logging enabled. Any query taking longer than 500ms should be logged for performance analysis.

Step 5.1: Initialize the Cluster

# Initialize the UAT cluster
# Each cluster needs its own data directory
/usr/pgsql-16/bin/initdb -D /pgdata/16/cosmouat

Step 5.2: Navigate to the Cluster Directory

# Change to the UAT cluster directory
cd /pgdata/16/cosmouat

Step 5.3: Edit postgresql.conf for Port and Slow Query Logging

# Open the configuration file
vi postgresql.conf

Inside vi:

  1. Search for port: Press /, type port, press Enter
  2. Uncomment and change to: port = 5446
  3. Press Shift + G to go to end of file
  4. Press o to add new lines:
# Logging configuration for UAT
logging_collector = on
log_directory = 'logs'
log_filename = 'postgresql-%Y-%m-%d.log'

# SLOW QUERY LOGGING
# Log any query that takes longer than 500 milliseconds
# This helps identify performance problems
log_min_duration_statement = 500
  1. Press Esc then :wq to save

Step 5.4: Verify Configuration

# Check the port setting
grep "^port" postgresql.conf

# Check the slow query logging setting
grep "^log_min_duration" postgresql.conf

Step 5.5: Create Logs Directory and Start

# Create the logs directory
mkdir -p /pgdata/16/cosmouat/logs

# Start the UAT cluster
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmouat -l /pgdata/16/cosmouat/logs/postgresql.log start

Step 5.6: Verify

# Check if cluster is running
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmouat status

# Verify slow query logging is enabled
psql -p 5446 -c "SHOW log_min_duration_statement;"

Expected output: 500ms

Part 6: Cluster Initialization - cosmoprod (Port 5447)

Scenario: The production environment requires maximum stability with daily log rotation to manage disk space.

Step 6.1: Initialize the Cluster

# Initialize the production cluster
/usr/pgsql-16/bin/initdb -D /pgdata/16/cosmoprod

Step 6.2: Navigate and Edit Configuration

# Change to the production cluster directory
cd /pgdata/16/cosmoprod

# Open the configuration file
vi postgresql.conf

Inside vi:

  1. Search for port: /port then Enter
  2. Uncomment and change to: port = 5447
  3. Go to end of file: Shift + G
  4. Add new lines: o
# Production logging with daily rotation
logging_collector = on
log_directory = 'logs'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# LOG ROTATION SETTINGS
# Rotate log files every 1 day
# This prevents log files from growing too large
log_rotation_age = 1d

# Set to 0 to disable size-based rotation (only use time-based)
log_rotation_size = 0
  1. Save and quit: Esc then :wq

Step 6.3: Create Logs Directory and Start

# Create logs directory for production
mkdir -p /pgdata/16/cosmoprod/logs

# Start the production cluster
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmoprod -l /pgdata/16/cosmoprod/logs/postgresql.log start

Step 6.4: Verify

# Check cluster status
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmoprod status

# Verify log rotation setting
psql -p 5447 -c "SHOW log_rotation_age;"

Expected output: 1d

Part 7: Cluster Initialization - cosmobackup (Port 5448)

Scenario: A dedicated cluster for backup operations with archive mode enabled. For this cluster, we'll demonstrate using sed to edit configuration and set up the bash profile for convenience.

Step 7.1: Initialize the Cluster

# Initialize the backup cluster
/usr/pgsql-16/bin/initdb -D /pgdata/16/cosmobackup

Step 7.2: Use sed to Change the Port (Alternative Method)

Instead of manually editing with vi, we can use sed (stream editor) to make changes automatically:

# Use sed to uncomment and change the port in one command
# Explanation:
#   sed -i        = Edit file in-place (modify the actual file)
#   "s/old/new/"  = Substitute 'old' with 'new'
#   #port = 5432  = The original commented line
#   port = 5448   = What we want it to become
sed -i "s/#port = 5432/port = 5448/" /pgdata/16/cosmobackup/postgresql.conf

# Verify the change worked
grep "^port" /pgdata/16/cosmobackup/postgresql.conf

Expected output: port = 5448

Note

When to use sed vs vi:

  • sed: Great for automated scripts and single-line changes
  • vi: Better for manual editing and complex changes

Step 7.3: Add Logging and Archive Configuration with vi

# Navigate to the cluster directory
cd /pgdata/16/cosmobackup

# Create logs directory
mkdir -p logs

# Open configuration for remaining settings
vi postgresql.conf

Inside vi, add at the end:

# Logging configuration
logging_collector = on
log_directory = 'logs'

# ARCHIVE MODE CONFIGURATION
# Enable WAL (Write-Ahead Log) archiving
# This is essential for point-in-time recovery
archive_mode = on

# Archive command - what to do with completed WAL files
# '/bin/true' is a placeholder that always succeeds
# In production, you would use: archive_command = 'cp %p /archive/%f'
archive_command = '/bin/true'

Save and quit: Esc then :wq

Step 7.4: Start the Cluster

# Start the backup cluster
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmobackup -l /pgdata/16/cosmobackup/logs/postgresql.log start

Step 7.5: Verify

# Check cluster status
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmobackup status

# Verify archive mode is enabled
psql -p 5448 -c "SHOW archive_mode;"

Expected output: on

Part 8: Setting Up Bash Profile for Convenience

Typing the full path /usr/pgsql-16/bin/pg_ctl every time is tedious. Let's set up the bash profile so we can just type pg_ctl directly.

Step 8.1: Edit the Bash Profile

# Open the postgres user's bash profile
# This file runs every time you log in as postgres
vi ~/.bash_profile

Add these lines at the end of the file:

# PostgreSQL 16 Environment Variables
# These settings make it easier to work with PostgreSQL

# Add PostgreSQL binaries to PATH
# This lets us run pg_ctl, psql, etc. without full path
export PATH=/usr/pgsql-16/bin:$PATH

# Set default data directory for pg_ctl
# Now pg_ctl knows which cluster to manage by default
export PGDATA=/pgdata/16/cosmobackup

# Set default port
# psql will connect to this port by default
export PGPORT=5448

# Optional: Set default database
export PGDATABASE=postgres

Save and quit: Esc then :wq

Step 8.2: Apply the Changes

# Source the profile to apply changes immediately
# Without this, you'd have to log out and back in
source ~/.bash_profile

Step 8.3: Verify the Setup

# Check that PATH is updated
echo $PATH | grep pgsql

# Now we can use pg_ctl without the full path!
pg_ctl status

# And psql connects to the default port automatically
psql -c "SHOW port;"
Note

Congratulations! With the bash profile configured, you can now:

  • Use pg_ctl instead of /usr/pgsql-16/bin/pg_ctl
  • Use psql without specifying -p 5448
  • Use initdb instead of /usr/pgsql-16/bin/initdb

Part 9: Port Verification - All Clusters

Let's verify all four clusters are running on their designated ports.

Step 9.1: Check All Ports with ss

# Use ss (socket statistics) to show listening ports
# -t = TCP connections
# -u = UDP connections  
# -l = Listening sockets
# -n = Show port numbers (not service names)
# -p = Show process using the socket
ss -tulnp | grep postgres

You should see all four ports: 5445, 5446, 5447, 5448

Step 9.2: Test Connectivity to Each Cluster

# Test each cluster by connecting and showing its port
# We need to specify -p for clusters that aren't the default

echo "=== Testing cosmodev (5445) ==="
psql -p 5445 -c "SELECT 'cosmodev' as cluster, current_setting('port') as port;"

echo "=== Testing cosmouat (5446) ==="
psql -p 5446 -c "SELECT 'cosmouat' as cluster, current_setting('port') as port;"

echo "=== Testing cosmoprod (5447) ==="
psql -p 5447 -c "SELECT 'cosmoprod' as cluster, current_setting('port') as port;"

echo "=== Testing cosmobackup (5448) ==="
psql -p 5448 -c "SELECT 'cosmobackup' as cluster, current_setting('port') as port;"

Part 10: Dynamic Parameter Update - cosmodev

Scenario: Developers need verbose logging. Set log_statement = 'all' to log every SQL statement.

Note

Dynamic vs Static Parameters:

  • Dynamic parameters can be changed with reload (no restart needed)
  • Static parameters require a full cluster restart
  • log_statement is dynamic, shared_buffers is static

Step 10.1: Navigate and Edit Configuration

# Change to cosmodev directory
cd /pgdata/16/cosmodev

# Open configuration file
vi postgresql.conf

Add at the end of the file:

# LOG ALL SQL STATEMENTS
# Options: none, ddl, mod, all
# 'all' logs every statement - useful for debugging
log_statement = 'all'

Save and quit: Esc then :wq

Step 10.2: Reload Configuration (No Restart Needed!)

# Reload configuration without stopping the cluster
# This only works for dynamic parameters
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmodev reload

You should see: server signaled

Step 10.3: Verify the Change

# Connect and check the setting
psql -p 5445 -c "SHOW log_statement;"

Expected output: all

Part 11: Static Parameter Update - cosmouat

Scenario: The UAT team needs more memory for performance testing. Increase shared_buffers to 1GB.

Note

Important: shared_buffers is a static parameter. The cluster must be restarted for changes to take effect. A simple reload will NOT work!

Step 11.1: Check Current Value

# See current shared_buffers setting
psql -p 5446 -c "SHOW shared_buffers;"

This will likely show a small value like 128MB.

Step 11.2: Edit Configuration

# Change to cosmouat directory
cd /pgdata/16/cosmouat

# Open configuration file
vi postgresql.conf

Add at the end of the file:

# MEMORY CONFIGURATION
# shared_buffers: Memory for caching data
# Recommended: 25% of total RAM, but not more than 8GB
# This is a STATIC parameter - requires restart!
shared_buffers = 1GB

Save and quit: Esc then :wq

Step 11.3: Restart the Cluster (Required for Static Parameters!)

# Restart the cluster - reload won't work for static parameters!
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmouat -l /pgdata/16/cosmouat/logs/postgresql.log restart

Step 11.4: Verify the Change

# Connect and verify the new setting
psql -p 5446 -c "SHOW shared_buffers;"

Expected output: 1GB

Part 12: Stopping and Starting cosmoprod (Maintenance)

Scenario: Scheduled maintenance requires safely stopping the production cluster.

Step 12.1: Check for Active Connections

# Before stopping, check if anyone is connected
# This query shows all active connections
psql -p 5447 -c "SELECT pid, usename, application_name, state, query 
FROM pg_stat_activity 
WHERE state != 'idle';"

Step 12.2: Stop the Cluster

# Stop the production cluster using fast mode
# Shutdown modes:
#   smart     - Wait for clients to disconnect (safest, slowest)
#   fast      - Disconnect clients, rollback transactions (recommended)
#   immediate - Kill all processes immediately (emergency only!)
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmoprod stop -m fast

You should see: server stopped

Step 12.3: Verify the Cluster is Stopped

# Check status - should show "no server running"
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmoprod status

Step 12.4: Check the Logs for Clean Shutdown

# Look at the last few lines of the log
# Should see "database system is shut down"
tail -10 /pgdata/16/cosmoprod/logs/postgresql.log

Step 12.5: Start the Cluster Again

# Start production back up after maintenance
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmoprod -l /pgdata/16/cosmoprod/logs/postgresql.log start

Step 12.6: Verify It's Running

# Confirm the cluster is running
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmoprod status

# Test connectivity
psql -p 5447 -c "SELECT 'Production is ONLINE!' as status, now() as current_time;"

Part 13: Creating a New Database - cosmodev

Scenario: A new microservice needs its own database called cosmodb.

Step 13.1: Connect and Create the Database

# Connect to cosmodev cluster and create a new database
# CREATE DATABASE creates a new database by copying template1
psql -p 5445 -c "CREATE DATABASE cosmodb;"

You should see: CREATE DATABASE

Step 13.2: Verify the Database Exists

# Query the system catalog to confirm the database was created
psql -p 5445 -c "SELECT datname, datdba, encoding FROM pg_database WHERE datname = 'cosmodb';"

Step 13.3: Connect to the New Database

# Connect directly to the new database
# -d specifies the database name
psql -p 5445 -d cosmodb -c "SELECT current_database();"

Expected output: cosmodb

Part 14: Comprehensive Maintenance - cosmoprod

Scenario: Full maintenance cycle - update work_mem to 64MB for better sort performance.

Note

About work_mem:

  • Memory used for sort operations and hash tables
  • This is per-operation, not per-connection!
  • If 100 connections each run complex queries, memory usage = 100 × 64MB
  • work_mem is a dynamic parameter - reload works!

Step 14.1: Check Current Value

# See current work_mem setting
psql -p 5447 -c "SHOW work_mem;"

Default is usually 4MB.

Step 14.2: Edit Configuration

# Change to cosmoprod directory
cd /pgdata/16/cosmoprod

# Open configuration file
vi postgresql.conf

Add at the end of the file:

# WORK MEMORY
# Memory for internal sort operations and hash tables
# Higher values = faster sorts, but uses more memory
# Be careful: this is PER OPERATION, not per connection!
work_mem = 64MB

Save and quit: Esc then :wq

Step 14.3: Reload Configuration

# Reload - work_mem is dynamic so no restart needed
/usr/pgsql-16/bin/pg_ctl -D /pgdata/16/cosmoprod reload

Step 14.4: Verify the Change

# Confirm the new setting
psql -p 5447 -c "SHOW work_mem;"

Expected output: 64MB

Step 14.5: Check Logs for Any Issues

# Always check logs after configuration changes
tail -20 /pgdata/16/cosmoprod/logs/postgresql.log

Summary

Congratulations! You've completed the CosmoTech PostgreSQL Cluster Management training!

What You Accomplished

ClusterPortKey Configurations
cosmodev5445log_statement = 'all', cosmodb database
cosmouat5446slow query logging (500ms), shared_buffers = 1GB
cosmoprod5447daily log rotation, work_mem = 64MB
cosmobackup5448archive_mode = on, bash profile configured

Skills Mastered

  • ✅ PostgreSQL 16 installation from PGDG repository
  • ✅ Cluster initialization with initdb
  • ✅ Manual configuration editing with vi
  • ✅ Automated editing with sed
  • ✅ Understanding dynamic vs static parameters
  • ✅ Cluster lifecycle with pg_ctl (start, stop, restart, reload)
  • ✅ Bash profile configuration for convenience
  • ✅ Database creation
  • ✅ Production maintenance procedures

Quick Reference

pg_ctl Commands

CommandDescription
pg_ctl -D <path> startStart the cluster
pg_ctl -D <path> stopStop (smart mode)
pg_ctl -D <path> stop -m fastStop (fast mode)
pg_ctl -D <path> restartRestart the cluster
pg_ctl -D <path> reloadReload configuration
pg_ctl -D <path> statusCheck if running

Dynamic vs Static Parameters

Dynamic (reload OK)Static (restart required)
log_statementshared_buffers
work_memmax_connections
log_min_duration_statementport
log_min_messagesarchive_mode

Port Assignments

cosmodev   → 5445
cosmouat   → 5446
cosmoprod  → 5447
cosmobackup → 5448

vi Quick Reference

CommandAction
iEnter insert mode
EscExit insert mode
/textSearch for "text"
GGo to end of file
ggGo to beginning
:wqSave and quit
:q!Quit without saving

About the Author

Happy Afopezi

Happy Afopezi

Find this author online

More tutorials you might like

Learn by doing, not just by reading or watching

Sign up for a free account to start a VM playground right on this page, track your progress, and get notified about new learning materials.

Sign up for free

How to Author Tutorials on iximiuz Labs

Instead of providing a subpar online editing experience, iximiuz Labs offers a helper CLI tool called labctl, allowing you to use your favorite text editor (or a full-featured IDE) to write content from the comfort of your local machine.

Install labctl CLI

curl -sf https://labs.iximiuz.com/cli/install.sh | sh

This will download and install the latest version of the labctl CLI. You only need to do this once per workstation.

Authorize labctl

labctl auth login

This will open a browser window asking you to authorize labctl to access your account. You need to do it after a fresh install of labctl and repeat it whenever the auth session expires.

Pull tutorial content

labctl content pull tutorial managing-postgresql-clusters-c90225a4

This will create a local copy of the tutorial content in a directory named managing-postgresql-clusters-c90225a4. You only need to do this once per tutorial.

Stream your changes

labctl content push -fw tutorial managing-postgresql-clusters-c90225a4

Run this command in a separate terminal to continuously upload your changes to the server while editing the tutorial in your favorite text editor or IDE.

You can also use labctl to create, list, and delete your content. Learn more about the available commands: labctl content --help

Start tutorial