OnlineSkillBuilder for Postgresql DBA

  Just yesterday I was reading a tutorial of sorts about how to design a cloud service like Dropbox. Among the many components required to build a large scale service like that, was a metadata database. It would be used to store information about users, their workspaces, files they want to upload, etc. Since it’s mostly just text information and a downstream service needed it to provide a consistent view of the files a user is trying to sync, a relational database would be the perfect choice owing to it’s ACID properties. Now I had heard about ACID properties for as long as I can remember but I wanted to refresh those concepts. So I took a detour from system design to database design. The  Isolation  aspect of it really got to me and I wanted to know more about it and more so in the context of postgres. This is an attempt to explain some of what I learned in the process. Isolation Levels Isolation is the ability of the transactions to operate concurrently without causing ...

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