POSTGRESQL CHEAT SHEET

-



Comments

Post a Comment

Popular posts from this blog

PostgreSQL - Architecture

-
Image
When you start PostgreSQL`s instance (via pg_ctl start) the main process of the databases is started. In the past versions it was known as the postmaster process and now days it is called postgres. When this processes is started it allocates memory for two main things : shared memory and other background processes. The shared memory is a memory that the database uses mainly for cashing. Its main goal is to reduce the I/O operations and as a result of that improve the performance of the database. The other processes that the postgres process starts are responsible for the ongoing work of the database.  PostgreSQL`s architecture is based on a client/server communication. In the server side there is a server process that manages the files, database operations, connection and so on.. On the client side there is a tool/program which can be a graphical tool (gui)  like pgAdmin or a command line tool(cli) like psql  that is repnsible for connection the database and all...
Read more

POSTGRESQL DATABASE MAINTENANCE

-
  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 l...
Read more

Job scheduler for PostgreSQL "pg_cron"

-
Image
What is pg_cron   : -   pg_cron is a simple cron-based job scheduler for   PostgreSQL (9.5 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 . Why We need it ? Running periodic maintenance jobs or removing old data is a common requirement in PostgreSQL. A simple way to achieve this is to configure cron or another external daemon to periodically connect to the database and run a command. Let's see how it's works  Step 1 :-  For implementing/Installation of pg_cron you need to download source code from git Dowload link  export PATH=/usr/local/pgsql/bin:$PATH wget https://github.com/citusdata/pg_cron/archive/master.zip unzip master cd pg_cron-master/ make make install    Step 2 : - To start the pg_cron background worker when PostgreSQL starts, you need to add pg_cron to  shared_preload_libraries   in post...
Read more