Test PostgreSQL Extension Behavior Before Moving to RDS

Build a version-pinned extension compatibility corpus, separate installation from application behavior, and retain evidence for replacement, deferral or a bounded RDS...

Before committing to RDS, select one extension-dependent operation, declare its expected answer independently, and run the same bounded corpus on separately identified rehearsal environments. Record the engine, extension, role, settings and application path that produced each result. A supported extension name leaves its behavior, privileges and lifecycle requirements to verify.

This playbook supplies the procedure and an offline teaching companion. It does not contain observed AWS or database results. The fictional example proposes self-managed PostgreSQL 17.6 and an RDS PostgreSQL 17.6R2 DB instance, both using pg_trgm 1.6. Those versions make the example inspectable; they are not a latest-version recommendation. Actual account, Region, resources and runtime observations remain UNKNOWN. Aurora, RDS Custom, managed blue/green execution, production cutover and extension licensing determinations are outside scope.

1. Select one operation and assign the review

The application maintainer starts with a concrete consumer: a search query, a generated identifier, a spatial predicate, a scheduler or a foreign-data read. Record the application build and the complete call, including connection initialization and role changes. “We use PostgreSQL” is too broad; “catalogue search uses the whole-string % operator from pg_trgm” can produce a testable contract.

Choose independently expected answers before collecting candidate results. A production source can contain a longstanding defect or use a setting the product owner no longer wants. Source/target agreement is useful evidence, but two wrong answers still agree. If the requirement is intentionally changing, preserve the old expectation and approved new one as different contract revisions. Do not edit expected results after a failure merely to make the candidate pass.

The database owner names the exact environments and obtains the required permission scope. The observer needs only the approved observations, not automatic authority to install extensions, change parameters or restore customer data. The security reviewer selects a controlled destination for logs. Even catalog exports can disclose schemas, user names and infrastructure details. Keep secrets and raw customer values out of the reusable record.

Use the database-selection guide if the unresolved question is which datastore to choose. This playbook starts after a specific PostgreSQL dependency has been identified and ends with its scoped compatibility evidence.

2. Pin the product and the extension independently

Read the exact RDS engine column in the extension-version matrix. On the source-check date, it lists pg_trgm 1.6 for 17.6 and 17.6R2. The same page warns that extension upgrades are separate from engine upgrades and that rds.extensions can display minor-release additions inaccurately. Retain the dated table reference rather than copying one supported-name list into every migration packet.

A documented combination is not proof that the proposed release is available for a new instance in the intended Region or that it is the version already running. Ask the AWS owner for dated, authorized engine-availability and resource readback. The DescribeDBEngineVersions API distinguishes engine versions and supported upgrade information. This package makes no API request. Preserve the requested provider release, returned engine identity and database version() output separately; do not assume a PostgreSQL server-version string represents an RDS packaging suffix.

For an existing source, record installed extension versions per database, not just per host. PostgreSQL's pg_extension catalog identifies installed extensions, their versions, owners and object-schema association. Its schema field is not a license to treat every object in that schema as an extension member. Record the relevant function/operator definitions and extension membership when identity is uncertain.

The observation contract must retain exact strings. An extension version and an engine version use different release schemes. Do not infer compatibility by comparing them numerically or assume that an engine patch upgrades every extension. A changed source, target or extension revision invalidates the corresponding rehearsal record until reviewed.

3. Separate available, installable, installed and usable

PostgreSQL's available-version view lists installable package versions and dependency metadata. It is distinct from the installed-extension catalog. A listed package does not establish that the intended identity can install it, that the chosen database already contains it or that the application can execute its functions.

RDS adds a managed permission boundary. Its extension guidance distinguishes trusted extensions and privileged installation. The restriction documentation lists pg_trgm as trusted and describes rds.allowed_extensions. Omitting an already installed extension from that installation allowlist does not disable its existing use. Therefore “not allowed to create” and “cannot execute an installed function” are different findings. Do not change that parameter as a diagnostic shortcut.

Have the installer document the authorized installation path and the application owner document the intended runtime role. A privileged operator's successful query is not an application-role test. Conversely, an application role need not gain installation authority merely because a deployment needs an extension. Keep setup privileges outside the ordinary runtime identity where the reviewed design permits that separation.

The companion inventory specimen contains read-only catalog queries. Run it only through an approved connection and retain failed reads rather than turning them into empty inventories. Repeat for every in-scope database and include application, job and routine references that catalogs alone cannot discover. A completed query over one database does not prove host-wide completeness.

4. Prepare an isolated setup without hiding changes

Use a separately authorized disposable source clone and target rehearsal database when the test requires installation, schema creation, writes or changes to extension objects. A physical read-only replica is not that surface. The managed blue/green paper owns that method's restrictions; this corpus grants no exception.

Before setup, review the extension package, prerequisites, schema ownership and allowed changes. CREATE EXTENSION runs an installation script in the current database. Explicitly record the requested version and dependency versions. Its IF NOT EXISTS option does not establish that an existing installation matches the requested one. Do not use an ignored duplicate installation as version verification. Installation-time schema trust also matters, so do not grant untrusted writers access to the chosen installation or dependency schemas to get past an error.

No installation or upgrade command is included in the corpus. Those are separately approved setup changes, with their own resulting-state readback. Once setup is complete, record the actual extension-object schema used by the specimen. Its quoted schema variable selects reviewed objects; it must not be filled from arbitrary untrusted input. A schema-qualified name narrows resolution, but does not authenticate the code behind that name.

Keep this exercise small. A function-level ASCII corpus does not require customer tables or production load. Set a finite observation window and acceptable resource use. Stop on an unexpected environment, role, package, denied required observation or external side effect. Do not promote a rehearsal to production merely because the script contains read-only transactions.

5. Preserve the independent answer path

The review has three inputs: the independently declared contract, source observations and target observations. No observation rewrites the contract. The control profiles help explain a mismatch but remain distinguishable from the application's actual connection state.

Proposed evidence flow, not a deployment or executed test. Solid arrows carry review inputs. The absence of a source-to-target arrow is deliberate: source agreement is not the independent expected answer.

The source and target observer records must identify the same corpus and operation revision, while retaining their own environment, database, role and settings. An operator can accidentally run both captures against the source. Different filenames do not establish distinct destinations. Have the independent reviewer compare connection/resource evidence with each capture before interpreting matching answers.

The review should explain both a required positive and a deliberate failure. If the chosen test cannot distinguish a meaningful behavior change, it may be too weak for the operation. Do not turn every possible difference into a required migration blocker: define which differences affect the business contract and which are permitted diagnostics.

6. Work the pg_trgm threshold counterexample

The fictional operation is catalogue-match-r1, with three input pairs and independently stipulated boolean answers. It uses the whole-string % operator, not word-similarity operators. PostgreSQL documents the function, operator and setting distinctions in pg_trgm. Its example gives similarity('word', 'words') as approximately 0.571429. That supports an expected match at threshold 0.3 and a non-match at 0.6 without making an equality-at-threshold claim.

The business contract wants the near-match, accepts the identical string and rejects the dissimilar pair. These are teaching requirements, not evidence that those three strings adequately represent a real search feature. The diagnostic 0.6 profile deliberately violates the near-match requirement while preserving the other two answers. A corpus containing only identical strings would miss this defect.

near: word compared with words

Business answer true. Controlled 0.3 expectation true; controlled 0.6 expectation false. The second result is an expected diagnostic failure of the business contract, not an acceptable replacement.

same: word compared with word

Business answer true. Both controlled profiles expect true. This positive alone cannot detect the threshold regression.

apart: word compared with xyz

Business answer false. Both controlled profiles expect false. Retaining a rejection case prevents an always-true implementation from looking compatible.

Suppose a supplied target packet reports extension 1.6, threshold 0.6 and false, true, false for those cases. Its package matches and its two easy cases agree, but the near-match fails. HOLD the operation's compatibility decision. Ask where the effective setting comes from and whether the required application path can preserve the agreed behavior. This is a stipulated packet, not a reproduced RDS incident.

A second supplied packet reports 0.3 with true, true, false. It meets this tiny corpus on paper. It still lacks authenticated environment evidence, real application execution, representative search data, index/plan behavior, concurrency and performance observations. Do not replace the hold on the overall migration with a green extension badge.

7. Observe the effective session before running controls

The corpus specimen first identifies the database/session and loads the reviewed extension function through its actual schema. It then captures the effective threshold and three answers without overriding that threshold. This baseline is the evidence relevant to the connection being examined. A direct SQL connection still does not prove that the application pool initializes sessions the same way.

Only afterward do separate transactions apply the 0.3 and 0.6 control profiles. PostgreSQL documents that SET LOCAL lasts to the transaction boundary. The specimen uses ROLLBACK to end each read-only control transaction and a separate capture label for each result. These local controls diagnose sensitivity; they do not change the application's global configuration or prove it will reconnect with the right state.

Use a fresh, approved session and the intended role. Review connection startup settings and any role switching before calling it representative. The script records session_user and current_user; unexpected differences need explanation. Do not put credentials in command history or output files. The sample invocation refers to an already configured service alias and provides no secret values.

# Specification only. Requires separate environment/access approval.
psql -X --dbname="service=approved_extension_rehearsal" \
  --set=ON_ERROR_STOP=1 --set=extension_schema=reviewed_schema \
  --file=awsbr-p004-corpus.sql

The alias and schema are placeholders to resolve through the approved setup, not working connection details. The psql reference defines quoted variable interpolation and error-stop behavior. Preserve standard output, standard error and exit status. A partial output followed by an error is not a completed corpus; an absent profile remains missing. End the failed session and record the error before requesting another attempt.

8. Review the offline companion within its narrow boundary

Download the offline extension-behavior companion (ZIP). No email is required. The eight files contain synthetic teaching data and inert SQL specifications, not acquired database or AWS results. Read the included README before running the offline Python tests; SQL execution requires separate authorization.

The companion includes catalog SQL, corpus SQL, literal expectations, a supplied synthetic result file and a dependency-free checker. The checker compares that file with the fixed expectations and validates the required case/profile coverage. Its deliberately small input has no live endpoints or credentials. It neither invokes PostgreSQL nor implements trigram matching.

The synthetic file is explicitly marked supplied. Passing its tests demonstrates only that the comparison code detects its declared missing, duplicate, malformed and changed-answer cases. It cannot authenticate a result, identify an AWS resource, discover unlisted consumers, prove extension installation or approve a real rehearsal. Even a fabricated but internally consistent packet can match. The output therefore says CORPUS_REVIEW_ONLY, never “RDS compatible,” and keeps migration authorization false.

Use retained actual outputs and controlled identities for a real review, not edited copies of the supplied sample. The offline companion is a teaching aid for reading a result. It does not acquire evidence. The diagnostic 0.6 answers can pass the checker because they match the deliberate control expectations while failing the business contract.

9. Investigate mismatches without erasing them

The application and database owners classify a failure before changing anything. A missing package, denied schema access, changed operator, different effective setting and unexpected result require different next evidence. Keep the original command, identities, error and observation intact. Do not silently rerun as a privileged role or substitute another operator, because either changes what was tested.

An extension upgrade needs its own version path. ALTER EXTENSION requires a suitable update script or chain; a higher package number is not enough. Do not assume an inverse downgrade path exists. If the candidate needs replacement behavior instead, create a new operation contract, data conversion plan and independently expected corpus. Preserving a function name is not proof that a replacement preserves semantics.

Dump/restore also deserves a separate rehearsal. PostgreSQL's extension packaging documentation explains how member objects and designated configuration data are handled. This function-only corpus does not establish that extension-owned state or custom changes survive the proposed migration mechanism. Have the database owner inspect the actual dump/restore procedure and dependent objects; the restore-evidence paper supplies the broader recovery contract.

If no supported target can preserve a required operation, record a no-go for that proposed target. Alternatives include an approved application replacement, a supported extension/version combination with a new rehearsal, retaining the present hosting model, or deferring the move. Each alternative must satisfy the same business requirement or explicitly change it through the accountable owner. No licensing or commercial eligibility conclusion follows from an extension appearing in documentation.

10. Keep a filled record beside a blank one

The filled example intentionally ends in HOLD because no actual environment was observed. Its useful work is to specify the next evidence, not to manufacture a completed migration record.

Record and accountable owners

extension-review-r1; fictional database-owner and catalogue-owner; independent reviewer unassigned. No human approval is asserted.

Environments and exact releases

source-clone-a proposed PostgreSQL 17.6; target-rehearsal-b proposed RDS PostgreSQL 17.6R2 DB instance. Account, Region, resource identities and actual server versions UNKNOWN.

Extension and database scope

pg_trgm 1.6 proposed for one catalogue database. Published version pairing found; installation, schema, other databases and consumers UNKNOWN.

Authority and runtime identity

Installer and application roles not observed. Allowed-extension policy, schema privileges, approved access and independent review remain required. No installation or parameter change authorized.

Operation and expected corpus

catalogue-match-r1, corpus-ascii-r1: near true, same true, apart false. Application owner stipulates these teaching answers; real requirements are not supplied.

Settings and observation order

Capture effective application session first. Run 0.3 and 0.6 transaction-local controls separately; retain each threshold with its answers and role. Actual settings UNKNOWN.

Results and discrepancies

Supplied regression packet at 0.6 is false, true, false and fails near. The 0.3 contrast is true, true, false. Both are synthetic, with no database execution or provenance evidence.

Coverage and exclusions

Three fixed ASCII pairs, one boolean operator. No real search population, locale behavior, index plan, load, extension data migration, recovery or application pool observed.

Stop and recovery boundary

Stop on wrong identity, unexpected privilege, missing profile or denied evidence. End the isolated session, preserve logs and retain source authority. No automatic production rollback or cleanup.

Disposition, next evidence and invalidators

HOLD actual compatibility. Database owner obtains authorized exact-target and role evidence; catalogue owner defines the real corpus. Engine, package, role, schema, query, settings or data-shape changes reopen affected checks.

Complete the same fields without inheriting the example's answers or resource assumptions. Keep controlled evidence references rather than sensitive payloads.

Record and accountable owners

Record/revision/date; database, application, security and independent review owners; authority references.

Environments and exact releases

Source and target product/resource/database identities; exact requested and observed engine versions; Region/account; capture time and evidence; missing fields.

Extension and database scope

Name, installed/requested versions, package support source, object schema/membership, dependencies, all in-scope databases and unresolved consumers.

Authority and runtime identity

Approved installer path, installation restrictions, schema privileges, actual session/current role, application connection path and denied checks.

Operation and expected corpus

Application/query revision, exact operator or function, input identities, independently expected outputs, negative cases, accepted differences and owner.

Settings and observation order

Effective initialization and settings before controls; control changes and transaction boundaries; separate result labels; reconnect evidence.

Results and discrepancies

Immutable source/target output references, script/corpus hashes, exit/error status, missing cases, exact mismatches and next diagnostic evidence.

Coverage and exclusions

Tested population and role; omitted behavior; collation, performance, upgrade, extension state, restore and real-client tests required elsewhere.

Stop and recovery boundary

Approved duration/load; stop signals, operator, session termination, retained evidence, separately authorized setup recovery and cleanup targets.

Disposition, next evidence and invalidators

HOLD, bounded candidate or rejected target; specific missing observation and owner; alternative; changed facts that reopen review; separate migration authority.

11. Stop safely and retain the failed basis

For this read-only corpus, the immediate stop is to end the isolated session and preserve partial observations. Transaction-local control settings do not provide a general rollback mechanism for earlier setup work. Extension installation, upgrade, copied data or external integrations belong to their own approved change and recovery plan. Never drop an extension with dependent application objects merely to leave the rehearsal looking clean.

The cleanup owner should use the exact disposable-resource inventory, retention decision and authorization. This playbook does not authorize deletion of a database, backup or evidence file. If a setup change cannot be reversed safely, retain the environment as isolated and ask for a recovery decision. A recreated empty schema is not restoration of lost extension-owned data.

Production source authority remains unchanged throughout this proposed rehearsal. Once a real migration introduces target writes, the return path becomes a different problem. Use the migration compatibility and recovery gates; a passing extension test does not establish synchronized failback.

12. Close the extension decision, not the migration

The independent reviewer should be able to trace each required result to its environment, application path, corpus revision and expected answer. Ask the application owner to explain the deliberately failing case and the database owner to explain the installed-versus-available distinction. If either answer depends on an unobserved assumption, leave that part held.

Done means one operation has a defensible disposition and an evidence trail: the proposed target is rejected for a hard conflict, remains held for named missing evidence, or is a bounded candidate supported by actual authorized observations. It does not mean every extension, locale, job, recovery path and workload has been accepted. The three-pair teaching corpus cannot close those wider requirements.

Start with the extension-dependent operation that would block the move if it changed. Bring its filled record, a failed case and the next requested observation to the database and application owners. An optional AWS migration review can examine that packet. Any later installation, upgrade, parameter change, resource creation or traffic movement needs its own authority and current evidence.