The saga of migrating from an existing deployment from pgstac:v0.9.1 to pgstac:v0.9.2 - Chapter I.

[zbenta]

Hi guys, buckle up because this will be a long one.

We have deployed eoAPI with pgstac:v0.9.1 and it works perfectly. Now that our deployment is solid and we validated that it is indeed what we need, want to migrate the pgstac to the latest version.

The first idea we had was just to update the version of the image from image: ghcr.io/stac-utils/pgstac:v0.9.1 to image: ghcr.io/stac-utils/pgstac:v0.9.2. But it turns out it is not possible since the posgres version jumps from 15 on pgstac:v0.9.1 to 17 pgstac:v0.9.2 and the database engine states it can not start due to incompatibilties between versions.

database-1-1  | 2025-11-21 15:37:25.803 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 15, which is not compatible with this version 17.2 (Debian 17.2-1.pgdg110+1).

The next idea was to use pypgstac to migrate between versions and then alter the image version to pgstac:v0.9.1, but we ran accross the same issue of the inconpatibilities betwen database versions.

Our latest adventure was to try the approach of doing a dump of the current database, run a side container with the newest version and then restore the backup using the pypgstac tool to migrate and "Bob's your uncle.".
Certainly this would to the job right? But actually it did not.
After creating a dump of the database.

docker compose exec pgstac-1 pg_dumpall --clean --if-exists --attribute-inserts  --file=/var/lib/postgresql/data/fulldump --superuser=MYSUPERPASSWORD``` 

We fired up a temporary container with the dump mapped to the /tmp dir.

docker run -it --rm --name pgstac-temp  -e POSTGRES_USER=MYSUPERUSER -e POSTGRES_PASSWORD=MYSUPERPASSWORD -e POSTGRES_DB=postgis -e PGUSER=MYSUPERUSER -e PGPASSWORD=MYSUPERPASSWORD -e PGDATABASE=postgis -v .pgdata/fulldump:/tmp/fulldump /dev/null:/docker-entrypoint-initdb./d990_pgstac.sh   -p 5439:5432   ghcr.io/stac-utils/pgstac:v0.9.2

We tried restoring the dump wich led to a series of errors.

docker exec -it pgstac-temp psql -U MYUSERNAME -d postgis -f /tmp/fulldump


DETAIL:  Key (collection)=(LAZ) is not present in table "collections".
psql:/tmp/fulldump:29777: ERROR:  insert or update on table "_items_2" violates foreign key constraint "items_collections_fk"
DETAIL:  Key (collection)=(LAZ) is not present in table "collections".
psql:/tmp/fulldump:29778: ERROR:  insert or update on table "_items_2" violates foreign key constraint "items_collections_fk"
DETAIL:  Key (collection)=(LAZ) is not present in table "collections".
^CCancel request sent
psql:/tmp/fulldump:29779: ERROR:  insert or update on table "_items_2" violates foreign key constraint "items_collections_fk"
DETAIL:  Key (collection)=(LAZ) is not present in table "collections".

So we tried backing up and restoring the collections table before executing the full database restore:

docker compose exec database-1 pg_dump --data-only --table=collections --file=/var/lib/postgresql/data/collectiondump --superuser=MYUSERNAME

Then we tried restoring the collection table and got this error:

docker exec -it pgstac-temp psql -U MYUSENAME -d postgis -f /tmp/collectiondump
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
psql:/tmp/collectiondump:41: ERROR:  function st_makeenvelope(double precision, double precision, double precision, double precision, integer) does not exist
LINE 4:         st_makeenvelope(
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  
    WITH box AS (SELECT content->'extent'->'spatial'->'bbox'->0 as box)
    SELECT
        st_makeenvelope(
            (box->>0)::float,
            (box->>1)::float,
            (box->>2)::float,
            (box->>3)::float,
            4326
        )
    FROM box;

CONTEXT:  SQL function "collection_geom" during inlining
COPY collections, line 1: "4    {"id": "MDS-50cm", "name": "MDS-50cm", "type": "Collection", "links": [{"rel": "items", "href": "h..."
 setval 
--------
     18
(1 row)

After looking around the public schema on the original container we tried copying the public schema and copy it to the temporary container before.

docker compose exec database-1 pg_dump -n public --clean --if-exists -F c --file=/var/lib/postgresql/data/publicschemadump -v
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding table check constraints
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: saving database definition
pg_dump: dumping contents of table "public.spatial_ref_sys"

Then we restored it:

docker exec -it pgstac-temp pg_restore -d postgis -n public --verbose --clean --if-exists --single-transaction /tmp/publicschemadump
pg_restore: connecting to database for restore
pg_restore: dropping FUNCTION pgstac_admin_owns()
pg_restore: creating FUNCTION "public.pgstac_admin_owns()"
pg_restore: processing data for table "public.spatial_ref_sys"

And tried restoring the public schema once again:

docker exec -it pgstac-temp psql -U MYUSERNAME -d postgis -f /tmp/collectiondump
SET
SET
SET
SET
SET
 set_config 
------------
 
(1 row)

SET
SET
SET
SET
psql:/tmp/collectiondump:41: ERROR:  function st_makeenvelope(double precision, double precision, double precision, double precision, integer) does not exist
LINE 4:         st_makeenvelope(
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  
    WITH box AS (SELECT content->'extent'->'spatial'->'bbox'->0 as box)
    SELECT
        st_makeenvelope(
            (box->>0)::float,
            (box->>1)::float,
            (box->>2)::float,
            (box->>3)::float,
            4326
        )
    FROM box;

CONTEXT:  SQL function "collection_geom" during inlining
COPY collections, line 1: "4    {"id": "MDS-50cm", "name": "MDS-50cm", "type": "Collection", "links": [{"rel": "items", "href": "h..."
 setval 
--------
     18
(1 row)

And now we are out of ideas.
Does anyone have any tips on how to do this migration.

[hrodmn]

Hi @zbenta - this is an epic tale! Can you share more about how you are deploying/running pgstac? Migrating pgstac versions by upgrading the pgstac container image is not something we typically see. If you are running on kubernetes definitely check out eoapi-k8s for a battle-tested kubernetes deployment strategy. For a (mostly) serverless AWS-based deployment, check out eoapi-cdk.

I think the closest you probably got was the pypgstac migrate route - everything looks good in the process you outlined (pypgstac migrate) until you tried upgrading the container image. Instead, just run pypgstac migrate on the running pgstac container and leave it alone after that. This would be slightly confusing because you would still have a container called pgstac:v0.9.1 but the database would have pgstac==0.9.2 installed on it after you run the migration. There is nothing wrong with this state besides the confusing container name!

Another option would be to just export the collections and items to .ndjson files (not via pgdump), spin up a new container with the desired pgstac version, then load the collections and items into the fresh database container.