Instructions for Upgrading PostgreSQL 17.6 to 18.1 (Production Standard)

PostgreSQL 18 was officially launched on September 25, 2025, marking one of the most important releases in PostgreSQL history. With introduction Asynchronous I/O subsystem, the performance of reading from storage is improved up to 3 times, along with many new features such as UUIDv7, Virtual Generated Columns, and OAuth 2.0 authentication.

This article will provide detailed instructions on how to upgrade from PostgreSQL 17.6 to 18.1 according to production standards, ensuring Minimum downtime and Safe rollback.

1. Why Should You Upgrade to PostgreSQL 18?

1.1. Outstanding Features

Features	Description	Benefits
Asynchronous I/O	New asynchronous I/O system	Speed up sequential scan, bitmap heap scan up to 3x
Statistics Preservation	Keep planner statistics when upgrading	No need to run ANALYZE after upgrade
Skip Scan	Lookup on multicolumn B-tree indexes	Query is faster when conditions are omitted `=` on prefix columns
UUIDv7	Native `uuidv7()` function	UUID has timestamp, better for indexing
Virtual Generated Columns	Computed columns at query time	More flexibility in schema design
OAuth 2.0	Authentication with identity providers	Easy SSO integration
pg_upgrade --swap	New swap mode	Upgrade faster, no need to copy files

1.2. Breaking Changes to Note

⚠️ QUAN TRỌNG - Các thay đổi không tương thích ngược:


Data Checksums mặc định BẬT (initdb)

Cần matching checksum settings khi pg_upgrade



MD5 authentication DEPRECATED

Nên migrate sang SCRAM-SHA-256



Time zone abbreviation handling thay đổi

Session timezone được ưu tiên trước timezone_abbreviations

2. Prepare Before Upgrading

2.1. Checklist Pre-Upgrade

#!/bin/bash



pre-upgrade-checklist.sh
echo "=== PostgreSQL Upgrade Checklist ==="
1. Kiểm tra version hiện tại
echo "1. Current PostgreSQL Version:"
psql -c "SELECT version();"
2. Kiểm tra disk space
echo "2. Disk Space (cần ít nhất 2x data directory size):"
df -h /var/lib/postgresql
3. Kiểm tra data directory size
echo "3. Data Directory Size:"
du -sh /var/lib/postgresql/17/main
4. Kiểm tra checksum status
echo "4. Data Checksum Status:"
pg_controldata /var/lib/postgresql/17/main | grep "Data page checksum"
5. Kiểm tra extensions
echo "5. Installed Extensions:"
psql -c "SELECT extname, extversion FROM pg_extension ORDER BY extname;"
6. Kiểm tra replication slots
echo "6. Replication Slots:"
psql -c "SELECT slot_name, slot_type, active FROM pg_replication_slots;"
7. Kiểm tra prepared transactions
echo "7. Prepared Transactions (should be empty):"
psql -c "SELECT * FROM pg_prepared_xacts;"

2.2. Backup Strategy

IMPORTANT: Always backup before upgrading!

# 1. Full backup với pg_dumpall (logical backup) pg_dumpall -U postgres -h localhost -f /backup/full_backup_$(date +%Y%m%d).sql 2. Base backup (physical backup) - khuyến nghị pg_basebackup -D /backup/basebackup_$(date +%Y%m%d) -Ft -z -P -U replication -h localhost 3. Backup configuration files

cp /etc/postgresql/17/main/postgresql.conf /backup/ cp /etc/postgresql/17/main/pg_hba.conf /backup/ cp /etc/postgresql/17/main/pg_ident.conf /backup/

2.3. Install PostgreSQL 18.1

# Ubuntu/Debian sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt-get install postgresql-18 RHEL/Rocky Linux

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

3. Upgrade Methods

3.1. Comparison of Methods

Method	Downtime	Use Case	Complexity
pg_upgrade	Minutes	Same host, brief pause	Low
pg_upgrade --link	Seconds - Minutes	Same filesystem, fastest	Low
pg_upgrade --swap (New PG18)	Seconds - Minutes	Swap directories	Low
Logical Replication	Seconds	24x7 apps, zero-downtime	High
pg_dump/restore	Hour - Date	Small DBs, cross-platform	Low

3.2. Recommendations for Production

┌─────────────────────────────────────────────────────────────┐
│                    DECISION FLOWCHART                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Database size < 100GB?                                      │
│     YES → pg_upgrade --link hoặc --swap                     │
│     NO  ↓                                                   │
│                                                              │
│  Zero-downtime required?                                     │
│     YES → Logical Replication                                │
│     NO  → pg_upgrade --link với maintenance window           │
│                                                              │
│  Cross-platform migration?                                   │
│     YES → pg_dump/restore                                    │
│                                                              │
└─────────────────────────────────────────────────────────────┘

4. Upgrade With pg_upgrade (Recommended)

4.1. Initializing a New Cluster

# Tạo data directory cho PostgreSQL 18 sudo mkdir -p /var/lib/postgresql/18/main sudo chown postgres:postgres /var/lib/postgresql/18/main Chuyển sang user postgres sudo -i -u postgres Kiểm tra checksum của cluster cũ pg_controldata /var/lib/postgresql/17/main | grep "Data page checksum" Output: Data page checksum version: 0 (disabled) hoặc 1 (enabled) Init cluster mới với MATCHING checksum setting Nếu cluster cũ KHÔNG có checksum: /usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --no-data-checksums --encoding=UTF8 --locale=en_US.UTF-8 Nếu cluster cũ CÓ checksum (hoặc muốn enable):

/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/main --data-checksums --encoding=UTF8 --locale=en_US.UTF-8

4.2. Pre-Upgrade Check

# Set environment variables export PGBINOLD=/usr/lib/postgresql/17/bin export PGBINNEW=/usr/lib/postgresql/18/bin export PGDATAOLD=/var/lib/postgresql/17/main export PGDATANEW=/var/lib/postgresql/18/main Chạy check mode TRƯỚC /usr/lib/postgresql/18/bin/pg_upgrade --old-datadir=$PGDATAOLD --new-datadir=$PGDATANEW --old-bindir=$PGBINOLD --new-bindir=$PGBINNEW --check Expected output: Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database connection settings ok Checking database user is the install user ok Checking for prepared transactions ok ... Clusters are compatible

4.3. Stop Services & Upgrades

# 1. Stop ứng dụng kết nối đến database
(tùy thuộc vào setup của bạn)
2. Stop PostgreSQL 17
sudo systemctl stop postgresql@17-main
3. Verify PostgreSQL 17 đã stop
pg_isready -p 5432
Output: no response
4. Thực hiện upgrade với --link mode (nhanh nhất)
cd /var/lib/postgresql
/usr/lib/postgresql/18/bin/pg_upgrade 

--old-datadir=$PGDATAOLD 

--new-datadir=$PGDATANEW 

--old-bindir=$PGBINOLD 

--new-bindir=$PGBINNEW 

--link 

--jobs=$(nproc)
Hoặc với --swap mode (PostgreSQL 18 mới)
/usr/lib/postgresql/18/bin/pg_upgrade 

--old-datadir=$PGDATAOLD 

--new-datadir=$PGDATANEW 

--old-bindir=$PGBINOLD 

--new-bindir=$PGBINNEW 

--swap 

--jobs=$(nproc)

4.4. Copy Configuration Files

# Copy các config files từ cluster cũ cp /etc/postgresql/17/main/postgresql.conf /etc/postgresql/18/main/ cp /etc/postgresql/17/main/pg_hba.conf /etc/postgresql/18/main/ cp /etc/postgresql/17/main/pg_ident.conf /etc/postgresql/18/main/ Hoặc nếu dùng data directory cho config cp $PGDATAOLD/postgresql.conf $PGDATANEW/ cp $PGDATAOLD/pg_hba.conf $PGDATANEW/ Cập nhật port trong postgresql.conf nếu cần (nếu muốn chạy song song cả 2 version)

sed -i 's/port = 5432/port = 5433/' /etc/postgresql/18/main/postgresql.conf

4.5. Start PostgreSQL 18 & Verify

# Start PostgreSQL 18
sudo systemctl start postgresql@18-main
Verify connection
psql -p 5432 -c "SELECT version();"
Output: PostgreSQL 18.1 on x86_64-pc-linux-gnu...
Verify databases
psql -c "\l"
Verify table counts (sample)
psql -d your_database -c "SELECT schemaname, COUNT(*) FROM pg_tables GROUP BY schemaname;"

5. Post-Upgrade Tasks

5.1. Statistics Handling (PostgreSQL 18 automatically preserves)

# PostgreSQL 18 tự động preserve statistics! Chỉ cần chạy cho extended statistics nếu có: /usr/lib/postgresql/18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only Hoặc chạy đầy đủ nếu muốn

vacuumdb --all --analyze

5.2. Extension Updates

# Kiểm tra extensions cần update
psql -c "SELECT * FROM pg_extension WHERE extversion != (SELECT default_version FROM pg_available_extensions WHERE name = extname);"
Update tất cả extensions
psql -c "SELECT format('ALTER EXTENSION %I UPDATE;', extname) FROM pg_extension;" | psql
Hoặc update từng extension
psql -c "ALTER EXTENSION pg_stat_statements UPDATE;"
psql -c "ALTER EXTENSION postgis UPDATE;"

5.3. Cleanup

# Xóa cluster cũ (CHỈ SAU KHI VERIFY HOÀN TẤT!) pg_upgrade tạo script delete_old_cluster.sh ./delete_old_cluster.sh Hoặc thủ công rm -rf /var/lib/postgresql/17/main Disable PostgreSQL 17 service

sudo systemctl disable postgresql@17-main

5.4. Performance Validation

-- Kiểm tra query plan của các query quan trọng
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM your_critical_table WHERE your_condition;
-- So sánh với baseline trước upgrade
-- Lưu ý: PostgreSQL 18 EXPLAIN ANALYZE tự động include BUFFERS
-- Kiểm tra AIO settings (PostgreSQL 18)
SHOW io_method;
-- Output: io_uring (Linux) hoặc worker
-- Monitor với pg_stat_io (mới trong PG16, cải thiện PG18)
SELECT * FROM pg_stat_io WHERE reads > 0;

6. Upgrade With Logical Replication (Zero-Downtime)

6.1. Architecture Overview

┌──────────────────────┐         ┌──────────────────────┐
│   PostgreSQL 17.6    │         │   PostgreSQL 18.1    │
│     (Publisher)      │  ─────► │    (Subscriber)      │
│   Primary/Source     │   WAL   │   Target/Replica     │
│     Port: 5432       │         │     Port: 5433       │
└──────────────────────┘         └──────────────────────┘
│
▼
Cutover (DNS/HAProxy)

6.2. Setup Publisher (PostgreSQL 17)

-- 1. Enable logical replication trong postgresql.conf
-- wal_level = logical
-- max_replication_slots = 10
-- max_wal_senders = 10
-- 2. Tạo publication
CREATE PUBLICATION pg18_migration FOR ALL TABLES;
-- 3. Tạo replication user
CREATE USER repl_user WITH REPLICATION PASSWORD 'secure_password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;
-- 4. Cập nhật pg_hba.conf
-- host    all    repl_user    subscriber_ip/32    scram-sha-256
-- host    replication    repl_user    subscriber_ip/32    scram-sha-256

6.3. Setup Subscriber (PostgreSQL 18)

# 1. Dump schema only (không data) pg_dump -h old_server -U postgres -s -d your_db > schema.sql 2. Restore schema vào PostgreSQL 18

psql -d your_db -f schema.sql

-- 3. Tạo subscription
CREATE SUBSCRIPTION pg18_sub
CONNECTION 'host=old_server port=5432 dbname=your_db user=repl_user password=secure_password'
PUBLICATION pg18_migration;
-- 4. Monitor sync progress
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_subscription_rel;
-- 5. Kiểm tra lag
SELECT
slot_name,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) as lag
FROM pg_replication_slots;

6.4. CutoverProcess

#!/bin/bash
cutover.sh - Zero-downtime cutover script
echo "=== Starting Cutover Process ==="
1. Stop writes to old server (application level)
echo "1. Stopping application writes..."
kubectl scale deployment/app --replicas=0
hoặc update HAProxy/PgBouncer
2. Wait for replication to catch up
echo "2. Waiting for replication lag to be 0..."
while true; do
LAG=$(psql -h new_server -p 5433 -t -c 

"SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) FROM pg_replication_slots WHERE slot_name='pg18_sub'")
if [ "$LAG" -eq 0 ]; then
break
fi
sleep 1
done
3. Disable subscription
echo "3. Disabling subscription..."
psql -h new_server -p 5433 -c "ALTER SUBSCRIPTION pg18_sub DISABLE;"
psql -h new_server -p 5433 -c "DROP SUBSCRIPTION pg18_sub;"
4. Reset sequences (quan trọng!)
echo "4. Resetting sequences..."
psql -h new_server -p 5433 -c "SELECT setval(c.oid, s.last_value) FROM pg_class c JOIN pg_sequences s ON c.relname = s.sequencename WHERE s.last_value IS NOT NULL;"
5. Switch traffic to new server
echo "5. Switching traffic..."
Update DNS, HAProxy, PgBouncer, etc.
echo "=== Cutover Complete ==="

7. Rollback Plan

7.1. Rollback From pg_upgrade --link

# QUAN TRỌNG: Với --link mode, bạn KHÔNG THỂ rollback sau khi start cluster mới! Do đó, LUÔN test kỹ trước khi start Nếu chưa start PostgreSQL 18: Chỉ cần start lại PostgreSQL 17

sudo systemctl start postgresql@17-main

7.2. Rollback From pg_upgrade --copy

# Nếu dùng --copy mode, cluster cũ vẫn còn nguyên Chỉ cần switch về cluster cũ

sudo systemctl stop postgresql@18-main sudo systemctl start postgresql@17-main

7.3. Rollback From Logical Replication

-- Trên subscriber (PostgreSQL 18) ALTER SUBSCRIPTION pg18_sub DISABLE; DROP SUBSCRIPTION pg18_sub;

-- Switch traffic về PostgreSQL 17 -- Update DNS/HAProxy/PgBouncer

8. Monitoring & Troubleshooting

8.1. Monitoring Script

#!/bin/bash
monitor-upgrade.sh
echo "=== PostgreSQL 18 Health Check ==="
1. Version
psql -c "SELECT version();"
2. Uptime
psql -c "SELECT pg_postmaster_start_time(), now() - pg_postmaster_start_time() as uptime;"
3. Active connections
psql -c "SELECT count(*) as active_connections FROM pg_stat_activity WHERE state = 'active';"
4. Database sizes
psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;"
5. Long running queries
psql -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND now() - pg_stat_activity.query_start > interval '5 minutes';"
6. Replication status (if applicable)
psql -c "SELECT * FROM pg_stat_replication;"
7. I/O statistics (PostgreSQL 18)
psql -c "SELECT backend_type, reads, writes, extends FROM pg_stat_io WHERE reads > 0 OR writes > 0;"

9. Best Practices Summary

9.1. Pre-Upgrade

[ ] Read carefully Release Notes PostgreSQL 18
[ ] Test upgrade on staging/development environment
[ ] Full backup (logical + physical)
[ ] Document rollback plan
[ ] Notify stakeholders about maintenance window
[ ] Check compatibility of all extensions

9.2. During Upgrade

[ ] Use pg_upgrade --check before the actual upgrade
[ ] Monitor disk space during upgrade
[ ] Keep terminal session with screen or tmux
[ ] Log all output for troubleshooting

9.3. Post-Upgrade

[ ] Verify data integrity
[ ] Test critical queries and compare with baseline
[ ] Update extensions
[ ] Monitor performance 24-48 hours
[ ] Cleanup old cluster after verification is complete
[ ] Update documentation and runbooks

10. Conclusion

PostgreSQL 18 brings many valuable improvements, especially Asynchronous I/O and Statistics Preservation Helps upgrade smoother than ever. With careful planning and following the right process, you can upgrade your production database Minimum downtime and low risk.

References:

If the article is useful, please share and leave a comment below! 👇