Alex's Slip-box

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

psql

Created on September 22, 2023

Updated on May 17, 2024

Tags: postgres

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

See also postgresql.conf See also Row locking

# Authentication

There are several methods. See also https://stackoverflow.com/a/6524167

# Using env vars

  • PGUSER
  • PGPASSWORD

# .psqlrc

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

See also

# Logs

# Where are the logs?

See also https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE 

SHOW log_destination;

log_destination
-----------------
stderr
(1 row)

stderr is default, but other options are available. See docs above. There is also a log collector that will capture log entries and write to a file. So determine if logs are being collected: 

SHOW log_collector;

logging_collector
-------------------
on
(1 row)

If the log collector isn’t on, see postgresql.conf for how to turn it on. If the collector is on, find the current collection file with: 

SELECT pg_current_logfile();

This will print a path relative to the data directory. To find the location of the data directory: 

SHOW data_directory;

Putting those two things together, you can tail the logs.

# 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. 

BEGIN;

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

COMMIT;

# Miscellaneous

# PG_BACKEND_PID()

psql process ID 

SELECT PG_BACKEND_PID();

# 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. If you need to supply a password for the user supplied by the -U flag, you can prefix the command with PGPASSWORD=mypassword 

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