Table of Contents
- Meta commands
- Restore a dump
- System catalog tables
- Terminate queries
There are several methods. See also https://stackoverflow.com/a/6524167
# Using env vars
To me the most important one is
\pset null '(null)' in order to distinguish
between empty string and null.
# Meta commands
…followed by the database name to connect to it.
list installed extensions
Run the last command. Also use up arrow enter.
List the databases on the server
…followed by a filename, to toggle writing query output to that file.
# \pset format wrapped
Turn on wrapping of the results
Prints history of commands run previously.
ctrl r “reverse search” is available
Toggles timing which prints the time each command takes
Expanded display to make output more readable
# Restore a dump
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 * FROMthese tables to get system information.
Here’s just a few for reference:
Contains installed extensions
Database activity. This will show the
pid of the activity owner. Use the
to cancel or terminate queries.
# Terminate queries
Find the pid by querying
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;
psql process ID
# Find PostgreSQL config file
This will print the file path to the config file.
See also postgresql.conf
# Find the data directory
Commands can be passed to
psql using the
-c switch, which outputs the results
psql -h database -d myapp_development -c "select * from users" > results
-f followed by a file path to run commands from a file.
psql -f insert_widgets.sql