postgrepsql 要点汇总

log_min_duration_statement=5000

ALTER SYSTEM set work_mem = 16MB;

explain analyze select …
explain (analyze,verbose,buffers) select …

begin;
explain analyze insert/update/delete … ;
rollback;

CREATE ROLE leo LOGIN PASSWORD 'password' SUPERUSER VALID UNTIL 'infinity'

CREATE ROLE user_percona LOGIN PASSWORD 'xxxxxx';

# admin
CREATE ROLE user_db LOGIN PASSWORD 'xxxxxx';
CREATE DATABASE dbtest WITH owner  = 'user_db';

CREATE SCHEMA db_percona;

# grant privielges
GRANT ALL ON ALL TABLES IN SCHEMA public TO user_test WITH GRANT OPTION;
GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA my_schema TO PUBLIC; # PUBLIC 为授予所有人;
GRANT ALL ON SCHEMA db_percona TO user_percona;

initdb -E UTF8

# command line
$ createdb -E UTF8 -T template0 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8 dbtest

# with SQL 
CREATE DATABASE dbtest WITH ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TEMPLATE=template0;

$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 percona   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

# 9.1 之前的版本为 procid, 新版为 pid 
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'user_name'

SELECT
    pg_database.datname,
    REPLACE(tmp.state, ' ', '_'),
    COALESCE(count,0) as count,
    COALESCE(max_tx_duration,0) as max_tx_duration
FROM
  (
    VALUES ('active'),
     ('idle'),
     ('idle in transaction'),
     ('idle in transaction (aborted)'),
     ('fastpath function call'),
     ('disabled')
) AS tmp(state) CROSS JOIN (select * from pg_database where datname NOT IN ('postgres', 'template0', 'template1')) AS pg_database
LEFT JOIN
  (
    SELECT
        datname,
        state,
        count(*) AS count,
        MAX(EXTRACT(EPOCH FROM now() - xact_start))::float AS max_tx_duration
FROM pg_stat_activity
GROUP BY datname,state) AS tmp2
  ON tmp.state = tmp2.state AND pg_database.datname = tmp2.datname;

    SELECT pg_database.datname,tmp.mode,COALESCE(count,0) as count
     FROM
    (
        VALUES ('accesssharelock'),
               ('rowsharelock'),
               ('rowexclusivelock'),
               ('shareupdateexclusivelock'),
               ('sharelock'),
               ('sharerowexclusivelock'),
               ('exclusivelock'),
               ('accessexclusivelock'),
               ('sireadlock')
     ) AS tmp(mode) CROSS JOIN (select *, oid from pg_database where datname NOT IN ('postgres', 'template0', 'template1')) AS pg_database
     LEFT JOIN
     (SELECT database, lower(mode) AS mode,count(*) AS count
       FROM pg_locks WHERE database IS NOT NULL
       GROUP BY database, lower(mode)
     ) AS tmp2
     ON tmp.mode=tmp2.mode and pg_database.oid = tmp2.database ORDER BY 1

SELECT *,
    (case pg_is_in_recovery() when 't' then null else pg_current_xlog_location() end) AS pg_current_xlog_location,
    (case pg_is_in_recovery() when 't' then null else pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::float end) AS pg_xlog_location_diff
FROM pg_stat_replication

SELECT slot_name, database, active, pg_xlog_location_diff(pg_current_xlog_location(), restart_lsn)
  FROM pg_replication_slots

SELECT *, extract(epoch from now() - last_archived_time) AS last_archive_age 
  FROM pg_stat_archiver

select pg_is_in_recovery()

export LD_LIBRARY_PATH=/usr/local/perl5.26/lib/5.26.0/x86_64-linux-thread-multi/CORE:$LD_LIBRARY_PATH
pg_ctl restart

# 再安装扩展
postgres=# create extension plperl;                        
CREATE EXTENSION

# 查看对应扩展的函数
postgres=# \dx+ plperl
      Objects in extension "plperl"
            Object Description            
------------------------------------------
 function plperl_call_handler()
 function plperl_inline_handler(internal)
 function plperl_validator(oid)
 language plperl
(4 rows)