9

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?

aynber
  • 22,380
  • 8
  • 50
  • 63
Omri Shani
  • 91
  • 2

2 Answers2

2

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.

Yunyu L.
  • 715
  • 9
  • 22
  • 1
    CloudSQL is fully managed by Google, you can not access the OS to perform this. – Pamela Chup Apr 02 '21 at 23:40
  • 1
    @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... – Yunyu L. Apr 04 '21 at 00:15
0

Upgrade the extension:

ALTER EXTENSION pg_repack UPDATE;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263