Consider adding pg_partman extension

[Jean-Daniel]

As Couldnative PG supports declarative Tablespace (since 1.22), it would be extremely useful to get pg_partman available in the PostgreSQL image.

To quote Cloudnative PG documentation:

In the context of the database industry, tablespaces play a strategic role, particularly when paired with table partitioning, a logical database modeling technique.

While it is perfectly possible to use table partitioning without pg_partman, it is widely used to managed table partitions.

pg_partman is readily available in the Debian repository, so it can easily be included in the built images.

[ardentperf]

Note that adding pg_partman would be a one-liner change, so you can easily fork the repo and create your own image with pg_partman included (I've done this with pg_hint_plan which is also readily available in the PGDG Debian repo)

Probably need some thought about how CNPG is going to handle this particular repo, since there could be a lot of potential extension requests. It might make sense to keep this repo smaller but for us to spend some time on instructions to build custom extensions. OTOH if extensions don't add many bytes, then adding them here benefits a lot of people. A few extensions have already been added here (like pgvector & pgaudit), and it might make sense to add a few more widely used ones which are already in the debian repos (eg. pg_hint_plan, pg_cron, pg_repack, pg_squeeze, pg_partman, pldebugger, plprofiler, FDWs, wal2json, maybe pg_stat_kcache)

---

In the meantime, if you want to just create your own images, here are the steps which worked for me:

1. fork github repo - keep same name "postgres-containers"
   1. copy only the main branch from upstream
   2. after forking, rename your default branch to something other than main - for example you can call it testing-pg-hint-plan.
      1. i think the build scripts use the branch name to determine some behaviors, like where to publish containers in your github account and whether to allow rebuilds if artifacts already exist. if the branch is named main then containers are not rebuilt without a version number change, and they are published to the package named postgresql. with other branch names, containers are published to the package named postgresql-testing. i myself am using testing branches at the moment in my personal github repo.
2. create a fine-grained personal access token 1. name "postgres-containers-gha-pat"
   2. only select the "postgres-containers" repo
   3. allow "read and write" access to Administration and Contents
   4. allow "read-only" access to Metadata and Secrets
   5. save the token!
3. auth github cli with gh auth
4. create secret with gh secret set REPO_GHA_PAT and paste the personal access token that was created above
5. enable github actions for the forked repo
6. manually run the workflow named Continuous Delivery - it should succeed
   1. confirm that packages have been published in github container registry via your own repo with docker images for the latest minor releases of all supported Postgres major versions
7. pull a docker image from github container registry - eg. docker pull ghcr.io/ardentperf/postgresql:latest

test a local build

1. clone my github forked repo down to my local machine: git clone https://github.com/ardentperf/postgres-containers
2. cd postgres-containers/Debian/16/bookworm
3. docker build -t pgtest1
4. docker run -de POSTGRES_PASSWORD=jeremy pgtest1

install pip-tools (we will need the pip-compile utility)

1. pip install pip-tools

add a simple extension from the PGDG apt repo

1. cd postgres-containers/Debian
2. nvim Dockerfile.template
3. add the final line (pg-hint-plan) below:

apt-get install -y --no-install-recommends \
                "postgresql-${PG_MAJOR}-pgaudit" \
                "postgresql-${PG_MAJOR}-pgvector" \
                "postgresql-${PG_MAJOR}-pg-failover-slots" \
                "postgresql-${PG_MAJOR}-pg-hint-plan" \
        ; \

4. run ./update.sh
5. run git diff and confirm that Dockerfile has been updated in each subdirectory for all supported Postgres major versions, and for both supported Debian distros (stable - currently bookworm, oldstable - currently bullseye)
6. choose a version to test: cd 16/bookworm
7. build with the updated Dockerfile and run the image to test locally
   1. docker build -t pgtest2
   2. docker run -e POSTGRES_PASSWORD=jeremy pgtest2
      1. add -d if you want to run detached
   3. use psql to connect to the database
   4. select * from pg_available_extensions
   5. create extension pg_hint_plan
      After confirming that your local test succeeded, push only the Dockerfile.template change back to your github fork and let CI build and publish your images in the github container registry.
8. git add postgres-containers/Debian/Dockerfile.template
9. git commit -m "adding my extension"
10. git push
11. in the github UI, go to Actions and manually trigger the workflow named Automatic Updates - this will run update.sh
    1. i disabled this scheduled action after i finished creating my images; i don't need it running daily when i'm not actively working on the images anymore
12. in the github UI, go to Actions and manually trigger the workflow named Continuous Delivery - this will run a build and publish the new containers to the github container registry

Test running the container from the registry

1. docker pull ghcr.io/ardentperf/postgresql-testing:17
2. docker run -e POSTGRES_PASSWORD=jeremy ghcr.io/ardentperf/postgresql-testing:17
   1. if you need to modify any postgres parameters, add -c parameter to the end of the run command. for example: docker run -e POSTGRES_PASSWORD=jeremy ghcr.io/ardentperf/postgresql-testing:17 -c shared_preload_libraries='pg_stat_statements,pg_hint_plan'

---

this was cobbled together from some notes and might have mistakes... but hopefully it helps. :)

[Jean-Daniel]

Thank you very much for these very detailed instructions :-)

[dosubot]

Hi, @Jean-Daniel. I'm Dosu, and I'm helping the postgres-containers team manage their backlog. I'm marking this issue as stale.

Issue Summary:

• You proposed adding the pg_partman extension to the PostgreSQL image for better use of declarative tablespaces.
• Ardentperf provided detailed instructions on creating a custom image with the extension.
• You expressed gratitude for the guidance and discussed the potential for handling multiple extension requests.

Next Steps:

• Please let me know if this issue is still relevant to the latest version of the postgres-containers repository by commenting here.
• If there is no further activity, this issue will be automatically closed in 7 days.

Thank you for your understanding and contribution!