Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

pg_repack version mismatch after maintenance on cloud sql (GCP)

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?

like image 703
Omri Shani Avatar asked Mar 25 '21 18:03

Omri Shani


1 Answers

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.

like image 56
Yunyu Lin Avatar answered Oct 24 '22 10:10

Yunyu Lin