- 1. Install
- 2. Development
- 3. Usage
- 4. make targets
- 5. Version-Specific SQL Files
- 6. Document Handling
- 7. pg_tle Support
- 8. Configuration Variables
- 8.1. PGXN_REMOTE
- 8.2. ASCIIDOC
- 8.3. PG_CONFIG
- 8.4. TESTDIR
- 8.5. TESTOUT
- 8.6. PGXNTOOL_VERIFY_RESULTS_MODE
- 8.7. PGXNTOOL_ENABLE_TEST_BUILD *
- 8.8. PGXNTOOL_ENABLE_TEST_INSTALL *
- 8.9. PGXNTOOL_ENABLE_VERIFY_RESULTS *
- 8.10. PGXNTOOL_ENABLE_CHECK_STALE_EXPECTED *
- 8.11. PGXNTOOL_CHECK_EXPECTED_FILE_TYPES *
- 8.12. PGXNTOOL_NO_PGXS_INCLUDE
- 9. Copyright
PGXNtool is meant to make developing new Postgres extensions for PGXN easier.
Currently, it consists a base Makefile that you can include instead of writing your own, a template META.in.json (from which META.json is generated — you edit the former, never the latter directly), and some test framework. More features will be added over time.
If you find any bugs or have ideas for improvements, please open an issue.
This assumes that you’ve already initialized your extension in git.
|
Note
|
The --squash is important! Otherwise you’ll clutter your repo with a bunch of commits you probably don’t want.
|
git subtree add -P pgxntool --squash git@github.com:decibel/pgxntool.git release pgxntool/setup.sh
TODO: Create a nice script that will init a new project for you.
If you want to contribute to pgxntool development, work from the pgxntool-test repository, not from this repository. That repository contains the test infrastructure and development tools needed to validate changes to pgxntool. This repository contains only the framework files that get embedded into extension projects via git subtree.
Changes are normally paired across both repos: a pgxntool change should come with a matching branch (same name, on the same account) and PR in pgxntool-test, and CI enforces this pairing. See the CI and Contributing section in pgxntool-test for the full workflow.
Typically, you can just create a simple Makefile that does nothing but include base.mk:
include pgxntool/base.mk
These are the make targets that are provided by base.mk
|
Note
|
all the targets normally provided by Postgres PGXS still work. |
This will build any .html files that can be created. See [_Document_Handling].
Runs unit tests via the PGXS installcheck target. Unlike a simple make installcheck though, the test rule has the following prerequisites: clean testdeps install installcheck. All of those are PGXS rules, except for testdeps.
|
Note
|
While you can still run make installcheck or any other valid PGXS make target directly, it’s recommended to use make test when using pgxntool. The test target ensures clean builds, proper test isolation, and correct dependency installation.
|
Validates that extension SQL files are syntactically correct before running the full test suite. This feature runs SQL files from test/build/ through pg_regress, providing better error messages than CREATE EXTENSION failures when there are syntax errors in your extension code.
How it works:
-
Place SQL files in
test/build/*.sql -
Place expected output in
test/build/expected/*.out -
These files run through
pg_regressbeforemake testruns the main test suite -
If any build test fails, the test run stops immediately with clear error messages
Directory structure:
test/build/
├── *.sql # SQL test files (checked in)
├── expected/ # Expected output files (checked in)
│ └── *.out
└── sql/ # GENERATED - do not edit or check in
└── *.sql # Synced from *.sql above
The sql/ subdirectory is generated automatically by make test-build. It is listed in .gitignore and removed by make clean. Do not place files directly in test/build/sql/.
Configuration:
The feature auto-detects based on whether test/build/*.sql files exist:
-
Files present → feature enabled automatically
-
No files → feature disabled (no impact on existing projects)
You can override auto-detection by setting PGXNTOOL_ENABLE_TEST_BUILD:
# In your Makefile PGXNTOOL_ENABLE_TEST_BUILD = yes # or no
Example: Validate extension SQL compiles
Create test/build/build.sql to run your extension’s SQL directly:
\set ECHO none -- Sets ON_ERROR_STOP, VERBOSITY verbose, and ON_ERROR_ROLLBACK \i test/pgxntool/psql.sql -- Suppress column headers and row counts for cleaner expected output \t BEGIN; SET client_min_messages = WARNING; -- Install dependencies your extension requires CREATE EXTENSION IF NOT EXISTS pgtap CASCADE; -- Clean slate DROP EXTENSION IF EXISTS myext; DROP SCHEMA IF EXISTS myext; CREATE SCHEMA myext; -- Run the actual extension SQL (not CREATE EXTENSION) -- psql.sql above ensures errors abort immediately with clear messages \i sql/myext.sql -- If we get here, the build succeeded; ON_ERROR_STOP would have aborted on any error above \echo # BUILD TEST SUCCEEDED ROLLBACK;
This approach catches SQL syntax errors before running CREATE EXTENSION, giving clearer error messages with line numbers. The ROLLBACK ensures nothing persists—this is purely validation.
Why use \i instead of CREATE EXTENSION?
When CREATE EXTENSION fails, PostgreSQL shows only "syntax error" with limited context. Running the SQL directly via \i shows the exact line and position of errors, making debugging much faster.
Runs setup files before the main test suite within the same pg_regress invocation. This allows expensive one-time operations (like extension installation) to set up state that persists into the regular test files.
How it works:
-
Place SQL files in
test/install/*.sql -
Place expected output alongside as
test/install/*.out -
A schedule file is auto-generated that lists install files with
../install/relative paths -
pg_regressprocesses the install schedule first, then runs regular test files — all in one invocation, so database state persists
Directory structure:
test/install/ ├── *.sql # SQL setup files (checked in) ├── *.out # Expected output (checked in, alongside .sql) ├── .gitignore # Ignores pg_regress artifacts (*.out.diff) └── schedule # GENERATED - auto-created by make
The schedule file is generated automatically and listed in .gitignore. Do not edit it.
Configuration:
The feature auto-detects based on whether test/install/*.sql files exist:
-
Files present → feature enabled automatically
-
No files → feature disabled (no impact on existing projects)
You can override auto-detection by setting PGXNTOOL_ENABLE_TEST_INSTALL:
# In your Makefile PGXNTOOL_ENABLE_TEST_INSTALL = yes # or no
Why this is useful:
Without test/install, each test file typically needs to run CREATE EXTENSION in its setup, which adds overhead and doesn’t allow validating the installation step separately. With test/install, setup runs once before all tests, and any state it creates (tables, extensions, etc.) is available to every subsequent test file.
Key detail: Install files and regular tests run in a single pg_regress invocation. This means the database is NOT dropped between install and test phases — state created by install files persists into the main test suite.
Beyond validating a plain install, it’s worth testing that your extension behaves correctly across the two transitions every extension with more than one release eventually goes through:
-
Update:
ALTER EXTENSION ext UPDATE, moving to a newer extension version on the same PostgreSQL version. -
Upgrade:
pg_upgrade, moving to a newer PostgreSQL major version while carrying the extension’s on-disk catalog state along with it.
Both catch bugs that a simple "did the script run without error" check misses — wrong mappings, missing `REVOKE`s, objects that drifted from the base SQL, or catalog state that doesn’t survive a binary upgrade cleanly. This isn’t a niche concern that only applies if your extension does something unusual (adds enum types, custom operators, etc.) — it applies to any extension that will ever be updated or upgraded in place, which in practice is every extension.
test/install is the recommended place to build this, because its "load once, committed, before the suite" mechanic (described above) is exactly what update/upgrade testing needs.
The pattern: load the extension exactly once, committed, from a test/install/*.sql file, in one of several modes selected by a custom backend setting that your Makefile sets via PGOPTIONS (e.g. -c myext.test_load_mode=$(TEST_LOAD_SOURCE)) and that test/install reads with current_setting('myext.test_load_mode'):
-
fresh:
CREATE EXTENSION ext; -
update:
CREATE EXTENSION ext VERSION 'oldest_supported';thenALTER EXTENSION ext UPDATE;(to the currentdefault_version, or an explicit intermediate version) -
existing: the extension is already installed — by a real
pg_upgrade, or an update performed outside the suite — andtest/installonly asserts it’s present at the expected version, without dropping or touching it
The same test/sql/ suite and the *same test/expected/ output then run unchanged against every mode. Identical expected output passing in *update mode is the update-equivalence assertion — if the updated database behaves any differently than a fresh install, some test will diff. Running that same suite in existing mode against a database that just went through a real pg_upgrade gives you upgrade testing using the same test files, no separate suite required.
If you adopt this pattern, test/deps.sql should no longer run CREATE EXTENSION itself for the per-test setup — the committed install performed by test/install persists into every (rolled-back) test file, exactly as described above.
Why the install step must be committed, not run per-test in test/deps.sql: every test/sql/ file runs inside BEGIN; … ROLLBACK; — a pgxntool convention (test/pgxntool/setup.sql opens the transaction; since it’s never committed, it rolls back when the session ends). Running the update inside that transaction means the suite never actually exercises a committed update — which doesn’t match production, where ALTER EXTENSION UPDATE commits before anything else touches the database, and it hides bugs that only show up once the update is durably committed. (Modifying an enum is one example of how this can turn into an outright failure rather than just a hidden bug — but it’s just an example; the reason to commit first holds regardless of what the update script does.)
As a bonus, sharing one committed install across every test file also removes the per-test re-install cost.
|
Note
|
This pattern isn’t (yet) native to pgxntool — you wire up the mode selection and PGOPTIONS plumbing yourself in test/install and your Makefile. First-class support (a standard mode-switching toggle, and scaffolding for real pg_upgrade testing) is planned; see issue #42.
|
Working examples (specific files, not full PRs — both extensions' histories include plenty of unrelated changes):
-
cat_tools
test/install/load.sqland theTEST_LOAD_SOURCEblock in itsMakefile— the fresh/update/existing pattern above, including thePGOPTIONSwiring. This is a reasonably complete example, and somewhat more elaborate than the minimum needed to get started. -
pg_count_nulls
test/sql/extension_tests.sqladditionally demonstrates testing an extension installed into a non-default schema. It’s a useful reference for that specific problem, but it also defines its assertions as plpgsql functions rather than plain pgTAP calls — a pattern that adds complexity of its own and isn’t recommended as a model for U&U testing itself.
This rule allows you to ensure certain actions have taken place before running tests. By default it has a single prerequisite, pgtap, which will attempt to install pgtap from PGXN. This depneds on having the pgxn client installed.
You can add any other dependencies you want by simply adding another testdeps rule. For example:
testdeps example from test_factory
testdeps: check_control .PHONY: check_control check_control: grep -q "requires = 'pgtap, test_factory'" test_factory_pgtap.control
If you want to over-ride the default dependency on pgtap you should be able to do that with a makefile override. If you need help with that, please open an issue.
|
Warning
|
It will probably cause problems if you try to create a testdeps rule that has a recipe. Instead of doing that, put the recipe in a separate rule and make that rule a prerequisite of testdeps as show in the example.
|
Because make test ultimately runs installcheck, it’s using the Postgres test suite. Unfortunately, that suite is based on running diff between a raw output file and expected results. I STRONGLY recommend you use pgTap instead! With pgTap, it’s MUCH easier to determine whether a test is passing or not - tests explicitly pass or fail rather than requiring you to examine diff output. The extra effort of learning pgTap will quickly pay for itself. This example might help get you started.
No matter what method you use, once you know that all your tests are passing correctly, you need to create or update the test output expected files. make results does that for you.
|
Important
|
make results requires manual verification first. The correct workflow is:
|
-
Run
make testand examine the diff output -
Manually verify that the differences are correct and expected
-
Only then run
make resultsto update the expected output files intest/expected/
Never run make results without first verifying the test changes are correct. The results target copies files from test/results/ to test/expected/, so running it blindly will make incorrect output become the new expected behavior.
By default, make results will refuse to run if test/results/regression.diffs exists (indicating failing tests). This prevents accidentally updating expected files with incorrect output.
If tests are failing, you’ll see:
ERROR: Tests are failing. Cannot run 'make results'. Fix test failures first, then run 'make results'.
To disable this safeguard (not recommended):
# In your Makefile PGXNTOOL_ENABLE_VERIFY_RESULTS = no # Or on the command line make PGXNTOOL_ENABLE_VERIFY_RESULTS=no results
make tag will create an annotated git tag for the current version of your extension, as determined by the META.json file (generated from META.in.json — see [_pgxn_distributions_vs_extensions]), and push it to origin. The reason to do this is so you can always refer to the exact code that went into a released version.
If there’s already a tag for the current version that probably means you forgot to update META.json, so you’ll get an error. If you’re certain you want to over-write the tag, you can do make forcetag, which removes the existing tag (via make rmtag) and creates a new one.
|
Warning
|
You will be very unhappy if you forget to update the .control file for your extension! There is an open issue to improve this. |
make dist will create a .zip file for your current version that you can upload to PGXN. It first runs tag (creating/pushing the version’s git tag as described above if needed), then uses git archive at that tag to build the .zip file, so the archive always matches the exact tagged commit. The file is named after the PGXN name and version (the top-level "name" and "version" attributes in META.json, generated from META.in.json). The .zip file is placed in the parent directory so as not to clutter up your git repo.
|
Note
|
Part of the clean recipe is cleaning up these .zip files. If you accidentally clean before uploading, just run make dist-only.
|
This rule will pull down the latest released version of PGXNtool via git subtree pull and then reconcile the files setup.sh copied into your project (.gitignore, test/deps.sql) with a 3-way merge.
|
Note
|
Your repository must be clean (no modified files) in order to run this. Running this command will produce a git commit of the merge. |
|
Tip
|
The actual work is done by pgxntool/pgxntool-sync.sh, so you can run it directly (pgxntool/pgxntool-sync.sh) if you’d rather not go through make. It optionally takes <repo> and <ref> arguments to pull from somewhere other than the default.
|
|
Tip
|
There is also a pgxntool-sync-% rule if you need to do more advanced things. make pgxntool-sync-<name> pulls from the <repo> <ref> defined by the pgxntool-sync-<name> make variable.
|
make distclean removes generated configuration files (META.json — generated from META.in.json — plus meta.mk and control.mk) that survive a normal make clean.
|
Note
|
PGXS doesn’t provide any special support for distclean — its built-in distclean target simply depends on clean. PGXNtool uses its own PGXNTOOL_distclean variable to track files that should only be removed by distclean, not clean.
|
If your extension generates additional files that should be removed by distclean but not clean, you can add them:
PGXNTOOL_distclean += my_generated_config.mk
Generates pg_tle (Trusted Language Extensions) registration SQL files for deploying extensions in managed environments like AWS RDS/Aurora. See [_pg_tle_Support] for complete documentation.
make pgtle generates SQL files in pg_tle/ subdirectories organized by pg_tle version ranges. For version range details, see pgtle_versions.md.
Checks if pg_tle is installed and reports the version. This target:
- Reports the version from pg_extension if CREATE EXTENSION pg_tle has been run in the database
- Errors if pg_tle is not available in the cluster
This target assumes PG* environment variables are configured for psql connectivity.
make check-pgtle
Registers all extensions with pg_tle by executing the generated pg_tle registration SQL files in a PostgreSQL database. This target:
- Requires pg_tle extension to be installed (checked via check-pgtle)
- Uses pgtle.sh to determine which version range directory to use based on the installed pg_tle version
- Runs all generated SQL files via psql to register your extensions with pg_tle
This target assumes that running psql without any arguments will connect to the desired database. You can control this by setting the various PG* environment variables (and possibly using the .pgpassword file). See the PostgreSQL documentation for more details.
|
Note
|
The pgtle target is a dependency, so make run-pgtle will automatically generate the SQL files if needed.
|
make run-pgtle
After running make run-pgtle, you can create your extension in the database:
CREATE EXTENSION "your-extension-name";
PGXNtool automatically generates version-specific SQL files from your base SQL file. These files follow the pattern sql/{extension}--{version}.sql and are used by PostgreSQL’s extension system to install specific versions of your extension.
PGXN distinguishes two things it’s easy to conflate: a distribution and an extension. Per the PGXN meta spec, a distribution is "a collection of extensions, source code, utilities, tests, and/or documents that are distributed together" — the thing you release and upload to PGXN, identified by the top-level name/version in META.json (generated from META.in.json — you edit the latter, never META.json directly). An extension is the individual thing installed via CREATE EXTENSION, described by its own .control file. A single distribution routinely provides more than one extension — META.in.json’s `provides map lists each one; the pgTAP distribution, for example, provides both the pgtap and schematap extensions from one release.
This split matters for versioning, and it’s where pgxntool has a real gap: META.json’s top-level `version is the distribution’s release version (used by make tag and make dist — it’s the git tag name and the .zip filename). Each extension’s own version, though, ends up tracked in two separate places that pgxntool does not keep in sync for you:
-
the extension’s
.controlfile (default_version) — what PostgreSQL and pgxntool’s own build actually use; see [_what_controls_the_version_number] below -
that extension’s entry under
META.in.json’s `providesmap (provides.{extension}.version) — which the PGXN meta spec defines as "a Version for the extension" in its own right, not the distribution’s version, and which is what PGXN’s public index reads
Nothing generates one from the other or checks that they still agree, so it’s easy to bump one and forget the other; issue #47 tracks fixing that.
When you run make (or make all), PGXNtool:
-
Reads each extension’s own
.controlfile to determine its version fromdefault_version(viacontrol.mk.sh) -
Generates a Makefile rule that copies your base SQL file (
sql/{extension}.sql) to the version-specific file (sql/{extension}--{version}.sql) -
Executes this rule, creating the version-specific file with a header comment indicating it’s auto-generated
For example, if your myext.control contains:
default_version = '1.2.3'
Running make will create sql/myext—1.2.3.sql by copying sql/myext.sql.
The version number comes from each extension’s own .control file → default_version, not from META.json. PostgreSQL itself uses default_version to pick which versioned SQL file to load, so control.mk.sh parses the .control file(s) directly to keep the generated filename in sync with what PostgreSQL will actually use. META.in.json has its own, separate provides.{extension}.version for that same extension (see [_pgxn_distributions_vs_extensions] above) — pgxntool never reads it when generating SQL files, and nothing keeps it in sync with the .control file’s default_version.
To change the version of one of your extensions:
1. Update default_version in that extension’s .control file
2. Run make to regenerate the version-specific file
3. Update that extension’s provides.{extension}.version in META.in.json to match by hand, and regenerate META.json (make META.json) — pgxntool won’t do this for you
4. Update META.in.json’s top-level `version too if you’re also cutting a new distribution release
Version-specific SQL files are treated as permanent files that should be committed to your repository by default. This makes it much easier to test updates to extensions, as you can see exactly what SQL was included in each version.
|
Important
|
These files are auto-generated and include a header comment warning not to edit them. Any manual changes will be overwritten the next time you run make. To modify the extension, edit the base SQL file (sql/{extension}.sql) instead.
|
The primary value of committing a version’s install script is update testing: with the file in place, you can install that exact version (CREATE EXTENSION ext VERSION 'x.y.z'), run ALTER EXTENSION ext UPDATE, and verify the update path actually works. In principle you would only ever need the very first committed version’s install script — every later version is reachable by chaining upgrade scripts from it. See [_testinstall] for how to wire this into your test suite.
In practice, you should keep most old versions tracked rather than relying purely on that chain, because you cannot predict when a new major PostgreSQL version will break the ability to install an older extension version — a system catalog column changes type, a SELECT * over a catalog starts failing, and so on. Committing the old version’s install script lets CI catch that regression directly, by attempting to install that exact historical version against the new PostgreSQL release. For any non-trivial extension, most old versions should stay tracked for this reason alone.
For a minor version change that doesn’t meaningfully alter the extension (e.g. a small bug fix), it’s unlikely to straddle a PostgreSQL supported-version boundary, so there’s much less test-coverage value in committing that specific version’s generated install script. For large extensions, deliberately not committing some of these individual version files is worth doing to keep repository size down — the base sql/{extension}.sql still fully captures the change in git history, and the install script itself is trivially regenerated from that base file at build time.
When you decide not to track a particular version’s generated install script, do not add a .gitignore entry for it.
make only ever generates the current version’s file, stamped from the base source according to that extension’s .control file default_version. The moment you bump the version, make starts generating the new current version’s file and stops touching the old one — the old file left behind in your working tree is a stale, one-off artifact, not something make keeps recreating on every build.
Because it’s only ever transiently present, the correct cleanup is a single rm sql/{extension}--{old-version}.sql right after the version bump — not a permanent .gitignore rule. A per-version .gitignore line would be perpetual clutter added on every release, for a file that’s only ever present for one build cycle. A broad glob (e.g. sql/--.sql) is wrong for the same reason it’s wrong below in Alternative: Ignoring All Version Files: it would also hide the prior-version install scripts and upgrade scripts (sql/{extension}--{a}--{b}.sql) that you do want tracked and visible in git status.
control.mk.sh generates a Make rule along these lines for the current version’s file:
$(EXTENSION_ext_VERSION_FILE): sql/ext.sql extension.control @echo '/* DO NOT EDIT - AUTO-GENERATED FILE */' > $(EXTENSION_ext_VERSION_FILE) @cat sql/ext.sql >> $(EXTENSION_ext_VERSION_FILE)
That rule only ever targets the file matching the extension’s current default_version, so editing the base sql/{extension}.sql and running make is the correct, safe way to change that one file.
Every other versioned file — any sql/{extension}--{version}.sql where {version} is no longer current — is a frozen historical record, not something make will ever regenerate or overwrite again. Its entire purpose is to preserve exactly what shipped in that version (see [_why_commit_them_update_testing] above), so it can keep being used to test update paths and PostgreSQL-version compatibility against exactly what your users actually installed.
Hand-editing an old versioned file directly is never correct — it silently corrupts that historical record. If you need to change behavior after a version has already shipped, bump the version and add a proper sql/{extension}--{old}--{new}.sql upgrade script instead. Never edit sql/{extension}--{old}.sql in place.
|
Caution
|
This is especially relevant for AI coding agents, which won’t know this convention exists unless it’s spelled out. The generic DO NOT EDIT - AUTO-GENERATED FILE header on every version file doesn’t distinguish "regenerated on every build" (the current version) from "was generated once, now frozen" (every other version). An agent without this context may reasonably — but incorrectly — treat any auto-generated file as fair game to patch directly. Old versioned SQL files must instead be treated as append-only history: the fix is always a new version plus an upgrade script, never an in-place edit.
|
|
Note
|
This is a different, all-or-nothing choice from [_when_its_ok_to_skip_a_version] above, where the recommendation is to keep tracking most versions and selectively skip only a few low-value minor ones. This section instead covers not tracking any version-specific files at all. |
If you prefer not to commit version-specific SQL files, you must add them to your .gitignore to prevent make dist from failing due to untracked files. Add the following to your .gitignore:
# Auto-generated version-specific SQL files (if not committing them) sql/*--*.sql !sql/*--*--*.sql
The second line (!sql/----*.sql) ensures that upgrade scripts (which contain two version numbers and should be manually written) are still tracked.
|
Warning
|
If you ignore version files instead of committing them, they will NOT be included in your PGXN distribution (make dist uses git archive, which only includes tracked files). This means users installing your extension from PGXN will need make and PGXS available to build the extension - they cannot simply copy the SQL files into their PostgreSQL installation. For maximum compatibility, we recommend committing version files.
|
Version-specific files are included in distributions created by make dist only if they are committed to git. Since make dist uses git archive, only tracked files are included in the distribution archive.
If you need to support multiple versions of your extension:
-
Create additional version-specific files manually (e.g.,
sql/myext—1.0.0.sql,sql/myext—1.1.0.sql) -
Create upgrade scripts for version transitions (e.g.,
sql/myext—1.0.0—1.1.0.sql) -
Update
default_versionin the extension’s.controlfile to reflect the current version you’re working on -
Commit all version files and upgrade scripts to your repository
The version file for the current version (specified in the .control file’s default_version) will be automatically regenerated when you run make, but other version files you create manually will be preserved.
PGXNtool supports generation and installation of document files. There are several variables and rules that control this behavior.
It is recommended that you commit any generated documentation files (such as HTML generated from Asciidoc) into git. That way users will have these files installed when they install your extension. If any generated files are missing (or out-of-date) during installation, PGXNtool will build them if Asciidoc is present on the system.
- DOC_DIRS
-
Directories to look for documents in. Defined as
+= doc. - DOCS
-
PGXS variable. See [_the_docs_variable] below.
- DOCS_HTML
-
Document HTML files. PGXNtool appends `$(ASCIIDOC_HTML) to this variable.
- ASCIIDOC
-
Location of
asciidocor equivalent executable. If not set PGXNtool will search for firstasciidoctor, thenasciidoc. - ASCIIDOC_EXTS
-
File extensions to consider as Asciidoc. Defined as
+= adoc asciidoc asc. - ASCIIDOC_FILES
-
Asciidoc input files. PGXNtool searches each
$(DOC_DIRS)directory, looking for files with any$(ASCIIDOC_EXTS)extension. Any files found are added toASCIIDOC_FILESusing+=. - ASCIIDOC_FLAGS
-
Additional flags to pass to Asciidoc.
- ASCIIDOC_HTML
-
PGXNtool replaces each
$(ASCIIDOC_EXTS)in$(ASCIIDOC_FILES)withhtml. The result is appended toASCIIDOC_HTMLusing+=.
If Asciidoc is found (or $(ASCIIDOC) is set), the html rule will be added as a prerequisite to the install and installchec rules.
That will ensure that docs are generated for install and test, but only if Asciidoc is available.
The dist rule will always depend on html though, to ensure html files are up-to-date before creating a distribution.
The html rule simply depends on `$(ASCIIDOC_HTML).
This rule is always present.
For each Asciidoc extension in $(ASCIIDOC_EXTS) a rule is generated to build a .html file from that extension using $(ASCIIDOC).
These rules are generated from ASCIIDOC_template:
define ASCIIDOC_template
%.html: %.$(1) # (1)
ifndef ASCIIDOC
$$(warning Could not find "asciidoc" or "asciidoctor". Add one of them to your PATH,)
$$(warning or set ASCIIDOC to the correct location.)
$$(error Could not build %$$@)
endif # ifndef ASCIIDOC
$$(ASCIIDOC) $$(ASCIIDOC_FLAGS) $$<
endef # define ASCIIDOC_template-
$(1)is replaced by the extension.
These rules will always exist, even if $(ASCIIDOC) isn’t set (ie: if Asciidoc wasn’t found on the system).
These rules will throw an error if they are run if $(ASCIIDOC) isn’t defined.
On a normal user system that should never happen, because the html rule won’t be included in install or installcheck.
This variable has special meaning to PGXS. See the Postgres documentation for full details.
If DOCS is defined when PGXS is included then rules will be added to install everything defined by $(DOCS) in PREFIX/share/doc/extension.
|
Note
|
If DOCS is defined but empty some of the PGXS targets will error out.
Because of this, base.mk will forcibly define it to be NULL if it’s empty.
|
PGXNtool appends all files found in all $(DOC_DIRS) to DOCS.
pgxntool can generate pg_tle (Trusted Language Extensions) registration SQL for deploying PostgreSQL extensions in managed environments like AWS RDS and Aurora where filesystem access is not available.
For make targets, see: [_pgtle], [_check_pgtle], [_run_pgtle].
pg_tle is an AWS open-source framework that enables developers to create and deploy PostgreSQL extensions without filesystem access. Traditional PostgreSQL extensions require .control and .sql files on the filesystem, which isn’t possible in managed services like RDS and Aurora.
pg_tle solves this by:
- Storing extension metadata and SQL in database tables
- Using the pgtle_admin role for administrative operations
- Enabling CREATE EXTENSION to work in managed environments
Generate pg_tle registration SQL for your extension:
make pgtle
This creates files in pg_tle/ subdirectories organized by pg_tle version ranges. See pgtle_versions.md for complete version range details and API compatibility boundaries.
pgxntool creates different sets of files for different pg_tle versions to handle backward-incompatible API changes. Each version boundary represents a change to pg_tle’s API functions that we use.
For details on version boundaries and API changes, see pgtle_versions.md.
|
Important
|
This is only a basic example. Always refer to the main pg_tle documentation for complete installation instructions and best practices. |
Basic installation steps:
-
Ensure pg_tle is installed and grant the
pgtle_adminrole to your user -
Generate and run the pg_tle registration SQL files:
make run-pgtle
This automatically detects your pg_tle version and runs the appropriate SQL files. See
pgtle_versions.mdfor version range details. -
Create your extension:
CREATE EXTENSION myextension;
If your project has multiple extensions (multiple .control files), make pgtle generates files for all of them:
myproject/
├── ext1.control
├── ext2.control
└── pg_tle/
├── 1.0.0-1.5.0/
│ ├── ext1.sql
│ └── ext2.sql
└── 1.5.0+/
├── ext1.sql
└── ext2.sql
make pgtle does the following:
-
Parses control file(s): Extracts
comment,default_version,requires, andschemafields -
Discovers SQL files: Finds all versioned files (
sql/{ext}--{version}.sql) and upgrade scripts (sql/{ext}--{ver1}--{ver2}.sql) -
Wraps SQL content: Uses a fixed dollar-quote delimiter (
$pgtle_wrap_delimiter$) to wrap SQL for pg_tle functions -
Generates registration SQL: Creates
pgtle.install_extension()calls for each version,pgtle.install_update_path()for upgrades, andpgtle.set_default_version()for the default -
Version-specific output: Generates separate files for different pg_tle capability levels
Each generated SQL file is wrapped in a transaction (BEGIN; … COMMIT;) to ensure atomic installation.
Problem: The script can’t find sql/{ext}--{version}.sql files.
Solution: Run make first to generate versioned files from your base sql/{ext}.sql file.
Problem: The script can’t find {ext}.control in the current directory.
Solution: Run make pgtle from your extension’s root directory (where the .control file is).
Problem: Your SQL files contain the string $pgtle_wrap_delimiter$ (extremely unlikely).
Solution: Don’t use that dollar-quote delimiter in your code.
Problem: Your control file has module_pathname, indicating C code.
Solution: pg_tle only supports trusted languages. You cannot use C extensions with pg_tle. The script will warn you but still generate files (which won’t work).
|
Note
|
there are several untrusted languages (such as plpython), and the only tests for C. |
This is the comprehensive list of make variables you can override to customize pgxntool’s behavior, either in your Makefile (before include pgxntool/base.mk) or on the make command line. Several of these are also covered in more detail in the sections above; this section exists so there’s one place that lists all of them.
|
Note
|
Variables marked with * must be set to exactly yes or no (case-insensitive) when given explicitly; any other value is a hard make error.
|
Default: origin. The git remote used by tag, rmtag, forcetag, and dist (which depends on tag). If your origin is a personal fork rather than the canonical repository — for example, if you develop on a fork and only push release tags to the canonical repo — set this so tagging and pushing target the right remote:
make dist PGXN_REMOTE=upstream
Default: auto-detected, the first of asciidoctor or asciidoc found on PATH. Path to the Asciidoc processor used to build .html files from $(ASCIIDOC_EXTS) source files. Override if the processor you want isn’t first on PATH, or isn’t on PATH at all. See [_document_handling].
Default: pg_config. Path to the pg_config binary used to detect the PostgreSQL version and locate PGXS. Override when the right pg_config isn’t the one on PATH, such as when testing against a specific PostgreSQL install.
Default: test. Root directory for test input files: $(TESTDIR)/sql/, $(TESTDIR)/expected/, $(TESTDIR)/build/, $(TESTDIR)/install/. Overriding this on its own is unusual; it mainly exists so TESTOUT has a sensible default.
Default: $(TESTDIR). Directory pg_regress writes actual test output to — $(TESTOUT)/results/, $(TESTOUT)/regression.diffs, etc. — via --outputdir in REGRESS_OPTS. Kept separate from TESTDIR so generated output can be pointed somewhere other than the directory holding your checked-in sql/expected files, if you want that separation.
Default: pgtap. Controls how the verify-results safeguard decides whether tests are failing.
Scans test/results/*.out for pgTap not ok lines and plan mismatches, falling back to checking for regression.diffs too. Use this mode when your test suite uses pgTap.
Checks only for regression.diffs, matching classic pg_regress behavior. Use this mode when your tests use plain SQL expected-output comparison only.
Default: auto-detected — yes if test/build/*.sql files exist, no otherwise. Enables or disables the test-build pre-flight SQL check. Set explicitly to yes if you want an error when test/build/ unexpectedly has no SQL files (catches accidental deletion of its contents), or to no to disable the check even when files are present.
Default: auto-detected — yes if test/install/*.sql files exist, no otherwise. Enables or disables the test/install schedule-based setup feature. Same explicit-override semantics as PGXNTOOL_ENABLE_TEST_BUILD.
Default: yes. Enables or disables the verify-results safeguard that blocks make results when tests are failing. Setting it to empty on the command line (make PGXNTOOL_ENABLE_VERIFY_RESULTS= results) also disables it.
Default: yes. Enables or disables the check-stale-expected safeguard, which fails make test if test/expected/ (or test/build/expected/) contains a .out file with no corresponding .sql file — catching a stale file left behind after a test was renamed or removed. Set to no to make the check a complete no-op (it’s dropped from TEST_DEPS entirely).
This is also the supported pattern for disabling one specific optional pgxntool feature: a documented PGXNTOOL_ENABLE_* variable, gating both the target’s registration into TEST_DEPS/etc. and (where applicable) the target’s own definition. Cleanly replacing or overriding an arbitrary pgxntool-generated recipe with your own entirely is a separate, larger, and not-yet-decided design question — see issue #30 (two proposed approaches) — and is not what this mechanism solves.
Default: yes. Sub-check of check-stale-expected, independent of PGXNTOOL_ENABLE_CHECK_STALE_EXPECTED: fails (with a distinct error message and exit code from the orphaned-.out check) if test/expected/ (or test/build/expected/) contains any file that isn’t *.out. Set to no to disable just this sub-check while leaving the orphaned-.out check active.
Default: unset (PGXS is included normally). Skips including PGXS ($(PGXS)) entirely. This is only for advanced scenarios where you need to manage the PGXS include yourself; most projects should never set this.
Copyright (c) 2026 Jim Nasby <Jim.Nasby@gmail.com>
PGXNtool is released under a BSD license. Note that it includes JSON.sh, which is released under a MIT license.