PostgreSQL的备忘录

我会整理一下关于PosgreSQL数据库、用户、模式和表的DDL和用于确认的SQL。

0.利用psql工具进行操作

sudo -u postgres psql
postgres=#

1. 数据库

作成／削除

CREATE DATABASE testdb;
DROP DATABASE IF EXISTS testdb;

確認

select * from pg_database

2. 创建用户（角色）

作成

CREATE ROLE testuser PASSWORD 'secret' SUPERUSER CREATEDB CREATEROLE;

alter

alter role testuser login;

GRANT

GRANT ALL ON ALL TABLES IN SCHEMA public TO testuser;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO testuser;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO testuser;

確認

select * from pg_roles

削除

DROP ROLE testrole

3. 创建模式

作成

CREATE SCHEMA testschema;
DROP SCHEMA testschema;

確認

select * from information_schema.schemata

桌子

作成

create table "testschema".account (
  username character varying(64) not null
  , password character varying(255)
  , email character varying(64)
  , primary key (username)
)

確認

select * from information_schema.columns a
where
a.table_catalog='testdb'
and a.table_schema='testschema'
and table_name='account';

5. 使用主键信息获取列数据。

SELECT 
  col.column_name 
 ,col.data_type
 ,col.ordinal_position
 ,col.column_default
 ,col.is_nullable
 ,col.character_maximum_length as max_length 
 ,col.character_octet_length   as oct_length 
 ,col.numeric_precision        as num_precision 
 ,col.numeric_precision_radix  as num_radix 
 ,col.numeric_scale            as num_scale 
 ,col.datetime_precision       as dt_precision
 ,pk.ordinal_position         as pk_ordinal
,(case when pk.ordinal_position is not null then true else false end) as is_primary_key
,(case when substr(col.column_default,1,7) = 'nextval' then true else false end) as is_serial
from  
  information_schema.columns col 
LEFT JOIN
  (SELECT
        tc.constraint_catalog
      , tc.constraint_schema
      , tc.table_name
      , ccu.column_name
      , kcu.ordinal_position
    FROM
      information_schema.table_constraints tc
    INNER JOIN 
      information_schema.constraint_column_usage ccu
    ON 
      tc.constraint_catalog = ccu.constraint_catalog
    AND 
      tc.constraint_schema = ccu.constraint_schema
    AND 
      tc.constraint_name = ccu.constraint_name
    INNER JOIN 
      information_schema.key_column_usage kcu
    ON 
      tc.constraint_catalog = kcu.constraint_catalog
    AND 
      tc.constraint_schema = kcu.constraint_schema
    AND 
      tc.constraint_name = kcu.constraint_name
    AND 
      ccu.column_name = kcu.column_name
    WHERE
      tc.constraint_type='PRIMARY KEY'
  ) as pk
ON
  col.table_catalog=pk.constraint_catalog
AND
  col.table_schema=pk.constraint_schema
AND
  col.table_name=pk.table_name
AND
  col.column_name=pk.column_name
WHERE 
  col.table_catalog='データベース名'
AND
  col.table_schema='スキーマ名'
AND
  col.table_name='テーブル名'
ORDER BY
  col.ordinal_position  

2020年3月18日修正

使用主键联接将”information_schema.table_constraints”联接，并添加条件”constraint_type=’PRIMARY KEY'”。

6.备份/恢复

(1) 全体备份

pg_dumpall -f all.sql

※ 如果指定了数据库

pg_dump -Fp testdb -f testdb.sql

(2) 全体恢复

psql --username=postgres -f all.sql

7. 当前进程的活动状态

select * from pg_stat_activity