OnlineSkillBuilder for Postgresql DBA

  Recently I found huge archive logs generation on one of the servers(for my client). Around 3500 files per day and it needs 3500 * 16 MB = 54GB space every day and space is low on the server. First thing came to my mind was checkpoint parameters, I had a look at them and they were at default. Hmm, so had changed them as below: checkpoint_segments = 180 checkpoint_timeout = 30min checkpoint_completion_target = 0.9  Monitored for couple of days and found that archive logs generation decreased to 50%(around 1700 files).  With the above parameter settings, archive log generation should be minimal. However if not, Here are some general suggestions to look at: -- Make sure that you don't UPDATE a row to the same values it already has. The row will be logged even if old and new values match. One way to handle this is to make sure that any UPDATE statements which might set a column to its existing value include in the WHERE clause a test that oldvalue IS DISTINCT FROM new value....

  In this blog post, we dive into the nuts and bolts of setting up Streaming Replication (SR) in PostgreSQL. Streaming replication is the fundamental building block for achieving high availability in your  PostgreSQL hosting , and is produced by running a master-slave configuration. Master-Slave Terminology Master/Primary Server The server that can take writes. Also called read/write server. Slave/Standby Server A server where the data is kept in sync with the master continuously. Also called backup server or replica. A warm standby server is one that cannot be connected to until it is promoted to become a master server. In contrast, a hot standby server can accept connections and serves read-only queries. For the rest of this discussion, we will be focusing only on hot standby servers. Data is written to the master server and propagated to the slave servers. In case there are an issue with the existing master server, one of the slave servers will take over and conti...

  pg_ctl -- initialize, start, stop, or control a PostgreSQL server pg_ctl [ status | start | stop | restart | reload | init ] [-U username] [-P password] [--help] pg_ctl --help pg_ctl --version pg_ctl init[db] [-s] [-D datadir] [-o initdb-options] pg_ctl -D /usr/local/pgsql/data initdb pg_ctl start [-w] [-t seconds] [-s] [-D datadir] [-l filename] [-o options] [-p path] [-c] pg_ctl start pg_ctl -w start pg_ctl -D /data/postgres/9.6/5304 start pg_ctl start -l /data/logs pg_ctl -o "-F -p 5433" start pg_ctl stop [-W] [-t seconds] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] pg_ctl stop pg_ctl stop -D /Library/PostgreSQL/9.6/data pg_ctl --pgdata /usr/local/var/postgres stop pg_ctl stop -m fast pg_ctl stop -m smart pg_ctl stop -m immediate pg_ctl stop -w -l /home/pg/logs pg_ctl -D /usr/local/pgsql/data stop -m fast pg_ctl status [-D datadir] pg_ctl status pg_ctl -D /data/postgres/9.6/5301 status pg_ctl -D /opt/data/pg9-9.5.0/data/5361 status  pg_ctl reload [-s] [-D d...

  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...

  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/...

  POSTGRESQL BACKUP AND RECOVERY POSTGRESQL BACKUP AND RECOVERY Hot Backup:- Note:- 1. Database is must online(No Shutdown). 2.Archive mode must enabled      archive_command = 'cp %p /archive/%f'      archive_mode =on      wal_level=archive  Step1:- Go to database Level to find databse id ,its helpfull for easily find os level folders  

As a DBA, we often encounter scenarios when you need to track PostgreSQL database performance by analyzing workloads and identify bottlenecks if any. There comes  pgbadger  – an open source log analyzer for PostgreSQL. It parse the PostgreSQL log files and gives you a nice overview of the PostgreSQL cluster including various query metrics like number of query executions, their types and duration, session details, Locks statistics, warnings, errors and other event patterns etc. All of these details will be available in a nice HTML format on your web browser. Installation: Prerequisite: The  pgbadger  is written in pure  Perl  and uses a JavaScript library ( flotr2 ) to draw graphs. Hence, you need to ensure that a modern Perl distribution is available in your system. Charts are rendered using a JavaScript library and Your web browser will do all the work. Nothing additional is required here. Use the below command to install  perl  if it is not alre...