pg_repack version mismatch after maintenance on cloud sql (GCP)

Question

I have a cloud-sql postgres11 instance on GCP and use pg_repack cron for cleaning my database. I've noticed that since last maintenance occurred (7th of March 21) I cannot perform a repack. When tried to manually run a repack I encountered this error message:

ERROR: pg_repack failed with error: program 'pg_repack 1.4.4' does not match database library 'pg_repack 1.4.6'

Did the following checks:

• what is the version of pg_repack loaded:

                                       List of installed extensions
        Name        | Version |   Schema   |                         Description
--------------------+---------+------------+--------------------------------------------------------------
 pg_repack          | 1.4.4   | public     | Reorganize tables in PostgreSQL databases with minimal locks
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

• what is the available version of pg_repack:

   name    | version | installed | superuser | relocatable | schema | requires |                           comment
-----------+---------+-----------+-----------+-------------+--------+----------+--------------------------------------------------------------
 pg_repack | 1.4.4   | t         | t         | f           |        |          | Reorganize tables in PostgreSQL databases with minimal locks
(1 row)

I upgraded pg_repack to version 1.4.6 and it did not help, I also tried to drop and create the extension, or restart the sql-instance with no luck. :-(

I wonder if someone had encouctered this issue. If so, is there any solution?

Quarterstaff

Answer 1

I got this working on Debian 10 with a very jank workaround. Basically I built a copy of 1.4.6 with the version checks commented out, and successfully ran it with the -k flag:

sudo apt install build-essential postgresql-server-dev-13 libssl-dev zlib1g-dev libreadline-dev
git clone https://github.com/yunyu/pg_repack.git # My fork with the version checks commented out
cd pg_repack
make && sudo make install
./bin/pg_repack <flags>

It seemed to work and I haven't run into any issues. Obviously run this on a VM that can access the Postgres instance, since you need shell access to even execute pg_repack.

Lesbian

CloudSQL is fully managed by Google, you can not access the OS to perform this. – Admire

@MarioGranados You run this on a VM that can connect to the Postgres instance. Obviously you need a shell somewhere in order to run pg_repack, or even access the database with psql in the first place... – Lesbian

Answer 2

Upgrade the extension:

ALTER EXTENSION pg_repack UPDATE;

Neurology

didn't wotk. I can "update" to nothing but 1.4.4 dibi=> ALTER extension pg_repack update TO "1.4.6"; ERROR: Extension "pg_repack" and version "1.4.6" not supported by Cloud SQL. – Quarterstaff

Then the software wasn't installed properly. – Neurology

This is an open issue on GCP: issuetracker.google.com/issues/180193979 – Heterozygous