Why Upgrade PostgreSQL and How to Perform a Major Version Upgrade Using PGQ

1. Why Upgrade PostgreSQL Although a lower‑version PostgreSQL instance may be stable, each major release introduces significant new features—such as materialized views, event triggers (9.3), jsonb (9.4), logical decoding, upsert and BRIN indexes (9.5), parallel sequential scans and synchronous standby (9.6), and table‑level logical replication and partitioning (10.0). These features can dramatically improve performance and reduce development and operations effort. Moreover, the PostgreSQL community only maintains the five most recent major versions; versions older than 9.3 are no longer patched, so staying on an outdated version risks unaddressed bugs.

2. Traditional Upgrade Approaches Upgrades can be either minor (simple restart) or major. The official major‑version methods are pg_upgrade and pg_dumpall . While pg_dumpall is often used in production clusters, its duration scales with data size, requiring long service downtime—unacceptable for systems with 99.999% availability requirements. Therefore a faster solution like PGQ is desirable.

3. PGQ Overview and Features PGQ is a module of SkyTools (originally from Skype) that provides a queue system written in PL/pgSQL, Python, and C. It consists of producers, a ticker, and consumers. Producers generate events, the ticker batches them, and consumers turn events into SQL statements. PGQ works with other SkyTools modules such as Londiste (Python‑based replication) and Walmgr (WAL archiving). The cluster topology includes root, branch, and leaf nodes, allowing flexible data flow to downstream systems like Greenplum, Elasticsearch, Kafka, or SQL Server. Key characteristics include millisecond‑level data propagation, unlimited downstream node count, fault‑tolerant node switching via takeover , support for tables and sequences, requirement for primary keys or unique indexes, and open‑source API access.

4. PostgreSQL Major‑Version Upgrade Procedure Using PGQ

1) Verify that the PGQ version matches the target PostgreSQL version; if not, modify the PGQ source code, e.g.:

vi sql/pgq/triggers/stringutil.c

Adjust the include path for keywords.h :

- src/include/parser/keywords.h

+ src/include/common/keywords.h

2) Install the PGQ cluster: configure a root node on the old version and a branch node on the new version. Both nodes need a .pgqd.ini file and node‑specific configuration files.

[pgqd]
logfile = pgqd.log
pidfile = pgqd.pid
base_connstr = host=l-pgtest1.dev user=pgq password=pgq port=5432
database_list = postgres
ticker_period = 1

Create node configuration files:

vi pgq_root.ini
[londiste3]
job_name = pgq_root
db = host=l-pgtest1.dev port=5432 dbname=postgres user=pgq password=pgq
pgq_queue_name = pgq
logfile = pgq_root.log
pidfile = pgq_root.pid

vi pgq_branch.ini
[londiste3]
job_name = pgq_branch96
db = host=l-pgtest1.dev port=8432 dbname=postgres user=pgq password=pgq
pgq_queue_name = pgq
logfile = pgq_branch96.log
pidfile = pgq_branch96.pid

3) Create the root node:

londiste3 pgq_root.ini create-root pgq_root 'host=l-pgtest1.dev port=5432 dbname=postgres user=pgq password=pgq'

4) Create the branch node (pointing to the root as provider):

londiste3 pgq_branch.ini create-branch pgq_branch96 'host=l-pgtest1.dev port=8432 dbname=postgres user=pgq password=pgq' --provider='host=l-pgtest1.dev port=5432 dbname=postgres user=pgq password=pgq'

5) Add synchronization objects (tables). Ensure each table has a primary key or unique index, then run:

londiste3 pgq_root.ini add-table tablename1 tablename2

6) Verify cluster health:

londiste3 pgq_root.ini status

7) Perform the upgrade by switching the root node to the branch node:

londiste3 pgq_branch.ini takeover pgq_root

8) Restart the high‑version PostgreSQL instance if it runs on the same server (to apply the new port), otherwise no restart is needed.

5. Upgrade Summary

The PGQ‑based approach achieves near‑second upgrade times with minimal service interruption, offers flexible rollback via root‑node switching, and maintains compatibility of extensions and other PostgreSQL features across versions.