SRI SRI's Postgresql Knowledge

  Routine backup of daily database maintenance work , regular “clean up” database, periodic log file management check_postgres can be used to detect database health and report abnormal conditions 1. Daily cleaning PostgreSQL database requires periodic cleaning and maintenance. For many installations, it is sufficient to have the automatic cleanup daemon perform cleanup 1.1. Basic knowledge of cleaning The PostgreSQL VACUUM command must periodically process each table for several reasons: 1.  Restore or reuse the disk space occupied by updated or deleted rows. 2.  Update the data statistics used by the PostgreSQL query planner. 3.  Update the visibility map, it can speed up the scan using only the index. 4.  Protect old data from being lost due to transaction ID rewinding or multi-transaction ID rewinding. There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space but runs slower. VACUUM FULL requires an exclusive lock on the table it work

  pg_cron is a simple cron-based job scheduler for PostgreSQL (10 or higher) that runs inside the database as an extension. It uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database: You can also install pg_cron by building it from source: git clone https://github.com/citusdata/pg_cron.git cd pg_cron # Ensure pg_config is in your path, e.g. -bash-4.2$ export LD_LIBRARY_PATH=/u01/app/postgres/13.3/lib:$LD_LIBRARY_PATH -bash-4.2$ export PATH=/u01/app/postgres/13.3/bin:$PATH -bash-4.2$ cd pg_cron -bash-4.2$ make && sudo PATH=$PATH make install gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -std=c99 -Wall -Wextra -Werror -Wno-unused-parameter -Wno-implicit-fallthrough -Iinclude -I/u01/app/postgres/13.3/include -I. -I./ -I/u01/app/postgres/13.3