unable to upgrade postgres extensions

On 8/21/24 12:14, plsqlvids01 plsqlvids01 wrote:

AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per
https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x <https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x&gt; am trying to upgrade pg_cron and pgaudit extensions but it keeps throwing the same error, how to upgrade them?

|postgres=> select version(); version
--------------------------------------------------------------------------------------------------------- PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit (1 row) postgres=> select * from pg_extension; oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition -------+---------+----------+--------------+----------------+------------+---------------------------+--------------- 14501 | plpgsql | 10 | 11 | f | 1.0 | | 16410 | pg_cron | 10 | 11 | f | 1.6 | {16425,16423,16447,16445} | {"","","",""} 16413 | pgaudit | 10 | 16412 | t | 1.4.3 | | (3 rows) postgres=> select * from pg_available_extensions where name in ('pgaudit','pg_cron'); name | default_version | installed_version | comment ---------+-----------------+-------------------+--------------------------------- pgaudit | 16.0 | 1.4.3 | provides auditing functionality pg_cron | 1.6 | 1.6 | Job scheduler for PostgreSQL (2 rows) postgres=> ALTER EXTENSION pgaudit update to "16.0"; ERROR: extension "pgaudit" has no update path from version "1.4.3" to version "16.0" postgres=> ALTER EXTENSION pgaudit update to "16.0.0"; ERROR: extension "pgaudit" has no update path from version "1.4.3" to version "16.0.0" postgres=> ALTER EXTENSION pgaudit update to "16.0.1"; ERROR: extension "pgaudit" has no update path from version "1.4.3" to version "16.0.1" postgres=> ALTER EXTENSION pg_cron update to "1.6.1";

Here:

https://github.com/pgaudit/pgaudit/tree/REL_16_STABLE

I don't see any provision for doing an update from one version to another.

ERROR: extension "pg_cron" has no update path from version "1.6" to version "1.6.1"|

Which is true:

name | default_version | installed_version |
pg_cron | 1.6 | 1.6

You can't update as it does not exist.

How did you do the upgrade from 12.7 to 16.1?

FYI, per here:

https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-release-calendar.html

RDS is up to Postgres 16.4, which is the latest minor release and what
you should be using.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2024-Aug-21, plsqlvids01 plsqlvids01 wrote:

AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per
https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x
am
trying to upgrade pg_cron and pgaudit extensions but it keeps throwing the
same error, how to upgrade them?

postgres=> select * from pg_available_extensions where name in
('pgaudit','pg_cron');
name | default_version | installed_version |
comment---------+-----------------+-------------------+---------------------------------
pgaudit | 16.0 | 1.4.3 | provides auditing functionality
pg_cron | 1.6 | 1.6 | Job scheduler for PostgreSQL

postgres=> ALTER EXTENSION pgaudit update to "16.0";
ERROR: extension "pgaudit" has no update path from version "1.4.3" to
version "16.0"

That sounds like an RDS problem, so you should be talking to Amazon
support. But maybe see in "select * from
pg_available_extension_versions" if the versions their docco claims are
available, actually are. Also, the fact that a version is available
does not automatically mean that an upgrade path exists; you may need to
do the upgrade in multiple jumps, for instance for pgAudit go from 1.4.3
to 1.6.2 first, then to 1.7.0 finally to 16.0.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"How strange it is to find the words "Perl" and "saner" in such close
proximity, with no apparent sense of irony. I doubt that Larry himself
could have managed it." (ncm, http://lwn.net/Articles/174769/)

plsqlvids01 plsqlvids01 <plsqlvids01@gmail.com> writes:

AWS RDS Postgres database on v12.17 is upgraded to v16.1, as per
https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html#postgresql-extensions-16x
am
trying to upgrade pg_cron and pgaudit extensions but it keeps throwing the
same error, how to upgrade them?

You seem to be assuming that PG extensions have versioning matching
the core server. There's no requirement for that, and it doesn't
look like these extensions attempt to keep those numbers in sync.

The underlying compiled modules (.so files) do need to be updated
for each major server version. But if they load and work then
that's happened. The "extension version" is just an arbitrary
identifier for the version of the extension's SQL-level declarations,
which frequently don't need to change for a server update.

regards, tom lane