pg_repack failed with error: extension and database library mismatch

[esn89]

I am using pg_repack 1.4.4, and on my database, it matches what is on there:

psql -h $DB_IP -U $DB_USER -d $DB_NAME

postgres=> \dx
                                  List of installed extensions
   Name    | Version |   Schema   |                         Description                          
-----------+---------+------------+--------------------------------------------------------------
 pg_repack | 1.4.4   | public     | Reorganize tables in PostgreSQL databases with minimal locks
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language

However, when I run 1.4.4 on some of my tables within that DB, I get:
ERROR: pg_repack failed with error: program 'pg_repack 1.4.4' does not match database library 'pg_repack 1.4.6'

---

So I go and update the pg_repack binary on the client side to be 1.4.6, but now I get this:

ERROR: pg_repack failed with error: extension 'pg_repack 1.4.6' required, found extension 'pg_repack 1.4.4'

I'm a bit at a loss as to what it wants in terms of version.

This is how I am running the pg_repack command:

/opt/pg_repack-1.4.6/bin/pg_repack -h $DB_IP -U $DB_USER -w -k -d $DB_NAME -t "$table"

[Melkij]

Hello You seem to have multiple databases with different versions of the extension. This is a valid case for postgresql extension logic: someone might have a v1.4.4 extension in a database named postgres and a v1.4.6 extension in a database namedfoo. You need to update (recreate) the pg_repack extension on every database you want to process.

[esn89]

@Melkij

On every database?

Okay, will this cause any data loss? Or just a simple act of deleting the extension and recreating it?

[Melkij]

Right. You need install the extension (and therefore update it) before using it in every database. Because create extension command are isolated per database in PostgreSQL.

pg_repack does not provide support for alter extension pg_repack update syntax. As documented here:

If you are upgrading from a previous version of pg_repack or pg_reorg, just drop the old version from the database as explained above and install the new version.

[esn89]

Right now I am on an database on AWS, a managed one with some databases where I don't have access to, and is used for RDS to store some metadata. If that is the case, is it safe to say that this won't ever be fixed? :<

For example, one database is giving me this:

psql: FATAL:  database "template0" is not currently accepting connections

[omris94]

it seems similar to what I had experienced lately with google managed postgres service [here].(https://stackoverflow.com/questions/66805141/pg-repack-version-mismatch-after-maintenance-on-cloud-sql-gcp) In my case, google cloud support could fix it specifically on my instance, and told me that a full solution will be provided by the 5th of May.

[esn89]

@omris94

This is an answer I've been waiting for. I suppose I gotta hang on for awhile and if I can't, I will be able to make a case with GCP.

Thanks, @omris94

[Melkij]

@esn89 , are you familiar with postgresql extensions? Someone may have database instance with DB named foo, bar, mir. Its possible to have hstore v1.3 in foo, hstore v1.6 in bar, no hstore in mir. They could be upgraded separately. All such hstore are one library file. But this feature has some backward compatibility hazards. pg_repack does not want to pay for such complexity and requires the user to match the version of the extension with the pg_repack utility.

If you need to run pg_repack in the specified database, there must be a corresponding extension in this database (and corresponding version). In every database that needs to be processed. All other databases are irrelevant.

It is also very important to mention the use of RDS or something similar. It is well known that RDS modifies the postgresql source code and therefore may contain new bugs not related to postgresql itself or the tools.

[xzilla]

ISTM this issue could be closed, but since it is still open, I think it is worth clarifying that you only need to update the extension in databases that you intend to use it in. Therefore, in an rds environment (and probably most other environments too) you don't need to update/install the extension, since you're unlikely to ever use template0.

[reuvenstr]

Hi, I have faced the same issue in Google Cloud SQL, they have bumped pg_repack to 1.5.0 but I didn't find any rpm for PostgreSQL 11 because it's already deprecated.

I tried to handle it with the previous version that was installed CREATE EXTENSION IF NOT EXISTS pg_repack version "1.4.8";

But it fails on version mismatch LOG: (query) SET search_path TO pg_catalog, pg_temp, public LOG: (query) SET search_path TO pg_catalog, pg_temp, public NOTICE: Setting up workers.conns LOG: (query) SET search_path TO pg_catalog, pg_temp, public LOG: (query) SET search_path TO pg_catalog, pg_temp, public LOG: (query) SET search_path TO pg_catalog, pg_temp, public LOG: (query) select repack.version(), repack.version_sql() ERROR: pg_repack failed with error: program 'pg_repack 1.4.8' does not match database library 'pg_repack 1.5.0'

Is it something that I can do in order to run repack, without compiling the extension client from sources?