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 1tXCNc-004gxc-Oy for pgsql-admin@arkaria.postgresql.org; Mon, 13 Jan 2025 04:48:49 +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 1tXCNZ-006uSp-Ap for pgsql-admin@arkaria.postgresql.org; Mon, 13 Jan 2025 04:48:45 +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 1tXCNY-006uSh-TL for pgsql-admin@lists.postgresql.org; Mon, 13 Jan 2025 04:48:45 +0000 Received: from mail-lj1-x230.google.com ([2a00:1450:4864:20::230]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tXCNX-0006kt-0N for pgsql-admin@postgresql.org; Mon, 13 Jan 2025 04:48:45 +0000 Received: by mail-lj1-x230.google.com with SMTP id 38308e7fff4ca-30219437e63so45555111fa.1 for ; Sun, 12 Jan 2025 20:48:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1736743722; x=1737348522; darn=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=S3pjKlDWWJi8YWAGbffv4XESbTvR3eMdgSDNulFrot0=; b=fjOzP5mGCxUi7sWQIg8aAX7FC8Gjard92qDTVkUA/4wBZWN6IzOnctl+ShrZHu17FK FcRY933yas7oxiLhu4NhG2JQCJEExmTikdAZM+JmiJNRjFYQiLqNzInHaVnVk8VhVRgK 9eljAtAyYaEcWOd5qQkNI+H9cLr9APEZf/qlcu9vzWZBHKXJlOCB9D0GcIm5PRkXU82Q JO+PUvEcktSY2krKZbDjNPJaLPFPiI14eoya0Z+UheLcXjui7XxNOK9LIjP415/ERv66 INA0JX3f4+FFeuUENIwA9tYsYKOI4zATILLnUBCTYWwDt0Ccv3jowukkd6UtraSghC8I JcbQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736743722; x=1737348522; 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=S3pjKlDWWJi8YWAGbffv4XESbTvR3eMdgSDNulFrot0=; b=loj4nImNFnqM3zc4e7xjGeszQ92HH65++czsGg1BFEYa79pU4t2HeWl/JWdjrqGmaM fv/2wsKnV8LfNK+YL25TOjQX788Yy0lkrXBfkPHcS2skMmccy9SByVg76OY2YDX4Whq5 0UQ/nV6qO/B9eR6EDYhexP6AX6GmijcUZkE8VJyJjkG0q7SfEPMMmOLATh5LqLDgbkvz aE7K+q1SFwtnk4fBb2QjRMDag5vztudrQ3l+dC7ZpWxvoL/rcSDpNaw+iNgnw4b87kEr S9ornfRdr46pwCOTW4BSMUIaLFwRVQ6dl4xTGnFpURsc09B6rtMO3tm7tUdW+V7xwzop FDWw== X-Forwarded-Encrypted: i=1; AJvYcCVTDuD1mb9lWEX6vvAz8ezXdezWDS/2ILAKRvVZr0zUzLp8Jk5PTC+hvaFnpQJUalIGZXDnfGbDKGQQ4g==@postgresql.org X-Gm-Message-State: AOJu0Yznte9pRPyt445s5xVt53n4RTPZWmIqeLsABNexEgG066xfBnPl 6OdHaSiU1cMW8rq8/e2++VP2Jll7ULANwTsRHNpWhG5rGsVBnAI0hqd9TsFd9FuVcZ8epQydfKi 3jK9yHf7btNiqkEC+m2nky2Wb8DIEpg== X-Gm-Gg: ASbGncsoMEROyleRrgOjja6khzWS23fSYGYYc6b+ZoUfLhVhqrApzjL+ptHM6/b1Hom ufAu1AskzwGhBT/k4fWhwpvvn3hDQuBvR2m4LyxGRvVwbLSR2j17NS54cwJdj X-Google-Smtp-Source: AGHT+IFAW/zAu3JhIJS95sCIxpKhmZJ2bxukCOnIt4RYJBTEVdDRvGk8wddvSVM4Dy75uYtd+38SyUuHmJ8qTM9F6/U= X-Received: by 2002:a05:6512:1256:b0:541:32e5:654c with SMTP id 2adb3069b0e04-5428a663693mr4541786e87.17.1736743721484; Sun, 12 Jan 2025 20:48:41 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Imran Khan Date: Mon, 13 Jan 2025 07:48:28 +0300 X-Gm-Features: AbW1kvZAMZK7IutqMa50gx43DnFGzFFwKk13JQhP8ddIn1jjkHRgGJ4wRJjEuXA Message-ID: Subject: Re: Archiving solutions To: Rajesh Kumar Cc: Ron Johnson , pgsql-admin Content-Type: multipart/alternative; boundary="00000000000095d6f2062b8f288a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000095d6f2062b8f288a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Rajesh, Housekeeping activities in PostgreSQL are essential to ensure the database remains high performing, secure, and reliable over time. Below is a categorized list of key housekeeping tasks: --- 1. Routine Maintenance Tasks Vacuuming: Use VACUUM to reclaim storage and update visibility maps. Run VACUUM FULL for aggressive storage reclamation (requires downtime). Analyze Statistics: Run ANALYZE to update query planner statistics for efficient query execution. Use autovacuum for automatic vacuum and analyze. Reindexing: Use REINDEX to rebuild corrupted or bloated indexes. Schedule periodic reindexing for heavily updated tables. --- 2. Backup and Recovery Regular Backups: Use pg_basebackup or pg_dump for full backups. Implement incremental backups if using third-party tools (e.g., pgBackRest, Barman). Test Recovery: Periodically restore backups in a test environment to ensure recovery readiness. --- 3. Monitoring and Logging Review Logs: Check PostgreSQL logs for errors, slow queries, and unusual activities. Adjust log_min_duration_statement to capture slow queries. Monitor Performance Metrics: Use tools like pg_stat_activity, pg_stat_replication, and pg_stat_progress_vacuum. Implement monitoring solutions (e.g., pgAdmin, Prometheus/Grafana, pgwatch2). --- 4. Database Optimization Index Maintenance: Identify unused or bloated indexes using pg_stat_user_indexes. Drop unused indexes and optimize queries. Query Optimization: Use EXPLAIN or EXPLAIN ANALYZE to analyze query performance. Optimize slow queries by rewriting or indexing. Partitioning: Use table partitioning for large datasets to improve performance and manageability. --- 5. Configuration Management Parameter Tuning: Adjust key parameters like work_mem, maintenance_work_mem, effective_cache_size, and max_connections. Regularly review and tune configuration files (postgresql.conf and pg_hba.conf). Autovacuum Settings: Ensure autovacuum is enabled and tuned for your workload. Monitor pg_stat_autovacuum for activity. --- 6. Security and User Management User Roles and Permissions: Regularly review roles and privileges. Remove unused accounts and enforce strong passwords. SSL/TLS Management: Enable SSL for secure connections. Rotate certificates periodically. Audit Logs: Use extensions like pgAudit for detailed logging of user activities. --- 7. Archiving and WAL Management WAL Archiving: Enable archive_mode and configure archive_command for point-in-time recovery (PITR). Cleanup Old WAL Files: Ensure old WAL files are deleted or archived properly to free up storage. --- 8. Database Growth Management Table and Index Bloat Analysis: Use pgstattuple or pg_repack to identify and manage bloat. Disk Usage Monitoring: Regularly monitor disk usage with pg_database_size() and pg_relation_size()= . --- 9. Updates and Patches Apply Updates: Keep PostgreSQL updated with the latest patches for bug fixes and security. Extension Updates: Update installed extensions (e.g., PostGIS, pglogical) to their latest versions. --- 10. Miscellaneous Tasks Replication Monitoring: Monitor replication lag and the health of replicas using pg_stat_replication. System Resource Monitoring: Monitor CPU, memory, and I/O usage to ensure the system is not bottlenecked= . Disk Defragmentation: Perform disk defragmentation (OS level) if necessary. --- These activities, when performed periodically, will help keep your PostgreSQL database healthy and performing optimally. Automating some of these tasks via scripts or tools can further reduce manual intervention. On Mon, Jan 13, 2025, 7:33=E2=80=AFAM Rajesh Kumar wrote: > What are regular housekeeping activities @Imran? > > On Mon, 13 Jan 2025, 07:27 Ron Johnson, wrote: > >> COPY exists, and is very fast. >> >> In my experience, DELETE is quite fast when you: >> 1. have a supporting index, and >> 2. don't try to delete too much in one statement. >> >> Those are the only PG comments that I needed to archive a 6TB DB down to >> 2TB. >> >> On Sun, Jan 12, 2025 at 3:42=E2=80=AFPM kasem adel wrote: >> >>> Dears , >>> >>> Appreciate your support I have 2.4 TB database and I need to implement >>> archiving solutions to prevent data growthing. >>> >>> Thanks >>> >> >> >> -- >> Death to , and butter sauce. >> Don't boil me, I'm still alive. >> lobster! >> > --00000000000095d6f2062b8f288a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Rajesh,

<= div dir=3D"auto">Housekeeping activities in PostgreSQL are essential to ens= ure the database remains high performing, secure, and reliable over time. B= elow is a categorized list of key housekeeping tasks:


---

1. Routine Maintenance Tasks

Vacuuming:

Use VACUUM to reclaim storage and update visi= bility maps.

Run VACUUM = FULL for aggressive storage reclamation (requires downtime).


Analyze S= tatistics:

Run ANALYZE t= o update query planner statistics for efficient query execution.

Use autovacuum for automatic vacuu= m and analyze.


Reindexing:

Use REINDEX to rebuild corrupted or bloated indexes.

Schedule periodic reindexing for heav= ily updated tables.


=


---

2. Backup and R= ecovery

Regular Backups:=

Use pg_basebackup or pg= _dump for full backups.

= Implement incremental backups if using third-party tools (e.g., pgBackRest,= Barman).


Test Recovery:

Periodically restore backups in a test environment to ensure recovery = readiness.




---<= /div>

3. Monitoring and Loggin= g

Review Logs:

Check PostgreSQL logs for errors,= slow queries, and unusual activities.

Adjust log_min_duration_statement to capture slow queries.


Monitor Performance Metrics:

Use tools like pg_stat_activity, pg_stat_replication, and pg_stat= _progress_vacuum.

Implem= ent monitoring solutions (e.g., pgAdmin, Prometheus/Grafana, pgwatch2).




---

4. Database Optimization

Index Maintenance:

Identify unused or bloated indexes using = pg_stat_user_indexes.

Dr= op unused indexes and optimize queries.


Query Optimization:

Use EXPLAIN or EXPLAIN ANALYZE to a= nalyze query performance.

Optimize slow queries by rewriting or indexing.

Partitioning:

Use table partitioning for larg= e datasets to improve performance and manageability.




---

=
5. Configuration Management

Parameter Tuning:

Adjust key parameters like work_mem, maintenance_work_mem, = effective_cache_size, and max_connections.

Regularly review and tune configuration files (postgresq= l.conf and pg_hba.conf).


Autovacuum Settings:
Ensure autovacuum is enabled and tuned for your w= orkload.

Monitor pg_stat= _autovacuum for activity.




---

6. Securi= ty and User Management

U= ser Roles and Permissions:

Regularly review roles and privileges.

=
Remove unused accounts and enforce strong passwords.


= SSL/TLS Management:

Enab= le SSL for secure connections.

Rotate certificates periodically.

<= div dir=3D"auto">
Audit Logs:

Use extensions like pgAudit for detailed l= ogging of user activities.




---

7. Archi= ving and WAL Management

= WAL Archiving:

Enable ar= chive_mode and configure archive_command for point-in-time recovery (PITR).=


Cleanup Old WAL Files:

Ensure old WAL files are deleted or archived properly to free up storag= e.




---

8. Database Growth Management

Table and Index Bloat Anal= ysis:

Use pgstattuple or= pg_repack to identify and manage bloat.

<= div dir=3D"auto">
Disk Usage Monitoring:

Regularly monitor disk usage wi= th pg_database_size() and pg_relation_size().




---

9. Updates and Patches

Apply Updates:

Keep PostgreSQL updated with the latest patches for bug fixes and security= .


Extension Updates:

Update installed extensions (e.g., PostGIS, pglogical) to their latest ver= sions.




---

10. Miscellaneous Tasks

Replication Monitoring:

Monitor replication lag and = the health of replicas using pg_stat_replication.

System Resource Monit= oring:

Monitor CPU, memo= ry, and I/O usage to ensure the system is not bottlenecked.


Disk Defr= agmentation:

Perform dis= k defragmentation (OS level) if necessary.



=
---

These activities, when performed periodically, will help keep you= r PostgreSQL database healthy and performing optimally. Automating some of = these tasks via scripts or tools can further reduce manual intervention.


On Mon, Jan 13, = 2025, 7:33=E2=80=AFAM Rajesh Kumar <rajeshkumar.dba09@gmail.com> wrote:

What are regular housekeeping activities = @Imran?


On Mon= , 13 Jan 2025, 07:27 Ron Johnson, <ronljohnsonjr@gmail.com> = wrote:
COPY e= xists, and is very fast.

In my=C2=A0experience, DELETE = is quite fast when you:
1. have a supporting index, and=C2=A0
2. don't try to delete too much in one statement.

=
Those are the only PG comments that I needed to archive a 6TB DB down = to 2TB.

On Sun, Jan 12, 2025 at 3:42=E2=80=AFPM kasem adel <kasemadel8@gmail.com> wrote:
Dears ,

Appreciate your support I have 2.4 TB database and I = need to implement archiving solutions to prevent data growthing.=C2=A0

Thanks=C2=A0


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--00000000000095d6f2062b8f288a--