PostgreSQL utility tools

Several PostgreSQL utility tools are wrappers around SQL constructs. These tools are used to create and drop databases, users, and languages. For example, the dropdb and createdb commands are wrappers around DROP DATABASE [ IF EXISTS ] and CREATE DATABASE, respectively.

PostgreSQL also provides tools to maintain the system objects, mainly clusterdb and reindexdb. clusterdb is a wrapper around the CLUSTER statement, which is used to physically reorder the table based on certain index information. This can increase database-performance read operations due to the locality of reference principles, mainly the spatial locality. Clustering the table helps to retrieve data from adjacent storage blocks and thus reduces the hard-disk-access cost. 

reindexdb is a wrapper around reindex SQL statement. There are several reasons to reindex an index, for example, the index might get corrupted—which rarely happens in practice—or bloated. Index bloat happens when the index size grows due to sparse deletion. Index bloat affects performance since index scans take more time due to reading more disk blocks than needed.

In addition to the previous tools, PostgreSQL also provides tools for the following:

• Physical backup: This is used to back up PostgreSQL's database files. This method is a very fast way to create a backup, but the backup can only be restored on compatible PostgreSQL versions. The pg_basebackup tool is used for this purpose. pg_basebackup is often used to set up streaming replication as the standby is a clone of a master.
• Logical backup: This is used to back up the database objects in the form of SQL statements, such as CREATE TABLE, CREATE VIEW, and COPY. The generated backup can be restored on different PostgreSQL cluster versions, but it's slow. The pg_dump and pg_dumpall tools are used to dump a single database or a database cluster, respectively. pg_dump also can be used to dump a specific relation or a set of relations and schemas. Also, it has a lot of features, such as dumping schemas only or data only. pg_dumpall internally uses pg_dump to dump all databases on the cluster. Finally, the pg_restore tool is used to restore the dumps generated by pg_dump or pg_dumpall.