PostgreSQL

postgres.md

Dump/Restore

Dump the database schema (without data)

pg_dump -U <user> -Fc -s <databaseName> > <output>
pg_dump -U <user> -Fc -s <databaseName> -f <output>

Dump the full database (with data)

pg_dump -U <user> -Fc <databaseName> > <output>
pg_dump -U <user> -Fc <databaseName> -f <output>

Dump all the databases

pg_dumpall -U <user> -c -f <output>

Restore a dump

psql <databaseName> < <dumpFile>
psql <databaseName> -f <dumpFile>

Clone database

pg_dump -c -U <user> <databaseName> | psql -d <newDatabaseName> -U <user>

Upgrade

For example from version 11 to version 13

service postgresql stop
pg_dropcluster 13 main
pg_upgradecluster 11 main
service postgresql start

pgcli

Dump a table

\copy <tableName> to 'myDump.dump'

Dump data to stdout

\copy (select * from <tableName> where id = 'myId') to stdout

Restore a dump

\copy <tableName> from 'myDump.dump'

Various commands

• Toggle pager in psql/pgcli

\pset pager

• Change pager

\pset pager less

• Toggle expanded display

\x

• Delete all data in some tables

truncate table1, table2 cascade

• kill all connections easily

/etc/init.d/postgres restart

SQL Requests

• Search for data created in the last X hours/minutes...

select * from <table> where created_at > now() - interval '2 days 2 hours 20 minutes'

Debugging

PostgreSQL offers some tools enabled via its configuration (/etc/postgresql/<versionNumber>/postgresql.conf)

• log_lock_waits: Enabling this will instruct the Deadlock Detector to log whenever a statement exceeds deadlock_timeout. There is no performance overhead enabling this, since the Deadlock Detector should be running anyway, and if it is, then it’s practically free.
• log_min_duration: This will log queries running for more than X ms
• auto_explain: This is actually a number of configurations (auto_explain.log_min_duration, auto_explain.log_analyze etc). They control when and how PostgreSQL will automatically perform an EXPLAIN on running queries. Those are useful as a precaution too, to make sure that poorly performing statements will leave traces & query plans for you to debug.
• log_statement: This is pretty useful. It can enable logging all / most / error statements etc. If you want to find out if something’s wrong with your database, it’s a common practice to set this to all for some time, gather the output and analyze it with a tool like pgBadger. Logs can be displayed with the following command

# Logs are stored in "/var/log/postgresql/postgresql-<versionNumber>-main.log". For example:
sudo less +f /var/log/postgresql/postgresql-11-main.log

Tools

HypoPG

• description: HypoPG is a PostgreSQL extension adding support for hypothetical indexes.