pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPg_repack
26+ messages / 12 participants
[nested] [flat]
* Pg_repack
@ 2024-07-23 05:22 Sathish Reddy <sathishreddy.postgresql@gmail.com>
0 siblings, 3 replies; 26+ messages in thread
From: Sathish Reddy @ 2024-07-23 05:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 05:28 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
2 siblings, 1 reply; 26+ messages in thread
From: Ron Johnson @ 2024-07-23 05:28 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Tue, Jul 23, 2024 at 1: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 .
>
Out of curiosity, why do you feel the need to regularly run pg_repack?
IOW, why doesn't plain old VACUUM suit your needs?
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 05:36 Deepak Pahuja . <deepakpahuja@hotmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Deepak Pahuja . @ 2024-07-23 05:36 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Hi John
Vacuum full generally takes very long time on large tables and also does exclusive lock on table causing it unavailable.
Moreover it generates huge wal files and causes replication lag on secondary in streaming replication setup.
Pg_repacknis online and removes complete bloating.
But experts can tell more.
Thanks
Sent from Outlook for Android<https://aka.ms/AAb9ysg;
________________________________
From: Ron Johnson <ronljohnsonjr@gmail.com>
Sent: Tuesday, July 23, 2024 1:28:28 PM
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Pg_repack
On Tue, Jul 23, 2024 at 1:22 AM Sathish Reddy <sathishreddy.postgresql@gmail.com<mailto: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<http://instance.it; getting successful but not clearing bloat by using repack fuction in pg_repack extension.please help on these to sort out .
Out of curiosity, why do you feel the need to regularly run pg_repack? IOW, why doesn't plain old VACUUM suit your needs?
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 05:43 khan Affan <bawag773@gmail.com>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
2 siblings, 1 reply; 26+ messages in thread
From: khan Affan @ 2024-07-23 05:43 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>
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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 05:53 Sathish Reddy <sathishreddy.postgresql@gmail.com>
parent: khan Affan <bawag773@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Sathish Reddy @ 2024-07-23 05:53 UTC (permalink / raw)
To: khan Affan <bawag773@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>
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
>>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 06:28 khan Affan <bawag773@gmail.com>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: khan Affan @ 2024-07-23 06:28 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>
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
>>>
>>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 06:34 Wells Oliver <wells.oliver@gmail.com>
parent: khan Affan <bawag773@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Wells Oliver @ 2024-07-23 06:34 UTC (permalink / raw)
To: khan Affan <bawag773@gmail.com>; +Cc: Sathish Reddy <sathishreddy.postgresql@gmail.com>; pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>
Consider running pg_repack in a Docker container on ECS/Fargate etc if you
don't want to spin up an EC2 instance. I think a free t2.micro sort of EC2
instance would be plenty to repack against an RDS host, might want to
spring for a few cores to make the indexes faster...
On Mon, Jul 22, 2024 at 11:29 PM khan Affan <bawag773@gmail.com> wrote:
> 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
>>>>
>>>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 07:01 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
2 siblings, 1 reply; 26+ messages in thread
From: Laurenz Albe @ 2024-07-23 07:01 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>
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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 07:20 Sathish Reddy <sathishreddy.postgresql@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 26+ messages in thread
From: Sathish Reddy @ 2024-07-23 07:20 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>
Provide me example how can we use pg_squeeze from RDS environment
On Tue, Jul 23, 2024, 12:31 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> 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
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Pg_repack
@ 2024-07-23 08:06 Sathish Reddy <sathishreddy.postgresql@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Sathish Reddy @ 2024-07-23 08:06 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi team,
I am trying pg_repack run from pg_cron but it is getting failed when I
pass it.we planning to avoid running from EC2 pg_repack and trying to
moving RDS postgres instance level.
SELECT cron.schedule('test_repack_bet',
'5 14 * * *',
$$pg_repack -t bet$$);
Help me to schedule as like above in RDS environment...
Thanks
Sathish Reddy
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 09:51 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Laurenz Albe @ 2024-07-23 09:51 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>
On Tue, 2024-07-23 at 12:50 +0530, Sathish Reddy wrote:
> Provide me example how can we use pg_squeeze from RDS environment
With a hosted service, that would only work if Amazon provides the
extension. Sorry, I didn't see that. Then you probably have to go
the hard way.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-07-23 12:20 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Deepak Pahuja . <deepakpahuja@hotmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Ron Johnson @ 2024-07-23 12:20 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Pahu,
I asked about VACUUM, not VACUUM FULL.
On Tue, Jul 23, 2024 at 1:36 AM Deepak Pahuja . <deepakpahuja@hotmail.com>
wrote:
> Hi John
>
> Vacuum full generally takes very long time on large tables and also does
> exclusive lock on table causing it unavailable.
> Moreover it generates huge wal files and causes replication lag on
> secondary in streaming replication setup.
> Pg_repacknis online and removes complete bloating.
>
> But experts can tell more.
>
> Thanks
>
> Sent from Outlook for Android <https://aka.ms/AAb9ysg;
> ------------------------------
> *From:* Ron Johnson <ronljohnsonjr@gmail.com>
> *Sent:* Tuesday, July 23, 2024 1:28:28 PM
> *To:* Pgsql-admin <pgsql-admin@lists.postgresql.org>
> *Subject:* Re: Pg_repack
>
> On Tue, Jul 23, 2024 at 1: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 .
>
>
> Out of curiosity, why do you feel the need to regularly run pg_repack?
> IOW, why doesn't plain old VACUUM suit your needs?
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Pg_repack
@ 2024-08-06 11:08 Sathish Reddy <sathishreddy.postgresql@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Sathish Reddy @ 2024-08-06 11:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, keith.fiske@crunchydata.com, Keith <keith@keithf4.com>; khan Affan <bawag773@gmail.com>
Hi
We planning to create store procedure (function) in postgres database to
run pg_repack on removing bloating of table or index by using within
postgres instance.
Please help me on details on steps with example for same.
Thanks
Sathishreddy
^ permalink raw reply [nested|flat] 26+ messages in thread
* Pg_repack
@ 2024-08-06 11:09 Sathish Reddy <sathishreddy.postgresql@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: Sathish Reddy @ 2024-08-06 11:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi
We planning to create store procedure (function) in postgres database to
run pg_repack on removing bloating of table or index by using within
postgres instance.
Please help me on details on steps with example for same.
Thanks
Sathishreddy
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-06 11:15 hubert depesz lubaczewski <depesz@depesz.com>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: hubert depesz lubaczewski @ 2024-08-06 11:15 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Tue, Aug 06, 2024 at 04:39:53PM +0530, Sathish Reddy wrote:
> We planning to create store procedure (function) in postgres database to
> run pg_repack on removing bloating of table or index by using within
> postgres instance.
> Please help me on details on steps with example for same.
That will be impossible and/or hard.
The problem is that stored procedure/functions runs in database. and
pg_repack is external program, that *does stuff in database*, but it not
*all* in database.
It's kinda as if you wanted to make stored procedure to run photoshop.
You can kinda work around it by using some PL/* language that allows
external program execution, but it is extremely unlikely to do what
you'd think it will do.
Best regards,
depesz
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-06 11:20 Sathish Reddy <sathishreddy.postgresql@gmail.com>
parent: hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 2 replies; 26+ messages in thread
From: Sathish Reddy @ 2024-08-06 11:20 UTC (permalink / raw)
To: depesz@depesz.com; +Cc: pgsql-admin@lists.postgresql.org
Thanks for the information.please provide me solution on clearing bloating
on table or index bloating with out blocking any user and we are not run
any vaccum full .freeze, and reindex and also analized ...with out these
provide me perfect solution to run and remove bloat instance level with in
postgres..
Thanks
Sathishreddy
On Tue, Aug 6, 2024, 4:45 PM hubert depesz lubaczewski <depesz@depesz.com>
wrote:
> On Tue, Aug 06, 2024 at 04:39:53PM +0530, Sathish Reddy wrote:
> > We planning to create store procedure (function) in postgres database
> to
> > run pg_repack on removing bloating of table or index by using within
> > postgres instance.
> > Please help me on details on steps with example for same.
>
> That will be impossible and/or hard.
>
> The problem is that stored procedure/functions runs in database. and
> pg_repack is external program, that *does stuff in database*, but it not
> *all* in database.
>
> It's kinda as if you wanted to make stored procedure to run photoshop.
>
> You can kinda work around it by using some PL/* language that allows
> external program execution, but it is extremely unlikely to do what
> you'd think it will do.
>
> Best regards,
>
> depesz
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-06 11:22 hubert depesz lubaczewski <depesz@depesz.com>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: hubert depesz lubaczewski @ 2024-08-06 11:22 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Tue, Aug 06, 2024 at 04:50:33PM +0530, Sathish Reddy wrote:
> Thanks for the information.please provide me solution on clearing bloating
> on table or index bloating with out blocking any user and we are not run
> any vaccum full .freeze, and reindex and also analized ...with out these
> provide me perfect solution to run and remove bloat instance level with in
> postgres..
1. Configure autovacuum properly, so the problem doesn't happen.
2. If need is - run manual vacuum
3. If you really have to, run pg_repack - but this is not a thing that
one calls *from within pg* - you run it on some
server/computer/whatever, and pg_repack connects to database, and
does its magic.
Best regards,
depesz
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-06 11:24 abbas alizadeh <ramkly@yahoo.com>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: abbas alizadeh @ 2024-08-06 11:24 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: depesz@depesz.com; pgsql-admin@lists.postgresql.org
--Apple-Mail-8D50908C-B986-4154-90B3-A45EDB2F28CC
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto">Hi<div>Why you don=E2=80=99t use pg_squeeze=
?</div><div>It has its own scheduling to remove bloating.</div><div><br id=3D=
"lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Regards<div>Abbas</div></=
div><div dir=3D"ltr"><br><blockquote type=3D"cite">On 6 Aug 2024, at 2:51=E2=
=80=AFPM, Sathish Reddy <sathishreddy.postgresql@gmail.com> wrote:<br>=
<br></blockquote></div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<=
p dir=3D"ltr">Thanks for the information.please provide me solution on clear=
ing bloating on table or index bloating with out blocking any user and we ar=
e not run any vaccum full .freeze, and reindex and also analized ...with out=
these provide me perfect solution to run and remove bloat instance level wi=
th in postgres..<br></p>
<p dir=3D"ltr">Thanks <br>
Sathishreddy </p>
<br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue,=
Aug 6, 2024, 4:45=E2=80=AFPM hubert depesz lubaczewski <<a href=3D"mailt=
o:depesz@depesz.com">depesz@depesz.com</a>> wrote:<br></div><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;p=
adding-left:1ex">On Tue, Aug 06, 2024 at 04:39:53PM +0530, Sathish Reddy wro=
te:<br>
> We planning to create store procedure (function) in postgre=
s database to<br>
> run pg_repack on removing bloating of table or index by using within<br=
>
> postgres instance.<br>
> Please help me on details on steps with example for s=
ame.<br>
<br>
That will be impossible and/or hard.<br>
<br>
The problem is that stored procedure/functions runs in database. and<br>
pg_repack is external program, that *does stuff in database*, but it not<br>=
*all* in database.<br>
<br>
It's kinda as if you wanted to make stored procedure to run photoshop.<br>
<br>
You can kinda work around it by using some PL/* language that allows<br>
external program execution, but it is extremely unlikely to do what<br>
you'd think it will do.<br>
<br>
Best regards,<br>
<br>
depesz<br>
<br>
</blockquote></div>
</div></blockquote></div></body></html>=
--Apple-Mail-8D50908C-B986-4154-90B3-A45EDB2F28CC--
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-06 11:24 Kashif Zeeshan <kashi.zeeshan@gmail.com>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Kashif Zeeshan @ 2024-08-06 11:24 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Sathish
It's better to script it using any scripting language e.g. SHELL Scripting
as PL/pgSQL etc don't allow accessing Utilities from stored procedures.
Thanks
Kashif Zeeshan
On Tue, Aug 6, 2024 at 4:10 PM Sathish Reddy <
sathishreddy.postgresql@gmail.com> wrote:
> Hi
> We planning to create store procedure (function) in postgres database to
> run pg_repack on removing bloating of table or index by using within
> postgres instance.
>
> Please help me on details on steps with example for same.
>
>
> Thanks
> Sathishreddy
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-06 11:32 Sathish Reddy <sathishreddy.postgresql@gmail.com>
parent: abbas alizadeh <ramkly@yahoo.com>
0 siblings, 0 replies; 26+ messages in thread
From: Sathish Reddy @ 2024-08-06 11:32 UTC (permalink / raw)
To: abbas alizadeh <ramkly@yahoo.com>; +Cc: depesz@depesz.com; pgsql-admin@lists.postgresql.org
Thanks for the tip! We are trying in RDS postgres database environment and
also we are trying to run as instance level as like functional
Thanks
Sathishreddy
On Tue, Aug 6, 2024, 4:56 PM abbas alizadeh <ramkly@yahoo.com> wrote:
> Hi
> Why you don’t use pg_squeeze?
> It has its own scheduling to remove bloating.
>
> Regards
> Abbas
>
> On 6 Aug 2024, at 2:51 PM, Sathish Reddy <
> sathishreddy.postgresql@gmail.com> wrote:
>
>
>
> Thanks for the information.please provide me solution on clearing bloating
> on table or index bloating with out blocking any user and we are not run
> any vaccum full .freeze, and reindex and also analized ...with out these
> provide me perfect solution to run and remove bloat instance level with in
> postgres..
>
> Thanks
> Sathishreddy
>
> On Tue, Aug 6, 2024, 4:45 PM hubert depesz lubaczewski <depesz@depesz.com>
> wrote:
>
>> On Tue, Aug 06, 2024 at 04:39:53PM +0530, Sathish Reddy wrote:
>> > We planning to create store procedure (function) in postgres database
>> to
>> > run pg_repack on removing bloating of table or index by using within
>> > postgres instance.
>> > Please help me on details on steps with example for same.
>>
>> That will be impossible and/or hard.
>>
>> The problem is that stored procedure/functions runs in database. and
>> pg_repack is external program, that *does stuff in database*, but it not
>> *all* in database.
>>
>> It's kinda as if you wanted to make stored procedure to run photoshop.
>>
>> You can kinda work around it by using some PL/* language that allows
>> external program execution, but it is extremely unlikely to do what
>> you'd think it will do.
>>
>> Best regards,
>>
>> depesz
>>
>>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Pg_repack
@ 2024-08-12 11:47 Sathish Reddy <sathishreddy.postgresql@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: Sathish Reddy @ 2024-08-12 11:47 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi
We have configure pg_repack on database.when we ran pg_repak it is using
temporary table on repack once repack done it is going to swap temporary
table to original .on these case it is genarate huse wal files and it
getting size increase be end .
We need help on these instead of using temporary table can we use unlog
table on reduce these wal case.
Thanks
Sathishreddy
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-12 15:48 Alvaro Herrera <alvherre@alvh.no-ip.org>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Alvaro Herrera @ 2024-08-12 15:48 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On 2024-Aug-12, Sathish Reddy wrote:
> Hi
> We have configure pg_repack on database.when we ran pg_repak it is using
> temporary table on repack once repack done it is going to swap temporary
> table to original .on these case it is genarate huse wal files and it
> getting size increase be end .
> We need help on these instead of using temporary table can we use unlog
> table on reduce these wal case.
I bet you'll find that pg_squeeze gives you better characteristics on
those aspects. In any case, it's better if you can find a way to avoid
running either of these tools in a regular manner, and instead treat
them as if they were an emergency solution only, and rely on a better
configured autovacuum to avoid having to schedule them regularly.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-12 16:06 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 26+ messages in thread
From: Ron Johnson @ 2024-08-12 16:06 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Aug 12, 2024 at 11:49 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
> On 2024-Aug-12, Sathish Reddy wrote:
>
> > Hi
> > We have configure pg_repack on database.when we ran pg_repak it is
> using
> > temporary table on repack once repack done it is going to swap temporary
> > table to original .on these case it is genarate huse wal files and it
> > getting size increase be end .
>
> > We need help on these instead of using temporary table can we use
> unlog
> > table on reduce these wal case.
>
> I bet you'll find that pg_squeeze gives you better characteristics on
> those aspects. In any case, it's better if you can find a way to avoid
> running either of these tools in a regular manner, and instead treat
> them as if they were an emergency solution only, and rely on a better
> configured autovacuum to avoid having to schedule them regularly.
>
But pg_repack is just a better VACUUM FULL, and VACUUM FULL has to be
better than autovacuum because it *fully* vacuums a table.
Right? /s
--
Death to America, and butter sauce.
Iraq lobster!
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-12 17:54 Rui DeSousa <rui.desousa@icloud.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Rui DeSousa @ 2024-08-12 17:54 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Aug 12, 2024, at 12:06 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> But pg_repack is just a better VACUUM FULL, and VACUUM FULL has to be better than autovacuum because it fully vacuums a table.
>
No.
Vacuum — actually vacuums by removing dead tuples that are no longer needed, freezing tuples, etc. The removal of dead tuples frees space on the given page and it also truncates the fully empty pages that are located at the end of the file if it can.
Vacuum FULL — is something completely different. It rebuilds the entire table thus it coalesces all free space and by proxy does the same as vacuum (removing dead tuples that are no longer needed).
— It does this by creating a new table and then swapping in the new table when; regardless of the number of dead tuples.
Vacuum FULL should not be run on a regular basis.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-12 17:55 Rui DeSousa <rui.desousa@icloud.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Rui DeSousa @ 2024-08-12 17:55 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Aug 12, 2024, at 12:06 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
>
> But pg_repack is just a better VACUUM FULL, and VACUUM FULL has to be better than autovacuum because it fully vacuums a table.
>
No.
Vacuum — actually vacuums by removing dead tuples that are no longer needed, freezing tuples, etc. The removal of dead tuples frees space on the given page and it also truncates the fully empty pages that are located at the end of the file if it can.
Vacuum FULL — is something completely different. It rebuilds the entire table thus it coalesces all free space and by proxy does the same as vacuum (removing dead tuples that are no longer needed).
— It does this by creating a new table and then swapping in the new table when; regardless of the number of dead tuples.
Vacuum FULL should not be run on a regular basis.=
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: Pg_repack
@ 2024-08-13 08:15 Muhammad Imtiaz <imtiazpg712@gmail.com>
parent: Sathish Reddy <sathishreddy.postgresql@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Muhammad Imtiaz @ 2024-08-13 08:15 UTC (permalink / raw)
To: Sathish Reddy <sathishreddy.postgresql@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
Pg_repack doesn’t use unlogged tables, but you can minimize WAL file size
by enabling wal_compression in postgresql.conf. Additionally, you can
fine-tune wal_buffers, checkpoint_timeout, and checkpoint_completion_target
to better manage WAL file size.
Regards,
Muhammad Imtiaz
On Mon, Aug 12, 2024 at 4:48 PM Sathish Reddy <
sathishreddy.postgresql@gmail.com> wrote:
> Hi
> We have configure pg_repack on database.when we ran pg_repak it is using
> temporary table on repack once repack done it is going to swap temporary
> table to original .on these case it is genarate huse wal files and it
> getting size increase be end .
>
> We need help on these instead of using temporary table can we use
> unlog table on reduce these wal case.
>
>
> Thanks
> Sathishreddy
>
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2024-08-13 08:15 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-23 05:22 Pg_repack Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-07-23 05:28 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-23 05:36 ` Deepak Pahuja . <deepakpahuja@hotmail.com>
2024-07-23 12:20 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-23 05:43 ` khan Affan <bawag773@gmail.com>
2024-07-23 05:53 ` Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-07-23 06:28 ` khan Affan <bawag773@gmail.com>
2024-07-23 06:34 ` Wells Oliver <wells.oliver@gmail.com>
2024-07-23 07:01 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-23 07:20 ` Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-07-23 09:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-07-23 08:06 Pg_repack Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-08-06 11:08 Pg_repack Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-08-06 11:09 Pg_repack Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-08-06 11:15 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-08-06 11:20 ` Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-08-06 11:22 ` hubert depesz lubaczewski <depesz@depesz.com>
2024-08-06 11:24 ` abbas alizadeh <ramkly@yahoo.com>
2024-08-06 11:32 ` Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-08-06 11:24 ` Kashif Zeeshan <kashi.zeeshan@gmail.com>
2024-08-12 11:47 Pg_repack Sathish Reddy <sathishreddy.postgresql@gmail.com>
2024-08-12 15:48 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2024-08-12 16:06 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-08-12 17:54 ` Rui DeSousa <rui.desousa@icloud.com>
2024-08-12 17:55 ` Rui DeSousa <rui.desousa@icloud.com>
2024-08-13 08:15 ` Muhammad Imtiaz <imtiazpg712@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox