pg_repack extension

[Suhogruz]

Postgres use MVCC model and everyone dba and maybe customer know peculiarities MVCC model like bloat. Yes, you may run VACCUM FULL but this not always possible in production. That's why it would be cool to have the pg_repack extension in Crunchy PGO. This is a fairly well-established extension for removing bloat in tables. Maybe i don't know and Crunchy pgo has instruments for removing tables bloat, if this true please tell me how to do it or give the link in documentation because i didn't find nothing for this request in doc.

[Martin-Hogge]

Did you have any further informations about this ? I would also like to install pg_repack on crunchy but I'm not sure on how to do it.

[Suhogruz]

No, I found only this #2016 (comment) but I didn't try it yet

[dspitfire]

My ad-hoc Dockerfile looks that

FROM registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.3-0

USER 0
RUN rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
RUN mv /etc/yum.repos.d/crunchypg14.repo /tmp/crunchypg14.repo
RUN microdnf install pg_repack_14
RUN mv /tmp/crunchypg14.repo /etc/yum.repos.d/crunchypg14.repo
USER 26

It works, but it's a one shot solution of course. I need much more time to understand build scripts of postgres-operator to suggest proper PR

[Martin-Hogge]

My ad-hoc Dockerfile looks that

FROM registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-14.3-0

USER 0
RUN rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
RUN mv /etc/yum.repos.d/crunchypg14.repo /tmp/crunchypg14.repo
RUN microdnf install pg_repack_14
RUN mv /tmp/crunchypg14.repo /etc/yum.repos.d/crunchypg14.repo
USER 26

It works, but it's a one shot solution of course. I need much more time to understand build scripts of postgres-operator to suggest proper PR

Thank's a lot ! I just finished building my own ad-hoc Dockerfile an hour before your comment. I share mine as it could help.

FROM registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-13.4-1

USER root

# https://forketyfork.medium.com/centos-8-no-urls-in-mirrorlist-error-3f87c3466faa
RUN sed -i -e "s|mirrorlist=|#mirrorlist=|g" /etc/yum.repos.d/CentOS-*
RUN sed -i -e "s|#baseurl=http://mirror.centos.org|baseurl=http://vault.centos.org|g" /etc/yum.repos.d/CentOS-*

# Without disabling crunchypg we have an error "no host" on tomacco.com
# Inspired by https://github.com/CrunchyData/postgres-operator/issues/2193#issuecomment-765756069
RUN yum --disablerepo=crunchypg13 update -y --allowerasing
# Add postgresql repo (https://dbsguru.com/install-postgresql-13-on-linux-using-yum-command/) - without this pg_repack installation fails
RUN yum --disablerepo=crunchypg13 install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# Install pg_repack
RUN yum --disablerepo=crunchypg13 install -y pg_repack13
RUN yum clean all

# Postgres user id
USER 26

[benjaminjb]

Thanks for reaching out about adding pg_repack.

We do not have any immediate plans for adding that at this time and do not have an approved set of instructions for adding extensions at the moment. In the meantime, I have captured a story in our backlog for adding pg_repack in a future release and/or update. And I'm going to leave this issue open so that others can chime in with their use-cases for that extension.

Feel free to reach out at [email protected] if you would like to discuss your use case further.

[thatsanicehat]

Adding my use-case here: Our application supports a 24/7/~360 warehouse, and taking the application offline to do a full vacuum is extremely disruptive. We have multiple tables where we purge anything older than a year, but this leaves us with a lot of bloat (12GB on a table with 500MB in some cases). This extension would really help us get that cleaned up, since a normal vacuum only makes that freed up bloat space available for the original table and not the rest of the application.