Skip to content

Latest commit

 

History

History
117 lines (98 loc) · 2.13 KB

postgres.md

File metadata and controls

117 lines (98 loc) · 2.13 KB

PostgreSQL commands

General

  • Access the PostgreSQL
    psql -U [username]
    psql -h [host] -p [port] -U [username] [database]
  • Connect to a specific database
    \c [database_name]
  • List all databases
    \l
  • List schemas
    \dn
  • List all schemas
    \dn *.*
  • List all schemas to get more information
    \dn+
  • List all tables
    \dt
  • List all tables from a schema
    \dt [schema].*
  • List all tables to get more information
    \dt+
  • Get detailed information on a table
    \d+ [table_name]
  • List all users
    \du
  • Run sql file
    \i [file_path.sql]
   psql -h <host> -p <port> -U <user> -d <db> -f <sql file>
   psql -h monitoring-prd.cluster-c.us-east-1.rds.amazonaws.com -p 5432 -U user_prd -d prd -f result_2024_10.sql
  • List tables in lock
    SELECT
      l.locktype,
      t.relname,
      l.page,
      l.virtualtransaction,
      l.pid,
      l.mode,
      l.granted
    FROM pg_locks l,
         pg_stat_all_tables t
    WHERE l.relation = t.relid
    AND t.relname NOT IN ('pg_class', 'pg_index', 'pg_namespace')
    ORDER BY relation ASC;
  • Stop/Kill a query
    • This basically "starts" a request to terminate gracefully, which may be satisfied after some time, though the query comes back immediately.
    SELECT pg_cancel_backend([pid of the process])
  • If the process cannot be killed, try:
    SELECT pg_terminate_backend([pid of the process])
  • Grant CONNECT to the database
    GRANT CONNECT ON DATABASE database_name TO username;
  • Grant USAGE on schema
    GRANT USAGE ON SCHEMA schema_name TO username
  • Grant on all tables for DML statements: SELECT, INSERT, UPDATE, DELETE
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_name TO username
  • Grant all privileges on all tables in the schema
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name TO username