Ansible is a great automation tool to manage operating systems, but also to manage database like PostgreSQL. Many Ansible modules are available to create playbooks which execute various database administration tasks. In this article we will have a closer look how to ensure that a default database has been created a set of configured extensions…
At my recent job, I was asked to create a backup procedure, which would dump a PostgreSQL schema to a compressed file and which was able to create weekly and daily backups.The backups had to be full backups each time a backup was made and the amount of daily and weekly backups should be defined…
Not using indexes or huge tables without indexes, can have a very negative impact on the duration of a SQL query. The query planner will decide to make a sequence scan, which means that the query will go through the table sequentially to search for the required data. When this table is only 100 rows…
Working on large database systems, with many tables and many indexes, it is easy to loose the overview on what is actually being used and what is just consuming unwanted disk space.If indexes are not closely monitored, they could end up using undesired space and moreover, they will consume unnecessary CPU cycles. Statistics about indexes…
Recently I’ve discovered some very interesting new features in the PostgreSQL 9.3 database.First of all, a new data type has been introduced: JSON. Together with this new data type, new functions were also introduced. These new features now simply for instance saving web forms in your Postgresql database. Or actually any kind of dynamic data,…
Nagios plugin script written in Bash to check the master-slave replication in PostgreSQL (tested on PostgreSQL 9.2.4) (executed on the slave). The script will report how many bytes the slave server is behind, and how many seconds ago the last replay of data occurred. The script must be executed as ‘postgres’ user. #!/bin/bash # $Id:…
