Postgresql

Client Applications

Utilities

ecpg embedded SQL C preprocessor
pgbench run a benchmark test on PostgreSQL
pg_amcheck checks for corruption in one or more PostgreSQL databases
pg_basebackup take a base backup of a PostgreSQL cluster
pg_config retrieve information about the installed version of PostgreSQL
pg_dump extract a PostgreSQL database into a script file or other archive file
pg_dumpall extract a PostgreSQL database cluster into a script file
pg_isready check the connection status of a PostgreSQL server
pg_receivewal stream write-ahead logs from a PostgreSQL server
pg_recvlogical control PostgreSQL logical decoding streams
pg_restore restore a PostgreSQL database from an archive file created by pg_dump
pg_verifybackup verify the integrity of a base backup of a PostgreSQL cluster
psql PostgreSQL interactive terminal

Wrappers

Server Applications

initdb create a new PostgreSQL database cluster
pg_archivecleanup clean up PostgreSQL WAL archive files
pg_checksums enable, disable or check data checksums in a PostgreSQL database cluster
pg_controldata display control information of a PostgreSQL database cluster pg_controldata /var/lib/postgres/data
pg_ctl initialize, start, stop, or control a PostgreSQL server
pg_resetwal reset the write-ahead log and other control information of a PostgreSQL database cluster
pg_rewind synchronize a PostgreSQL data directory with another data directory that was forked from it
pg_test_fsync determine fastest wal_sync_method for PostgreSQL
pg_test_timing measure timing overhead
pg_upgrade upgrade a PostgreSQL server instance
pg_waldump display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster
postgres PostgreSQL database server

Manual In my system (Arch Linux), the file is /var/lib/postgres/data/postgresql.conf. FILE LOCATIONS data_directory = /var/lib/postgres/data hba_file = /var/lib/postgres/data/pg_hba.conf ident_file = /var/lib/postgres/data/pg_ident.conf external_pid_file = '' CONNECTIONS AND AUTHENTICATION RESOURCE USAGE (except WAL) shared_buffers = 128MB Resource Usage The default shared_buffers = 128MB should be raised to between 15% and 25% of available RAM according to How to tune PostgreSQL for memory Logging Reporting deadlocks and resource exhaustion. — PostgreSQL Logging The defaults are:

other-commands

concurrency

Concurrency Control

graphs

Postgresql uses WITH RECURSIVE to traverse graphs stored as databases. UNION ALL vs UNION The result of UNION does not contain any duplicate rows unless the ALL option is specified. ALL prevents elimination of duplicates. (Therefore, UNION ALL is usually significantly quicker than UNION; use ALL when you can.) DISTINCT can be written to explicitly specify the default behavior of eliminating duplicate rows. — UNION Clause Sometimes, using UNION instead of UNION ALL can accomplish this by discarding rows that duplicate previous output rows.

types

The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage. — Type Casts manual Numeric Monetary Character Binary Date/Time Boolean AND, OR, NOT Comparison Enumerated Geometric Network Address Bit String Text Search UUID XML JSON Functions Arrays Composite Range Domain Object Identifier pg_lsn Pseudo

plpgsql

CREATE OR REPLACE FUNCTION My Style RETURN vs OUT parameters Postgresql functions can either be written CREATE OR REPLACE FUNCTION add(a integer, b integer) RETURNS integer LANGUAGE plpgsql AS $code$ BEGIN RETURN a + b; END; $code$; Alternatively, it can be written in a more Prologish style with parameters labeled IN or OUT (or INOUT). CREATE OR REPLACE FUNCTION add(IN a integer, IN b integer, OUT c integer) LANGUAGE plpgsql AS $code$ BEGIN c = a + b; END; $code$; Since most of the functions for my project involve message broker patterns message_broker(IN json_in JSONB, OUT json_out JSONB), I find the Prologish style better.

Frontier Software