Failed to detect the target database version when loading collection and items

[nikkopante]

I am trying to create/migrate and utilize a PostreSQL in a Google Cloud Platform. I want to load my STAC items and collections into the database. However, I am confronted with error when loading the collections. Below is the traceback error. It says it failed to detect the target database version.

I am using the pypgstac v0.9.0. Besides the error, I think there are also other errors when migrating such as permission denied to set role "pgstac_ingest". I thought it was similar to this issue: https://github.com/stac-utils/pgstac/issues/239. But I think its different.

pypgstac-loader      | DEBUG:pypgstac.db:PG VERSION: 14.12.
pypgstac-loader      | INFO:pypgstac.migrate:Migrating PgSTAC on PostgreSQL Version 14.12
pypgstac-loader      | DEBUG:pypgstac.db:PgSTAC is not installed.
pypgstac-loader      | INFO:pypgstac.migrate:No pgstac version set, installing 0.9.1 from scratch.
pypgstac-loader      | DEBUG:pypgstac.migrate:Running migration file /usr/local/lib/python3.11/site-packages/pypgstac/migrations/pgstac.0.9.1.sql.
pypgstac-loader      | DEBUG:smart_open.smart_open_lib:{'uri': '/usr/local/lib/python3.11/site-packages/pypgstac/migrations/pgstac.0.9.1.sql', 'mode': 'r', 'buffering': -1, 'encoding': None, 'errors': None, 'newline': None, 'closefd': True, 'opener': None, 'compression': 'infer_from_extension', 'transport_params': None}

pypgstac-loader      | INFO:pypgstac.db:NOTICE - role "pgstac_admin" already exists, skipping
pypgstac-loader      | INFO:pypgstac.db:NOTICE - role "pgstac_read" already exists, skipping
pypgstac-loader      | INFO:pypgstac.db:NOTICE - role "pgstac_ingest" already exists, skipping
pypgstac-loader      | INFO:pypgstac.db:NOTICE - role "pgstac_ingest" is already a member of role "pgstac_read"
pypgstac-loader      | Traceback (most recent call last):
pypgstac-loader      |   File "/usr/local/bin/pypgstac", line 8, in <module>
pypgstac-loader      |     sys.exit(cli())
pypgstac-loader      |              ^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 125, in cli
pypgstac-loader      |     fire.Fire(PgstacCLI)
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 141, in Fire
pypgstac-loader      |     component_trace = _Fire(component, args, parsed_flag_args, context, name)
pypgstac-loader      |                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 466, in _Fire
pypgstac-loader      |     component, remaining_args = _CallAndUpdateTrace(
pypgstac-loader      |                                 ^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
pypgstac-loader      |     component = fn(*varargs, **kwargs)
pypgstac-loader      |                 ^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 61, in migrate
pypgstac-loader      |     return migrator.run_migration(toversion=toversion)
pypgstac-loader      |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/migrate.py", line 149, in run_migration
pypgstac-loader      |     cur.execute(migration_sql)
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
pypgstac-loader      |     raise ex.with_traceback(None)
pypgstac-loader      | psycopg.errors.InsufficientPrivilege: permission denied to set role "pgstac_ingest"
pypgstac-loader      | Traceback (most recent call last):
pypgstac-loader      |   File "/usr/local/bin/pypgstac", line 8, in <module>
pypgstac-loader      |     sys.exit(cli())
pypgstac-loader      |              ^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 125, in cli
pypgstac-loader      |     fire.Fire(PgstacCLI)
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 141, in Fire
pypgstac-loader      |     component_trace = _Fire(component, args, parsed_flag_args, context, name)
pypgstac-loader      |                       ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 466, in _Fire
pypgstac-loader      |     component, remaining_args = _CallAndUpdateTrace(
pypgstac-loader      |                                 ^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/fire/core.py", line 681, in _CallAndUpdateTrace
pypgstac-loader      |     component = fn(*varargs, **kwargs)
pypgstac-loader      |                 ^^^^^^^^^^^^^^^^^^^^^^
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/pypgstac.py", line 74, in load
pypgstac-loader      |     loader.load_collections(file, method)
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/load.py", line 200, in load_collections
pypgstac-loader      |     self.check_version()
pypgstac-loader      |   File "/usr/local/lib/python3.11/site-packages/pypgstac/load.py", line 160, in check_version
pypgstac-loader      |     raise Exception("Failed to detect the target database version.")
pypgstac-loader      | Exception: Failed to detect the target database version.

[nikkopante]

Update:

I tried to use pypgstac from a Windows local computer not running in docker. I tried to use migrate with --dsn tag.

DEBUG:pypgstac.db:PG VERSION: 14.12.
INFO:pypgstac.migrate:Migrating PgSTAC on PostgreSQL Version 14.12
DEBUG:pypgstac.db:PgSTAC is not installed.
INFO:pypgstac.migrate:No pgstac version set, installing 0.9.1 from scratch.
DEBUG:pypgstac.migrate:Running migration file C:\Users\HP\anaconda3\envs\dpad\Lib\site-packages\pypgstac\migrations\pgstac.0.9.1.sql.
DEBUG:smart_open.smart_open_lib:{'uri': 'C:\\Users\\HP\\anaconda3\\envs\\dpad\\Lib\\site-packages\\pypgstac\\migrations\\pgstac.0.9.1.sql', 'mode': 'r', 'buffering': -1, 'encoding': None, 'errors': None, 'newline': None, 'closefd': True, 'opener': None, 'compression': 'infer_from_extension', 'transport_params': None}

Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "C:\Users\HP\anaconda3\envs\dpad\Scripts\pypgstac.exe\__main__.py", line 7, in <module>
  File "C:\Users\HP\anaconda3\envs\dpad\Lib\site-packages\pypgstac\pypgstac.py", line 125, in cli
    fire.Fire(PgstacCLI)
  File "C:\Users\HP\anaconda3\envs\dpad\Lib\site-packages\fire\core.py", line 141, in Fire
    component_trace = _Fire(component, args, parsed_flag_args, context, name)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\anaconda3\envs\dpad\Lib\site-packages\fire\core.py", line 466, in _Fire
    component, remaining_args = _CallAndUpdateTrace(
                                ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\anaconda3\envs\dpad\Lib\site-packages\fire\core.py", line 681, in _CallAndUpdateTrace
    component = fn(*varargs, **kwargs)
                ^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\anaconda3\envs\dpad\Lib\site-packages\pypgstac\pypgstac.py", line 61, in migrate
    return migrator.run_migration(toversion=toversion)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\HP\anaconda3\envs\dpad\Lib\site-packages\pypgstac\migrate.py", line 148, in run_migration
    migration_sql = get_sql(file)
                    ^^^^^^^^^^^^^
  File "C:\Users\HP\anaconda3\envs\dpad\Lib\site-packages\pypgstac\migrate.py", line 104, in get_sql
    sqlstrs.extend(fd.readlines())
                   ^^^^^^^^^^^^^^
  File "C:\Users\HP\anaconda3\envs\dpad\Lib\encodings\cp1252.py", line 23, in decode
    return codecs.charmap_decode(input,self.errors,decoding_table)[0]
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
UnicodeDecodeError: 'charmap' codec can't decode byte 0x8d in position 1085: character maps to <undefined>

[bitner]

What permissions does the role that you are using with pypgstac migrate have? Is it the same role that was used to install pypgstac in the first place? Those first errors that you are getting just look like you are trying to migrate with a role that does not have the right permissions. I think that GRANT pgstac_admin TO <role that you are using>; should allow things to work for you, if not, try running it with a user with SUPERUSER permissions.

As to the Windows issue, I don't have a Windows environment that I can debug with, so I can't replicate that issue to track down.

[jackharrhy]

I'm also running into this issue, my database (also running on Google Cloud, within CloudSQL) is running 0.7.10 currently.

Using pypgstac migrate, it seems 0.7.10 to 0.8.0 is fine, so is 0.8.0 to 0.8.1, but an exception occurs while trying to upgrade from 0.8.1 to 0.8.2.

I'm also running into permission denied to set role "pgstac_ingest".

My user, postgres, has both pgstac_admin, and cloudsqlsuperuser, on CloudSQL it seems not possible to set a user to full SUPERUSER permissions.

I'm certain this is the user that created these tables as well.

Reporting the above findings here while I'm currently looking into fixing this, hopefully will report back with a fix shortly!

[jackharrhy]

Managed to fix it!

Although my user had pgstac_admin, and cloudsqlsuperuser, I believe it was failing to SET ROLE to pgstac_ingest.

I think pgstac_admin in my roles was not setup for some reason to inherit any subsequent roles? It seems like pgstac_ingest inherited pgstac_read, but pgstac_admin inherited nothing. Not sure if this is a red herring, or if due to my own fault mucking up the state of my database in a previous migration or something else...

Anyways, TL;DR, fix for me was:

GRANT pgstac_ingest TO postgres

And upgraded to 0.9.1 without any more issues :)