PosgreSQLのデータベース、ユーザ、スキーマ、テーブル作成などのDDLや確認用のSQLを整理しておきます

0.psql

sudo -u postgres psql
postgres=#

1.データベース

作成／削除

createDatabase.sql

CREATE DATABASE testdb;
DROP DATABASE IF EXISTS testdb;

確認

select * from pg_database

2.ユーザ（ロール）作成

作成

createUser.sql

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.スキーマ作成

作成

createSchema.sql

CREATE SCHEMA testschema;
DROP SCHEMA testschema;

確認

select * from information_schema.schemata

4.テーブル

作成

Table.sql

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