PostgreSQL Reference

Contents

• Install Location / Data Directory / Data Area
• Postgres Utilities
  • createdb
  • dropdb
  • initdb
  • pg_config
  • pg_ctl
  • pg_dump
  • pg_restore
  • postgres
  • psql
• postgres-common Utilities
  • pg_conftool
  • pg_createcluster
  • pg_ctlcluster
  • pg_dropcluster
  • pg_lsclusters
  • pg_renamecluster
  • pg_upgradecluster
  • pg_virtualenv
• 3rd Party Tools
  • pgbackrest
    • Selected Global Options
    • backup
    • info
  • pg_format
    • Selected Options
    • Meta-Commands
• Environment Variables
• Tips
• Resources
• Frequently Asked Questions (FAQs)
  • Why does INSERT return lines INSERT 0 1?
  • Why is it called “Postgres”? Where did the name “Postgres” come from? Where did the name “PostgreSQL” originate?
  • How to stop the postgres process and disable it from running at startup on macOS?
  • How can I tell what locale my database is in?
• Database Management System (DBMS) Terminology
• postgres-study
  • README.md
  • queries/delete.sql
  • queries/insert.sql
  • queries/select-like.sql

Install Location / Data Directory / Data Area

Postgres Utilities

createdb

Creates a new PostgreSQL database. The database user who executes this command becomes the owner of the new database, unless -O/--owner is specified.

createdb [connection-option...] [option...] [dbname [description]]

createdb --username postgres --echo mydb
Password:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE mydb;

dropdb

Destroys an existing PostgreSQL database. Wrapper around DROP DATABASE.

dropdb [connection-option...] [option...] dbname

initdb

Creates a new PostgreSQL database cluster. Use this to create the directories where cluster data will live, etc.

From the initdb(1) man page:

initdb must be run as the user that will own the server process, because the server needs to have access to the files and directories that initdb creates. Since the server cannot be run as root, you must not run initdb as root either. (It will in fact refuse to do so.)

For macOS:

mkdir path/to/data
chown -R postgres:daemon path/to/data
sudo -u postgres initdb --data-checksums --auth scram-sha-256 --pwprompt --pgdata path/to/data

Notes:

• we change the ownership of path/to/data because we want postgres to own the server process and therefore have access to the files and directories that initdb creates
• --auth and --pwprompt are passed just to have sensible defaults; you will likely modify your pg_hba.conf file after init appropriate to your usage
• --data-checksums is used on advice that the performance cost (seen referenced to be ~1-2%) is worth the peace of mind

pg_config

pg_config provides information about the installed version of PostgreSQL.

pg_config [OPTION]...

With no arguments, all known items are shown.

pg_ctl

Initialize, start, stop, or control a PostgreSQL server.

sudo -u postgres pg_ctl init --pgdata path/to/data
sudo -u postgres pg_ctl start --pgdata path/to/data --log path/to/postgres.log
sudo -u postgres pg_ctl stop --pgdata path/to/data

pg_dump

• for a single database
• use the format option (and choose the one that works with pg_restore

Therefore, it is wise to run ANALYZE after restoring from a dump file to ensure optimal performance; see Section 24.1.3 and Section 24.1.6 for more information.

pg_restore

postgres

PostgreSQL database server

psql

PostgreSQL interactive terminal. Reads from ~/.psqlrc on startup, if it exists.

Connect to the local cluster as user postgres:

psql --username postgres

Connect to the local cluster as user postgres, connecting to database mydb:

psql --username postgres --dbname mydb

postgres-common Utilities

pg_conftool

pg_createcluster

pg_ctlcluster

Start, stop, restart, reload a PostgreSQL cluster.

You have to start this program as the user who owns the database cluster or as root.

pg_dropcluster

pg_lsclusters

pg_renamecluster

pg_upgradecluster

pg_virtualenv

3rd Party Tools

pgbackrest

RTO vs. RPO is a great way to frame the problem

Selected Global Options

backup

|---------------------|---| | --type | |

Create a diff backup:

sudo -u postgres pgbackrest             \
  --config=/path/to/pgbackrest.conf     \
  --config-include-path=/path/to/conf.d \
  --log-level-console=info              \
  --log-path=/path/to/log/pgbackrest    \
  --stanza=demo                         \
  --type=diff

info

Gives info on current state of backups for a stanza:

sudo -u postgres pgbackrest             \
  --config=/path/to/pgbackrest.conf     \
  --config-include-path=/path/to/conf.d \
  --log-level-console=info              \
  --stanza=demo                         \
  info

pg_format

SQL formatter / beautifier.

Selected Options

Meta-Commands

List tables in mydb:

mydb=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | cities  | table | postgres
 public | weather | table | postgres
(2 rows)

List tables in mydb with additional detail:

mydb=# \dt+
                                      List of relations
 Schema |  Name   | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+---------+-------+----------+-------------+---------------+------------+-------------
 public | cities  | table | postgres | permanent   | heap          | 8192 bytes |
 public | weather | table | postgres | permanent   | heap          | 8192 bytes |
(2 rows)

Environment Variables

Tips

On role names:

• prefer underscores to dashes to prevent need of quoting
• consider pattern: {app}_{service}_{access}; e.g. foo_grpc_rw

Resources

• Releases / Release Notes
• Official Postgres Wiki
  • Official Postgres Frequently Asked Questions (FAQ)
  • Don’t Do This A short list of common mistakes.
• THE DESIGN OF POSTGRES (PDF) by Michael Stonebraker and Lawrence A. Rowe
• The POSTGRES Data Model (PDF) by Lawrence A. Rowe and Michael Stonebraker
• THE DESIGN OF THE POSTGRES STORAGE SYSTEM by Michael Stonebraker

Frequently Asked Questions (FAQs)

Why does INSERT return lines INSERT 0 1?

This is a command tag of the form:

INSERT oid count

Where oid refers to the deprecated object identifier and count refers to the number of rows inserted. See more detail in the INSERT documentation.

Why is it called “Postgres”? Where did the name “Postgres” come from? Where did the name “PostgreSQL” originate?

Chronological sequence:

• Ingres: INteractive Graphics REtrieval System.
• Postgres, or post inGRES (after inGRES)
• Postgres95, which was the cleaned up ANSI C version released to the web
• PostgreSQL, reflecting the connection / support of SQL

How to stop the postgres process and disable it from running at startup on macOS?

Determine the .plist defining the launchd service:

ls -halt /Library/LaunchDaemons/

Let’s assume it is /Library/LaunchDaemons/postgresql-16.plist.

Tell launchd to stop the service and remove it from the current boot session:

sudo launchctl bootout system /Library/LaunchDaemons/postgresql-16.plist

Tell launchd to prevent the service from running at next boot session:

sudo launchctl disable system/postgresql-16

Confirm it worked:

sudo launchctl print-disabled system

How can I tell what locale my database is in?

SELECT datname, datcollate, datctype
FROM pg_database
WHERE datname = current_database();

Database Management System (DBMS) Terminology

atomic

either happens completely or not at all

catalog

database; used in the SQL standard

cluster

the collection of databases managed by a single PostgreSQL server instance

dump

generate a file with SQL commands that, when fed back into the server, will recreate the database in the same state as it was at the time of the dump

durability

a guarantee of the recording of committed transactions even if the server crashes or loses power

function, window function

performs a calculation across a set of table rows that are somehow related to the current row

join

Queries that access multiple tables (or multiple instances of the same table) at one time

partition

splitting a single table into smaller tables

qualify

adding more specificity to a field or query; e.g. using SELECT tbl1.foo instead of SELECT foo; or adding a clause to a query (like WHERE) that restricts the results

query

how to retrieve data from a table

the process of retrieving, or the command to retrieve, data from a database

relation

essentially a mathematical term for a table

table

a named collection of rows

table expression

an expression that computes a table

transaction

bundles multiple steps into a single, all-or-nothing operation

transaction block

a group of statements surrounded by BEGIN and COMMIT

update

modifying data that is already present in the database

xact

short for “transaction”

postgres-study

README.md

# postgres-study

personal study area for Postgres

queries/delete.sql

DELETE FROM t1
WHERE
  col_a = 1;

queries/insert.sql

INSERT INTO t1 (col_a)
VALUES
('foo')
, ('bar')
, ('baz');

queries/select-like.sql

SELECT col_a
FROM
  t1
WHERE
  col_a LIKE '%pattern%';