Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sbgeZ-000j6b-Hh for pgsql-admin@arkaria.postgresql.org; Wed, 07 Aug 2024 13:24:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sbgeX-007JOf-Ci for pgsql-admin@arkaria.postgresql.org; Wed, 07 Aug 2024 13:24:33 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sbgeW-007JOW-Ty for pgsql-admin@lists.postgresql.org; Wed, 07 Aug 2024 13:24:33 +0000 Received: from mail-ej1-x630.google.com ([2a00:1450:4864:20::630]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sbgeU-003dWf-5C for pgsql-admin@lists.postgresql.org; Wed, 07 Aug 2024 13:24:32 +0000 Received: by mail-ej1-x630.google.com with SMTP id a640c23a62f3a-a7ab76558a9so151278066b.1 for ; Wed, 07 Aug 2024 06:24:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723037069; x=1723641869; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=4gYmeeuZHOqdWRAb845jpxqoyYGgXDYxJg9hWZhVSBM=; b=X+zf2Yc16al7Hr38y6IYWckjbkYxkztn9pJ6CWX6v+b9Mu50iYTSMCUeILUyL1diSX hoUnwEZCL57m3qwaJIkSzuBPRlngwTxcrc5btmshlUa7sKWU8pCRbuhAhy4uGb3srXWz TEx5bc/wIh1YPBzDvjRdZO4tHgy6u0nUQ9jJO5PScjkJKxxQWgxGSKw6Qg+RJRNjqpDW dWlBW7uL7N2nykkvtyjgeWhViW1D1J/OrJBBX+0+XDB3PcPU1TEJgbjEqBTFuDN5ZY7Y zABN1M7RGswkgGTC1WmszDiOLxpI/LBqnWeDTYLj9bZkUaIo12f9WRkdTDnUxfT8l/Q6 xEbA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723037069; x=1723641869; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=4gYmeeuZHOqdWRAb845jpxqoyYGgXDYxJg9hWZhVSBM=; b=dKQzUHvvfzI3w3nJZTQRt+wxSklJ4UF4stSjKb10c7CHFEKiIBkbzhsnFDgNjPBiSD UAL3HPMa5Qu3PS5cfKKCW6S8VeakZ0sCIeWf8ciaPAluiEKY8yG2dZZ8wmmfMkctxOJN GNcdXpy75UfeYAJHPdRNeJVFIsBH7oLMhRfs+brIlyuoeeh53Vv4A0vZAwF7UaVusRS/ u2uo3Xk9Bwnl690n5758BTLKFS8/TW5ukA+fcSqLZWUHc/n1HQz3au9HAWPt6waBZEZ/ c8i73198dm0L6qQpA3Hls9mfZy9rZzmZhjX5RRDi6yC6vQPItkCfC58DB98nAZaYBAKO P+Eg== X-Gm-Message-State: AOJu0YxOtvD4mUaVUUUDEUO3GFtcZNUJg0+UtEV0JKK+Pm5zeX06BZwW qFMor3BEMrYhrLP0k8q5eJsR3Mw8Dy/9UYmIjCSeDC+npxFZDteItiTaNtSySx/DoM0j8vynq9t c4yppSPSxIhdS0/O0Id3+WLnjcFE= X-Google-Smtp-Source: AGHT+IE7+Qv5Gt5gbXlDK+skqjYQNkziUTxRgQHfa2CptnnQscTiX/CVeX+qm4Hzm6kLZl2uVdspiggOdrGM2ykVTYo= X-Received: by 2002:a17:906:ee8e:b0:a7a:a557:454e with SMTP id a640c23a62f3a-a807900cfe1mr216079166b.2.1723037068776; Wed, 07 Aug 2024 06:24:28 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: jacob ndinkwa Date: Wed, 7 Aug 2024 09:24:16 -0400 Message-ID: Subject: Re: Schedule pg_repack job with pg_cron To: Sathish Reddy Cc: pgsql-admin@lists.postgresql.org, Keith , keith.fiske@crunchydata.com, Laurenz Albe Content-Type: multipart/alternative; boundary="0000000000006b5e33061f17d40e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006b5e33061f17d40e Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hello Sathish, To schedule a pg_repack job using pg_cron on an Amazon RDS for PostgreSQL instance, you need to follow a few steps. However, it=E2=80=99s important t= o note that pg_cron is only supported on certain versions of Amazon RDS, and pg_repack is also a separate extension that must be installed and enabled. Here=E2=80=99s a general guide on how you can set this up: *Prerequisites* 1. *Check Compatibility*: Ensure that your RDS instance supports pg_cron and pg_repack. Both extensions need to be available and supported by the RDS version you are using. 2. *Install pg_repack*: Ensure pg_repack is installed on your RDS instance. You can install it via the AWS Management Console if it's available for your PostgreSQL version. For example: - Go to *RDS Dashboard* -> *Parameter Groups*. - Modify the parameter group associated with your RDS instance to add pg_repack to the shared_preload_libraries parameter. 3. *Enable pg_cron*: Similarly, ensure that pg_cron is enabled. You may need to add pg_cron to the shared_preload_libraries in your parameter gr= oup and reboot your instance. *Setting Up the Job* 1. *Create the pg_cron Extension*: First, create the pg_cron extension in your database: sql Copy code CREATE EXTENSION IF NOT EXISTS pg_cron; 2. *Create the pg_repack Extension*: Similarly, create the pg_repack extension: sql Copy code CREATE EXTENSION IF NOT EXISTS pg_repack; 3. *Schedule a pg_repack Job*: You can schedule a job using pg_cron to run pg_repack. Here is an example of how to set up a weekly job: sql Copy code SELECT cron.schedule( 'weekly_repack', '0 3 * * 0', -- This runs every Sunday at 3 AM $$ SELECT pg_repack.repack_database(); $$); In this example, pg_repack.repack_database() is called to reorganize the database. Adjust the schedule expression (0 3 * * 0) as needed to fit your desired schedule. *Considerations* - *Permissions*: Ensure the user running the pg_cron job has the necessary permissions to execute pg_repack. - *Performance Impact*: Running pg_repack can be resource-intensive. Schedule the job during off-peak hours to minimize the impact on your database. - *Testing*: Before scheduling the job, test pg_repack on a non-production instance to ensure it behaves as expected. - *Monitoring*: Monitor the job to ensure it completes successfully and troubleshoot any issues that arise. By setting up pg_repack with pg_cron on Amazon RDS for PostgreSQL, you can automate the process of reclaiming disk space and improving database performance without significant downtime. Thanks! Jake On Wed, Aug 7, 2024 at 4:53=E2=80=AFAM Sathish Reddy < sathishreddy.postgresql@gmail.com> wrote: > Hi > Please share the details for pg_repack job schedule with pg_cron from > RDS postgres database instance level. > > Thanks > Sathishreddy > --0000000000006b5e33061f17d40e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello=C2=A0Sathish,

To schedule a pg_repack job using pg_cron on an Amazon R= DS for PostgreSQL instance, you need to follow a few steps. However, it=E2=80= =99s important to note that pg_cron is only supported on certain versions of Ama= zon RDS, and pg_repack is also a separate extension that must be installed and enabled.

Here=E2=80=99s a general guide on how= you can set this up:

Prerequisites

  1. Check Compatibility: Ensure that your RDS instance supports pg_cron and = pg_repack. Both extensions need to be available and supported by the RDS version = you are using.
  2. Install pg_repack: Ensure pg_repack is installed on your RDS instance. You= can install it via the AWS Management Console if it's available for yo= ur PostgreSQL version. For example:
    • Go to RDS Dashboard -> Parameter Groups.
    • Modify the parameter group associated with your RDS instance to add pg_repac= k to the shared_preload_libraries parameter.
  3. Enable pg_cron: Similarly, ensure that pg_cron is enabled. You may need t= o add pg_cron to the shared_preload_libraries in your parameter group an= d reboot your instance.

Setting Up the Job

  1. Create the pg_cron Extension: First, create the pg_cron extension in your database:

sql

Copy code

CREATE EXTENSION IF NOT EXISTS pg_cro= n;

  1. Create the pg_repack Extension: Similarly, create the pg_repack extension= :

sql

Copy code

CREATE EXTENSION IF NOT EXISTS pg_rep= ack;

  1. Schedule a pg_repack Job: You can schedule a job using pg_cron to run pg_re= pack. Here is an example of how to set up a weekly job:

sql

Copy code

SELECT cron.schedule(

=C2=A0 'weekly_repack',

=C2=A0 '0 3 * * 0',=C2=A0 -- = This runs every Sunday at 3 AM

=C2=A0 $$

=C2=A0 SELECT pg_repack.repack_database();

=C2=A0 $$);

In this example, pg_repack.repack_dat= abase() is called to reorganize the database. Adjust the schedule expression (0 3 * * 0) as need= ed to fit your desired schedule.

Considerations

  • Permissions: Ensure the user running the pg_cron job has the necessary permissions = to execute pg_repack.
  • Performance Impact: Running pg_repack can be resource-intensive. Schedule the = job during off-peak hours to minimize the impact on your database.
  • Testing: Before scheduling the job, test pg_repack on a non-production instance= to ensure it behaves as expected.
  • Monitoring: Monitor the job to ensure it completes successfully and troubleshoot a= ny issues that arise.

By setting up pg_repack with pg_cron = on Amazon RDS for PostgreSQL, you can automate the process of reclaiming disk space and impro= ving database performance without significant downtime.

=C2=A0

Thanks!

Jake

<= /div>
O= n Wed, Aug 7, 2024 at 4:53=E2=80=AFAM Sathish Reddy <sathishreddy.postgresql@gmail.com>= wrote:
Hi=C2=A0
=C2=A0 Please share the details for pg_= repack job schedule with pg_cron from RDS postgres database instance level.=

Thanks=C2=A0
Sathishreddy=C2=A0
--0000000000006b5e33061f17d40e--