Alex's Slip-box

These are my org-mode notes in sort of Zettelkasten style


:ID: 4D90F42B-B4DA-4CDA-9885-B8FF372FDB72

See also postgresql.conf See also Row locking

# Authentication

There are several methods. See also

# Using env vars


# .psqlrc

To me the most important one is \pset null '(null)' in order to distinguish between empty string and null.

See also

# Meta commands

# \c

…followed by the database name to connect to it.

# \dx

list installed extensions

# \dv

list views

# \g

Run the last command. Also use up arrow enter.

# \l

List the databases on the server

# \o

…followed by a filename, to toggle writing query output to that file.

# \pset format wrapped

Turn on wrapping of the results

# \s

Prints history of commands run previously. TIP: ctrl r “reverse search” is available

# \timing

Toggles timing which prints the time each command takes

# \x

Expanded display to make output more readable

# Restore a dump

See also

psql --set ON_ERROR_STOP=on --quiet \
  --no-psqlrc \
  --output /dev/null \
  dbname \
  --file database_dump.sql

# System catalog tables

  • These are tables that begin with pg_* (use tab completion to see the list)
  • SELECT * FROM these tables to get system information.

Here’s just a few for reference:

# pg_extension

Contains installed extensions

# pg_stat_activity

Database activity. This will show the pid of the activity owner. Use the pid to cancel or terminate queries.

# pg_locks

# Terminate queries

Find the pid by querying pg_stat_activity, then…

  • SELECT pg_cancel_backend(pid) (graceful)
  • SELECT pg_terminate_backend(pid)

# Transactions

Do things in transactions and validate them before committing them. Use rollback when things get weird.


-- Insert, update, modify schema, or whatever
-- Verify it worked. If it didn't Use `ROLLBACK;`.


# Miscellaneous


psql process ID


# Find PostgreSQL config file

This will print the file path to the config file.

SHOW config_file;

See also postgresql.conf

# Find the data directory

SHOW data_directory;

# Scripting

Commands can be passed to psql using the -c switch, which outputs the results to stdout

psql -h database -d myapp_development -c "select * from users" > results

Use the -f followed by a file path to run commands from a file.

psql -f insert_widgets.sql

Search Results