Thread: Pg_repack

Hi 

 I am trying to schedule pg_repack from pg_cron in RDS postgres environment on avoid the bash on host EC2 to run run directly with in postgres instance.it getting successful but not clearing bloat by using repack fuction in pg_repack extension.please help on these to sort out .

Thanks 

Sathish Reddy 

Hi 

First, use Vaccum Full or Vaccumlo if storing largeobject for clearing bloat, If you are running a script, dump it outside the RDS, such as you can dump it to EC2, and then apply PG_repack on the schema and then restore it to RDS.

As you know such services are not available on RDS. 

https://www.postgresql.org/docs/current/vacuumlo.html

https://www.postgresql.org/docs/current/sql-vacuum.html

Thanks & regards

Muhammad Affan (아판)

PostgreSQL Technical Support Engineer / Pakistan R&D

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan

On Tue, Jul 23, 2024 at 10:22 AM Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:

Hi 

 I am trying to schedule pg_repack from pg_cron in RDS postgres environment on avoid the bash on host EC2 to run run directly with in postgres instance.it getting successful but not clearing bloat by using repack fuction in pg_repack extension.please help on these to sort out .

Thanks 

Sathish Reddy 

We are planning to run pg repack from pg_cron in RDS environment not in EC2  help me schedule job pg_repack

On Tue, Jul 23, 2024, 11:13 AM khan Affan <bawag773@gmail.com> wrote:

Hi 

First, use Vaccum Full or Vaccumlo if storing largeobject for clearing bloat, If you are running a script, dump it outside the RDS, such as you can dump it to EC2, and then apply PG_repack on the schema and then restore it to RDS.

As you know such services are not available on RDS. 

https://www.postgresql.org/docs/current/vacuumlo.html

https://www.postgresql.org/docs/current/sql-vacuum.html

Thanks & regards

Muhammad Affan (아판)

PostgreSQL Technical Support Engineer / Pakistan R&D

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan

On Tue, Jul 23, 2024 at 10:22 AM Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:

Hi 

 I am trying to schedule pg_repack from pg_cron in RDS postgres environment on avoid the bash on host EC2 to run run directly with in postgres instance.it getting successful but not clearing bloat by using repack fuction in pg_repack extension.please help on these to sort out .

Thanks 

Sathish Reddy 

As stated before, security constraints prevent pg_repack from being executed directly in an RDS PostgreSQL environment.
Because RDS maintains a secure environment, installing custom extensions like pg_repack is restricted.
pg_cron in RDS is intended to be used for scheduling internal PostgreSQL functions or operations; it is not intended to be used with external utilities such as pg_repack. 

The alternative approach is to export your database schema and data (excluding large objects) to an external PostgreSQL instance, run pg_repack on the external instance to reclaim space, and then import the cleaned data back into your RDS instance.

Thanks & regards

Muhammad Affan (아판)

PostgreSQL Technical Support Engineer / Pakistan R&D

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan

On Tue, Jul 23, 2024 at 10:53 AM Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:

We are planning to run pg repack from pg_cron in RDS environment not in EC2  help me schedule job pg_repack

On Tue, Jul 23, 2024, 11:13 AM khan Affan <bawag773@gmail.com> wrote:

Hi 

First, use Vaccum Full or Vaccumlo if storing largeobject for clearing bloat, If you are running a script, dump it outside the RDS, such as you can dump it to EC2, and then apply PG_repack on the schema and then restore it to RDS.

As you know such services are not available on RDS. 

https://www.postgresql.org/docs/current/vacuumlo.html

https://www.postgresql.org/docs/current/sql-vacuum.html

Thanks & regards

Muhammad Affan (아판)

PostgreSQL Technical Support Engineer / Pakistan R&D

Interlace Plaza 4th floor Twinhub office 32 I8 Markaz, Islamabad, Pakistan

On Tue, Jul 23, 2024 at 10:22 AM Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:

Hi 

 I am trying to schedule pg_repack from pg_cron in RDS postgres environment on avoid the bash on host EC2 to run run directly with in postgres instance.it getting successful but not clearing bloat by using repack fuction in pg_repack extension.please help on these to sort out .

Thanks 

Sathish Reddy 

On Tue, 2024-07-23 at 10:52 +0530, Sathish Reddy wrote:
> I am trying to schedule pg_repack from pg_cron in RDS postgres environment on avoid
> the bash on host EC2 to run run directly with in postgres instance.it getting successful
> but not clearing bloat by using repack fuction in pg_repack extension.please help on
> these to sort out .

If you have the need to do this regularly, use pg_squeeze, which avoids the need
for pg_cron.  It can automatically trigger a rebuild of the table according to
conditions you specify.

Yours,
Laurenz Albe