Skip to content

Instantly share code, notes, and snippets.

@ma0c
Forked from Kartones/postgres-cheatsheet.md
Last active March 6, 2018 09:46
Show Gist options
  • Save ma0c/c444fa967a570dcfbedcb96b4a0bd909 to your computer and use it in GitHub Desktop.
Save ma0c/c444fa967a570dcfbedcb96b4a0bd909 to your computer and use it in GitHub Desktop.
PostgreSQL command line cheatsheet

PSQL

Palabras mágicas:

psql -U postgres

Si ejecutas con la bandera -E, describirá las consultas subyacentes que se realizan con los comandos \ (genial para aprender).

La mayora de comandos \d soportan un parámetro adicional del __schema__.name__ y aceptan comodines como *.*

  • \q: Salir
  • \c __database__: Conectarse a una base de datos
  • \d __table__: Mostrar la definición de una tabla, incluyendo disparadores
  • \dt *.*: Lista todas las tablas de todos los esquemas (Si no se coloca . solamente se buscaran los pertenecientes ap SEARCH_PATH)
  • \l: Lista de las bases de datos
  • \dn: Lista de los esquemas
  • \df: Lista de las funciones
  • \dv: Lista de las vistas
  • \df+ __function__ : Muestra el código SQL de una funcin
  • \x: Da una salida mas elaborada mostrando los resultados de la consulta

Relacionadas con usuarios:

  • \du: Lista de usuarios
  • \du __username__: Lista la información de un usuario si está presente
  • create user __username__ with password 'password': Crea un usuario username con contrasea password
  • create role __test1__: Crea un rol con un nombre de usuario específico
  • create role __test2__ noinherit login password __passsword__;: Crea un rol con un usuario y contraseña
  • set role __test__;: Cambia el rol de la sesión actual a __test__
  • grant __test2__ to __test1__;: Permite a __test1__ definir su rol como __test2__

Configuración

  • Comandos de gestin de servicio:
sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart
  • Cambiar la verbosidad del log de Postgtgres:  
    1) Primero edita el archivo de configuración, defone un log que te satisfaga, luego guarda y reinicia postgres:
sudo vim /etc/postgresql/9.x/main/postgresql.conf

# Uncomment/Change inside:
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1

sudo service postgresql restart

 2) Ahora puedes ver toneladas de detalles por cada sentencia, error e incluso tareas ejecutándose en segundo plano.

tail -f /var/log/postgresql/postgresql-9.x-main.log

 3) Se puede añadir quien ejecuto la sentencia en el log, editando el archivo postgresql.conf:

log_line_prefix = '%t %u %d %a '

Consultas útiles

  • SELECT * FROM pg_proc WHERE proname='__procedurename__': Lista los procedimientos/funciones
  • SELECT * FROM pg_views WHERE viewname='__viewname__';: Lista las vistas, incluidas las definiciones
  • SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));: Muestra la el espacio de tablas en uso.
  • SELECT pg_size_pretty(pg_database_size('__database_name__'));: Muestra el espacio de base de datos en uso
  • show statement_timeout;: Muestra el tiempo límite de finalización de la consulta.
  • SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';: Muestra los índices de las tablas
  • Obtener todos los índices de todas las tablas de un esquema:
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
  • Ejecutar datos:  - Consutas a ser ejecutadas en una base de datos específica:
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query 
  FROM pg_stat_activity 
  WHERE datname='__database_name__';
  • Obtener todas las consultas a todas las bases de datos esperando por datos.
SELECT * FROM pg_stat_activity WHERE waiting='t'
  • Consultas ejecutandose con su identificador de procesos:
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;

Casting:

  • CAST (column AS type) or column::type
  • '__table_name__'::regclass::oid: Obtener el identificador dado el nombre de la tabla

Herramientas

$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc

Comandos útiles

  • ALTER USER user_name WITH PASSWORD 'new_password'; Cambia la contraseña de un usuario
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment