Created
September 22, 2019 17:57
-
-
Save EileenJuergens/e599dd9e118e9940a0228e72feb1b6b3 to your computer and use it in GitHub Desktop.
Postgres
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
psql -U postgres // start postgres shell or command line interface. | |
Some interesting flags (to see all, use -h or --help depending on your psql version): | |
-E: will describe the underlaying queries of the \ commands (cool for learning!) | |
-l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS) | |
Most \d commands support additional param of __schema__.name__ and accept wildcards like *.* | |
\q: Quit/Exit | |
\l list all the databases | |
\c __database__: Connect to a database | |
\d __table__: Show table definition including triggers | |
\dt __table__: show specific table without showing the data | |
\l: List databases | |
\dt list tables | |
\dy: List events | |
\df: List functions | |
\di: List indexes | |
\dn: List schemas | |
\dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones) | |
\dv: List views | |
\df+ __function__ : Show function SQL code. | |
\x: Pretty-format query results instead of the not-so-useful ASCII tables | |
\copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV | |
User Related: | |
\du: List users | |
\du __username__: List a username if present. | |
create role __test1__: Create a role with an existing username. | |
create role __test2__ noinherit login password __passsword__;: Create a role with username and password. | |
set role __test__;: Change role for current session to __test__. | |
grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__. | |
Configuration | |
Service management commands: | |
sudo service postgresql stop | |
sudo service postgresql start | |
sudo service postgresql restart | |
Changing verbosity & querying Postgres log: | |
1) First edit the config file, set a decent verbosity, save and restart postgres: | |
sudo vim /etc/postgresql/9.3/main/postgresql.conf | |
# Uncomment/Change inside: | |
log_min_messages = debug5 | |
log_min_error_statement = debug5 | |
log_min_duration_statement = -1 | |
sudo service postgresql restart | |
Now you will get tons of details of every statement, error, and even background tasks like VACUUMs | |
tail -f /var/log/postgresql/postgresql-9.3-main.log | |
How to add user who executed a PG statement to log (editing postgresql.conf): | |
log_line_prefix = '%t %u %d %a ' | |
There are many CREATE choices, like CREATE DATABASE __database_name__, CREATE TABLE __table_name__ ... | |
Handy queries | |
SELECT * FROM pg_proc WHERE proname='__procedurename__': List procedure/function | |
SELECT * FROM pg_views WHERE viewname='__viewname__';: List view (including the definition) | |
SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));: Show DB table space in use | |
SELECT pg_size_pretty(pg_database_size('__database_name__'));: Show DB space in use | |
show statement_timeout;: Show current user's statement timeout | |
SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';: Show table indexes | |
SELECT | |
t.relname AS table_name, | |
i.relname AS index_name, | |
a.attname AS column_name | |
FROM | |
pg_class t, | |
pg_class i, | |
pg_index ix, | |
pg_attribute a, | |
pg_namespace n | |
WHERE | |
t.oid = ix.indrelid | |
AND i.oid = ix.indexrelid | |
AND a.attrelid = t.oid | |
AND a.attnum = ANY(ix.indkey) | |
AND t.relnamespace = n.oid | |
AND n.nspname = 'kartones' | |
ORDER BY | |
t.relname, | |
i.relname | |
Execution data: | |
Queries being executed at a certain DB: | |
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query | |
FROM pg_stat_activity | |
WHERE datname='__database_name__'; | |
Get all queries from all dbs waiting for data (might be hung): | |
SELECT * FROM pg_stat_activity WHERE waiting='t' | |
Currently running queries with process pid: | |
SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, | |
pg_stat_get_backend_activity(s.backendid) AS current_query | |
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s; | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment