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, dropdb and createdb commands are wrappers around DROP DATABASE [ IF EXISTS ] and CREATE DATABASE respectively.

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

reindexdb tool is a wrapper a round 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.

In addition to the previous ones, postgreSQL provides tools for the following:

• Physical backup: This is used to back up the PostgreSQL database files by taking a hard disk snapshot. This method is a very fast way to create a backup, but the backup can only be restored on compatible PostgreSQL versions. The tool pg_basebackup is used for this purpose. The pg_basebackup is often used for setting up streaming replication as the slave 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, COPY, and so on. The generated backup can be restored on different PostgreSQL cluster versions, but it is slow. The tools pg_dump, pg_dumpall are used to dump a single database or a database cluster respectively. pg_dump also can be used to dump a specific relation or set of relation and schema. Also it has a lot of features such as dumping schema 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. 

pg_dump does not dump the CREATE DATABASE statement command. For example, one can dump a database called customer to another database called client. Due to this, if you have a special privileges assigned to the database such as CONNECT, you need to assign these privileges to the new database.