User Tools

Site Tools


postgresql:postgresql_basic_querys

[DOC] PostgreSQL basic queries

Section: Description

Basic querys for PostgreSQL administration

psql documentation

Section: Usage

Change pager

export PAGER=less

Permanent:

cat >> ~/.bash_profile<<EOF
export PAGER=less
EOF

Working modes:

postgres=# \pset pager on;
Pager is used for long output.

postgres=# \pset pager off;
Pager is used for long output.

postgres=# \pset pager always;
Pager is used for long output.

postgres=# \pset pager occasionally
Pager is used for long output.

postgres=# \pset pager at random
Pager is used for long output.
\pset: extra argument "random" ignored

execute query

psql

\g

Execute sql script

\i somedir/script2.sql

help

psql

\h
\?

autocommit

\SET AUTOCOMMIT off

Check:

\echo :AUTOCOMMIT

change current schema

set search_path=SCHEMANAME ;

Section: Listing Objects

list databases

psql
\l
\l+
SELECT datname FROM pg_database;

list tablespaces

\db

&

SELECT spcname FROM pg_tablespace;

list schemas

\dn

list parameters

SELECT * FROM pg_settings ;

list users

SELECT * FROM pg_authid  ;
\du

list domains

\dD

list ALL tables in database

SELECT c.relname, np.nspname, u.usename, c.relkind
FROM pg_class c,
    pg_user u,
    pg_namespace np
WHERE u.usesysid=c.relowner
AND np.oid= c.relnamespace
AND c.relkind IN ('r', 'v')
ORDER BY np.nspname, c.relname
;

Where:

  • r = regular table
  • v = view

link

list functions

\da

list data types

\dT

list triggers

SELECT event_object_schema AS table_schema,
       event_object_table AS TABLE_NAME,
       trigger_schema,
       trigger_name,
       string_agg(event_manipulation, ',') AS event,
       action_timing AS activation,
       action_condition AS condition,
       action_statement AS definition
FROM information_schema.triggers
GROUP BY 1,2,3,4,6,7,8
ORDER BY table_schema,
         TABLE_NAME;

list objects in schema

Change <SCHEMA_NAME>:

SELECT n.nspname AS schema_name,
       p.proname AS specific_name,
       l.lanname AS LANGUAGE,
       CASE WHEN l.lanname = 'internal' THEN p.prosrc
            ELSE pg_get_functiondef(p.oid)
            END AS definition,
       pg_get_function_arguments(p.oid) AS arguments,
       p.probin
FROM pg_proc p
LEFT JOIN pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN pg_language l ON p.prolang = l.oid
LEFT JOIN pg_type t ON t.oid = p.prorettype 
WHERE n.nspname IN (<SCHEMA_NAME>)
ORDER BY schema_name,
         specific_name;

Section: objects information

Table description

\d+ tablename

list table columns

\d+ "table_name"
SELECT table_catalog,
    table_schema,
    TABLE_NAME,
    column_name,
    ordinal_position,
    data_type,
    character_maximum_length,
    column_default,
    is_nullable
FROM information_schema.columns
WHERE TABLE_NAME   = 'table_name'
ORDER BY ordinal_position
;

List table objects

SHOW INDEX FROM yourtable;
SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

show current schema

show search_path;

show object description

\dd
\dd [pattern]

show object source

Change FUNCTION_NAME:

SELECT prosrc FROM pg_proc WHERE proname='FUNCTION_NAME'  ;

Section: GRANTS

Column grants

SELECT * FROM information_schema.role_column_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

Routine grants

SELECT * FROM information_schema.role_routine_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

Table grants

SELECT * FROM information_schema.role_table_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;
SELECT * FROM (SELECT grantee, table_schema, TABLE_NAME, string_agg( privilege_type, ', ' ) FROM information_schema.role_table_grants WHERE grantee NOT IN ( 'postgres', 'PUBLIC' ) GROUP BY grantee, table_catalog, table_schema, TABLE_NAME) t ORDER BY table_schema, grantee ;

Udt grants

SELECT * FROM information_schema.role_udt_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

Usage grants

SELECT * FROM information_schema.role_usage_grants WHERE grantee NOT IN ('postgres', 'PUBLIC' ) ;

Grant all on SCHEMA to all users

DO $$
DECLARE r record;
BEGIN
    FOR r IN SELECT rolname FROM pg_roles WHERE rolname NOT IN ('postgres', 'replicator', 'pg_signal_backend')
    LOOP
        EXECUTE 'GRANT USAGE ON SCHEMA <SCHEMANAME> TO ' || r.rolname;
        EXECUTE 'GRANT INSERT, DELETE, UPDATE, SELECT ON ALL TABLES IN SCHEMA <SCHEMANAME> TO ' || r.rolname;
    END LOOP;
END$$;

Section: DBA

Sessions

SELECT datid,
    datname,
    pid,
    usename,
    client_addr,
    application_name,
    query
FROM pg_stat_activity
WHERE datname <>'postgres'
;
SELECT pid,
        usename, 
--         application_name, 
        client_addr, 
        backend_start, 
--         xact_start, 
        now()-query_start Query_duration, 
--         state_change, 
        waiting, 
        state, 
        query
FROM pg_stat_activity;

Change user password

ALTER USER "user_name" WITH PASSWORD 'new_password';

Section: Streaming Replication

Check if the database is in recovery mode

SELECT pg_is_in_recovery();

Current wall address

Master:

SELECT pg_current_wal_lsn();

Slave:

SELECT pg_last_wal_receive_lsn();

Wal apply lag (on the slave)

SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) ;

Status of the slaves (on master)

SELECT * FROM pg_stat_replication ;

Status of the slave (on the slave)

SELECT * FROM pg_stat_wal_receiver;

Section: Logical Replication

Status of the origin

SELECT * FROM pg_replication_origin_status;

Status of the subscriber

SELECT * FROM pg_stat_subscription;

list publications

SELECT * FROM pg_publication ;

list subscriptions

SELECT * FROM pg_subscription ;

Section: EMPTY












postgresql/postgresql_basic_querys.txt · Last modified: 2020/10/21 15:37 by dodger