2020-01-21

OSS-DB試験対策緑本 ch7 基本的な運用管理

www.shoeisha.co.jp

データベースユーザの追加/削除/変更
VACUUM, ANALYZE
自動バキューム
システム情報取得関数
情報スキーマ、システムカタログ(pg_roles, pg_authidなど)
テーブル単位の権限、GRANT文とREVOKE文

基本的な運用管理

データベースユーザの追加/削除/変更

ユーザの追加

postgres@e3b855e6f1da:~$ createuser -P user1

-P, --pwprompt: パスワード設定

Enter password for new role: 
Enter it again:

【補】パスワード認証を試すにはpg_hba.confの設定が必要

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
  # "local" is for Unix domain socket connections only
- local   all             all                                     trust
+ # local   all             all                                     trust
+ local   all             all                                     password

pg_hba.confの変更反映はSIGHUP

postgres@e3b855e6f1da:~$ pg_ctl reload

pg_ctl reload
server signaled

ユーザ確認

postgres@e3b855e6f1da:~$ psql -U user1 -d postgres -c "\du"

Password for user user1: 
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user1     |                                                            | {}

ユーザの削除

削除対象のユーザ指定

postgres@e3b855e6f1da:~$ dropuser user1

dropuser user1

削除対象のユーザを指定しないとエラー

postgres@e3b855e6f1da:~$ dropuser

dropuser: error: missing required argument role name
Try "dropuser --help" for more information.

-i, --interactive なら省略可能

postgres@e3b855e6f1da:~$ dropuser --interactive

Enter name of role to drop: user1

Role "user1" will be permanently removed.
Are you sure? (y/n) y

ユーザの変更

postgres@e3b855e6f1da:~$ psql -c "\h alter user"

Command:     ALTER USER
Description: change a database role
Syntax:
ALTER USER role_specification [ WITH ] option [ ... ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'

ALTER USER name RENAME TO new_name
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

    role_name
  | CURRENT_USER
  | SESSION_USER

URL: https://www.postgresql.org/docs/12/sql-alteruser.html

ALTER USERはALTER ROLEのエイリアス
- 【補】現在のPostgreSQLでは、USERもGROUPもROLEに統合されている
PostgreSQL拡張文
公式/ALTER ROLE
権限つけはずし

postgres@e3b855e6f1da:~$ createuser syaro
postgres@e3b855e6f1da:~$ psql -c "\du"

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {}

シャロちゃんをスーパーユーザにする

postgres@e3b855e6f1da:~$ psql -c "ALTER USER syaro WITH SUPERUSER"

ALTER ROLE

postgres@e3b855e6f1da:~$ psql -c "\du"

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     | Superuser                                                  | {}

もどす

postgres@e3b855e6f1da:~$ psql -c "ALTER USER syaro WITH NOSUPERUSER"

ALTER ROLE

postgres@e3b855e6f1da:~$ psql -c "\du"

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {}

VACUUM, ANALYZE

VACUUM

公式/Routine Vacuuming

In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the
old version of the row. This approach is necessary to gain the benefits of
multiversion concurrency control (MVCC, see Chapter 13)

MVCC: Multi-Version Concurrency Control (多版型同時実行制御)
- 公式/Concurrency Control
- トランザクション分離のための技術
PostgreSQLのMVCCは追記型
- UPDATEやDELETE時に即座には行を削除しない
- 不要領域が溜まっていく
  - ディスク領域圧迫
  - I/O増加による性能低下
- テーブルだけでなくインデックスにも生じる
VACUUMによる不要領域回収が必要

Command:     VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:

    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP [ boolean ]
    TRUNCATE [ boolean ]

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

URL: https://www.postgresql.org/docs/12/sql-vacuum.html

VACUUMとVACUUM FULL
- VACUUM
  - 定期的に実行しよう
    - 特に更新が頻繁なテーブル
- VACUUM FULL
  - より多くの領域を回収できるが…
  - より時間がかかる
  - 排他ロックがかかる
  - 余分のディスク領域を要する
    - テーブルをコピーしてから不要領域を物理削除するから
  - 操作が完了するまで領域は回収されない
VACUUMしてみる
作りたてのテーブルに対して

postgres=# CREATE TABLE tab1 (hoge integer);
CREATE TABLE

postgres=# \dt

        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tab1 | table | postgres
(1 row)

postgres=# VACUUM VERBOSE tab1;

INFO:  vacuuming "public.tab1"
INFO:  "tab1": found 0 removable, 0 nonremovable row versions in 0 out of 0 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 503
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM

VERBOSEの情報はstderrに出る
dead tupleを3つ用意してみる

postgres=# INSERT INTO tab1 VALUES (1);
INSERT 0 1
postgres=# INSERT INTO tab1 VALUES (2);
INSERT 0 1
postgres=# INSERT INTO tab1 VALUES (3);
INSERT 0 1

postgres=# DELETE FROM tab1;
DELETE 3


postgres=# VACUUM VERBOSE tab1;

INFO:  vacuuming "public.tab1"
INFO:  "tab1": removed 3 row versions in 1 pages
INFO:  "tab1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 507
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  "tab1": truncated 1 to 0 pages
DETAIL:  CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

これ

...
INFO:  "tab1": removed 3 row versions in 1 pages
INFO:  "tab1": found 3 removable, 0 nonremovable row versions in 1 out of 1 pages
...

3行がremoveされた

ANALYZE

統計情報
- 実行計画を決めるやつ
大量の挿入・更新等が行われたらANALYZEで統計情報更新

Command:     ANALYZE
Description: collect statistics about a database
Syntax:
ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]

where option can be one of:

    VERBOSE [ boolean ]
    SKIP_LOCKED [ boolean ]

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

URL: https://www.postgresql.org/docs/12/sql-analyze.html

公式/ANALYZE
【補】統計情報はpg_statisticシステムカタログに格納される

postgres=# ANALYZE VERBOSE tab1;

INFO:  analyzing "public.tab1"
INFO:  "tab1": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
ANALYZE

処理時間

For large tables, ANALYZE takes a random sample of the table contents,
rather than examining every row.
This allows even very large tables to be analyzed in a small amount of time.

巨大なテーブルでも各行舐めるのではなくランダムサンプリングするので高速

VACUUM ANALYZE

VACUUMとANALYZEをまとめて実行

自動バキューム

postgresql.confに設定項目あり

#------------------------------------------------------------------------------
# STATISTICS
#------------------------------------------------------------------------------

# - Query and Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_io_timing = off

...
#------------------------------------------------------------------------------
# AUTOVACUUM
#------------------------------------------------------------------------------

#autovacuum = on            # Enable autovacuum subprocess?  'on'
                    # requires track_counts to also be on.

公式/postgresql.confのautovacuum設定
公式/postgresql.confの統計情報設定
autovacuumをonにする場合、track_countsもonにする必要あり

システム情報取得関数

システム情報取得関数
システムの状態を確認する際に有用

version()関数

postgres@e3b855e6f1da:~$ psql
psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# SELECT version();
SELECT version();
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

psql起動時のバージョンはクライアントのバージョン
version()関数で表示されるのはサーバーのバージョン

current_database()関数

postgres=# SELECT current_database();

 current_database 
------------------
 postgres
(1 row)

現在接続中のデータベース
- psqlのプロンプトのpostgres=#の=#の前のやつ

current_userとuser

current_userとuserとは等価 (公式ドキュメントより)

postgres=# SELECT current_user;

 current_user 
--------------
 postgres
(1 row)

postgres=# SELECT user;

   user   
----------
 postgres
(1 row)

SQL規格上、カッコが付かないことになっている

postgres=# SELECT current_user();

ERROR:  syntax error at or near "("
LINE 1: SELECT current_user();
                           ^
postgres=# SELECT user();

ERROR:  syntax error at or near "("
LINE 1: SELECT user();
                   ^

情報スキーマ、システムカタログ(pg_roles, pg_authidなど)

データベースクラスタ全体にかかわる各種情報の大別
- 情報スキーマ
  - SQL標準規格
- システムカタログ
  - RDBMS固有

情報スキーマ -- information_schema

公式
SQL標準
- 可搬性がある
- 安定
PostgreSQL固有機能の情報は含まれない
例: enabled_roles
- 公式

postgres=# SELECT * FROM information_schema.enabled_roles;

         role_name         
---------------------------
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 postgres
 syaro
(10 rows)

現在有効なユーザ、ならびにそのユーザが継承しているメンバシップ(role)が再帰的に出力される
全テーブル
- publicスキーマ
  - デフォルトのスキーマ
  - 普段遣いのテーブルが入ってるやつ
- information_schema
  - 情報スキーマ
- pg_catalog
  - システムカタログ
  - 【補】\dSメタコマンドで出てくるやつ

SELECT * FROM information_schema.tables;

 table_catalog |    table_schema    |              table_name               | table_type | self_referencing_column_name | reference_generation | user_defined_type_catalog | user_defined_type_schema | user_defined_type_name | is_insertable_into | is_typed | commit_action 
---------------+--------------------+---------------------------------------+------------+------------------------------+----------------------+---------------------------+--------------------------+------------------------+--------------------+----------+---------------
 postgres      | public             | tab1                                  | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_statistic                          | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_type                               | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_foreign_server                     | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_authid                             | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_shadow                             | VIEW       |                              |                      |                           |                          |                        | NO                 | NO       | 
 postgres      | pg_catalog         | pg_statistic_ext_data                 | BASE TABLE |                              |                      |                           |                          |                        | YES                | NO       | 
 postgres      | pg_catalog         | pg_roles                              | VIEW       |                              |                      |                           |                          |                        | NO                 | NO       | 
 postgres      | pg_catalog         | pg_settings                           | VIEW       |                              |                      |                           |                          |                        | NO                 | NO       | 
...

システムカタログ、pg_rolesビュー、pg_authidカタログ

PostgreSQLの\duメタコマンドで出てくるような情報 + α

postgres@e3b855e6f1da:~$ psql -c "SELECT * FROM pg_roles WHERE rolcanlogin IS TRUE" -P pager=off

 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid  
----------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 postgres | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
 syaro    | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16394
(2 rows)

比較

postgres@e3b855e6f1da:~$ psql -c "\du"

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {}

pg_rolesではpasswordがrolpassword列に*で隠れて表示されたりする
cf. pg_authidではMD5 ハッシュ値が出てくる

postgres@e3b855e6f1da:~$  psql -c "SELECT * FROM pg_authid WHERE rolcanlogin IS TRUE" -P pager=off

  oid  | rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit |             rolpassword             | rolvaliduntil 
-------+----------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------------------------------+---------------
    10 | postgres | t        | t          | t             | t           | t           | t              | t            |           -1 |                                     | 
 16394 | syaro    | f        | t          | f             | f           | t           | f              | f            |           -1 | md5cd2fae6681b2fee62e31bd949d21abcf | 
(2 rows)

その他のシステムカタログ

公式/pg_settings
SHOW/SET文に代わるPostgreSQLの設定のインタフェース
- 最大/最小値、設定可能タイミング等も確認できる

postgres=# \x

Expanded display is on.


postgres=# SELECT * FROM pg_settings;

-[ RECORD 1 ]---+-----------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-------------------------------------------------------------------
name            | allow_system_table_mods
setting         | off
unit            | 
category        | Developer Options
short_desc      | Allows modifications of the structure of system tables.
extra_desc      | 
context         | postmaster
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | off
...

【補】設定可能タイミング(context)の確認とか

postgres=# SELECT name, context FROM pg_settings WHERE name = 'max_connections';

-[ RECORD 1 ]------------
name    | max_connections
context | postmaster

postgres=#  SELECT name, context FROM pg_settings WHERE name = 'max_wal_size';

-[ RECORD 1 ]---------
name    | max_wal_size
context | sighup

【補】enumの設定可能値の確認とか

postgres=# SELECT name, context, enumvals FROM pg_settings WHERE name = 'archive_mode';

-[ RECORD 1 ]-------------
name     | archive_mode
context  | postmaster
enumvals | {always,on,off}

pg_databaseシステムカタログ
- psql -lコマンドや\lメタコマンドで表示されるデータベース一覧を調べられる
- これはデータベースごとではなく、データベースクラスタに対して1つ存在する

postgres=# SELECT * FROM pg_database;

-[ RECORD 1 ]-+------------------------------------
oid           | 13408
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | en_US.utf8
datctype      | en_US.utf8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 13407
datfrozenxid  | 480
datminmxid    | 1
dattablespace | 1663
datacl        | 
-[ RECORD 2 ]-+------------------------------------
oid           | 1
datname       | template1

pg_tables

postgres=# SELECT * FROM pg_tables WHERE schemaname = 'public';

-[ RECORD 1 ]---------
schemaname  | public
tablename   | tab1
tableowner  | postgres
tablespace  | 
hasindexes  | f
hasrules    | f
hastriggers | f
rowsecurity | f

【補】information_schema.tablesとの比較

postgres=# SELECT * FROM information_schema.tables WHERE table_schema = 'public';

-[ RECORD 1 ]----------------+-----------
table_catalog                | postgres
table_schema                 | public
table_name                   | tab1
table_type                   | BASE TABLE
self_referencing_column_name | 
reference_generation         | 
user_defined_type_catalog    | 
user_defined_type_schema     | 
user_defined_type_name       | 
is_insertable_into           | YES
is_typed                     | NO
commit_action                |

pg_tablesにはインデックス、トリガーの情報などがある
【補】標準SQLにインデックスに関する既定はない
- = RDBMS固有 = システムカタログ
- 公式/CREATE INDEX

Compatibility

CREATE INDEX is a PostgreSQL language extension. There are no provisions for
indexes in the SQL standard.

テーブル単位の権限、GRANT文とREVOKE文

テーブル単位での権限設定

GRANT文

なんかいっぱい書式ある

postgres=# \h grant

Command:     GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
    ON [ TABLE ] table_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
    ON DATABASE database_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON DOMAIN domain_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN DATA WRAPPER fdw_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON FOREIGN SERVER server_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
    ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
         | ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON LANGUAGE lang_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
    ON LARGE OBJECT loid [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { CREATE | ALL [ PRIVILEGES ] }
    ON TABLESPACE tablespace_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_USER
  | SESSION_USER

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

文、テーブル、ロールに対して権限設定
全〜系
- ALL: 全権限
- TO PUBLIC: 全ユーザ
- ON ALL TABLES IN SCHEMA public: publicスキーマに属する全テーブル
○○もできる系
- SELECT権限: COPY TOも許可
- INSERT権限: COPY FROMも許可
WITH GRANT OPTIONを指定すると、他の人に権限を付与できるようになる
SELECTの列単位で権限付与可能

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
    [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }

権限付与してみる
- 作りたてのシャロちゃんはINSERT権限なし

postgres@e3b855e6f1da:~$ psql -U syaro -d postgres

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=> INSERT INTO tab1 VALUES (1);

ERROR:  permission denied for table tab1

INSERT権限を与える

postgres@e3b855e6f1da:~$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# GRANT INSERT ON tab1 TO syaro;

GRANT

再度INSERTを試してみる

postgres@e3b855e6f1da:~$ psql -U syaro -d postgres

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=> INSERT INTO tab1 VALUES (1);

INSERT 0 1

通った

REVOKE文

GRANTの逆のやつ

postgres=# REVOKE ALL ON ALL TABLES IN SCHEMA public FROM syaro;

REVOKE

INSERT試してみる

postgres@e3b855e6f1da:~$ psql -U syaro -d postgres

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=> INSERT INTO tab1 VALUES (2);

ERROR:  permission denied for table tab1

無事権限剥奪できた

\dpまたは\z -- テーブルの権限の確認

権限付与前

postgres=# \dp

                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 public | tab1 | table | postgres=arwdDxt/postgres |                   | 
(1 row)

全権付与後

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public TO syaro;

GRANT

postgres=# \dp

                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 public | tab1 | table | postgres=arwdDxt/postgres+|                   | 
        |      |       | syaro=arwdDxt/postgres    |                   | 
(1 row)

部分的に権限を取り上げる

postgres=# REVOKE INSERT,UPDATE,DELETE,TRUNCATE ON tab1 FROM syaro;

REVOKE

postgres=# \dp

                                Access privileges
 Schema | Name | Type  |     Access privileges     | Column privileges | Policies 
--------+------+-------+---------------------------+-------------------+----------
 public | tab1 | table | postgres=arwdDxt/postgres+|                   | 
        |      |       | syaro=rxt/postgres        |                   | 
(1 row)

syaro=rxt/postgresの意味
- postgresがsyaroに権限rxtを付与した
権限の見方

文字	意味	権限
a	append	INSERT
r	read	SELECT
w	write	UPDATE
d	delete	DELETE
D		TRUNCATE
x		REFERENCES
t		TRIGGER

2020-01-20

OSS-DB試験対策緑本 ch6 バックアップとリストア

OSS-DB PostgreSQL 勉強メモ

www.shoeisha.co.jp

pg_dumpコマンド、pg_dumpallコマンド、pg_restoreコマンド
ディレクトリコピーによるバックアップ&リストア
PITR: Point In Time Recovery
COPY文と\copyコマンド
- COPY文
- \copyコマンド

pg_dumpコマンド、pg_dumpallコマンド、pg_restoreコマンド

バックアップ形式	バックアップコマンド	リストアコマンド
平文	`pg_dump`, `pg_dumpall`	`psql`
平文以外	`pg_dump`	`pg_restore`

pg_dumpコマンド、pg_dumpallコマンドによるバックアップ

postgres@8040c0c21fcb:/$ pg_dump --help

pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
...

主要なオプション
-f, --file=FILENAME
- バックアップ先のファイル名
- 省略時stdout
-F, --format=c|d|t|p (-Fp みたいな感じに指定)
- 出力形式
- c: custom
- d: directory
- t: tar
- p: plain text (デフォルト)
テーブルとデータ作っておく

CREATE TABLE tab1 (
  col1 integer,
  col2 integer,
  col3 integer
);

INSERT INTO tab1 VALUES (1, 1, 1);
INSERT INTO tab1 VALUES (2, 2, 2);

平文でstdoutにダンプ出してみる

postgres@8040c0c21fcb:/$ pg_dump -Fp

...

--
-- Name: tab1; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.tab1 (
    col1 integer,
    col2 integer,
    col3 integer
);


ALTER TABLE public.tab1 OWNER TO postgres;

--
-- Data for Name: tab1; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.tab1 (col1, col2, col3) FROM stdin;
1   1   1
2   2   2
\.

--
-- PostgreSQL database dump complete
--

カスタム形式でpostgresデータベースをファイルに書き出してみる
- PostgreSQL独自のバイナリ圧縮形式

postgres@8040c0c21fcb:/home/postgres$ pg_dump -Fc -f sample.dump

これと同義

postgres@8040c0c21fcb:/home/postgres$ pg_dump -Fc > sample.dump

全データベース書き出すにはpg_dumpall

psqlコマンドを使った平文形式のリストア

psql -f <ファイル名>で流し込むだけ

pg_restoreコマンドを使った平文形式以外のリストア

公式/pg_restore

postgres@8040c0c21fcb:/home/postgres$ pg_restore --help

pg_restore restores a PostgreSQL database from an archive created by pg_dump.

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        connect to database name
  -f, --file=FILENAME      output file name (- for stdout)
...

データベース名を指定すると、そのデータベースへリストアする
データベース名を指定しない場合、平文のSQLをstdoutに書き出す
- 【補】PostgreSQL12からは-f -が必要
ファイルからリストア

postgres@8040c0c21fcb:/home/postgres$ pg_restore -p 5433 -d postgres < sample.dump

標準出力に書き出し(customやtarをデコードする感じ)

postgres@8040c0c21fcb:/home/postgres$ pg_restore sample.dump -f -

--
-- PostgreSQL database dump
--
...

バックアップ&リストアの組み合わせの例

(pg_dump/pg_dumpall) + (pg_restore/psql -f)
いずれもサーバ稼働中に行える
- オンラインバックアップ(ホットバックアップ)
- 論理バックアップ
  - テーブル構造やデータを意識する
  - 【補】別のメジャーバージョンへのリストア可能

postgres@8040c0c21fcb:/home/postgres$ pg_restore -p 5433 -d postgres < sample.dump

既にオブジェクトがあると怒られる

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 8; 2615 16384 SCHEMA exam_schema postgres
pg_restore: error: could not execute query: ERROR:  schema "exam_schema" already exists
Command was: CREATE SCHEMA exam_schema;


pg_restore: from TOC entry 203; 1259 16385 TABLE tab1 postgres
pg_restore: error: could not execute query: ERROR:  relation "tab1" already exists
Command was: CREATE TABLE public.tab1 (
    col1 integer,
    col2 integer,
    col3 integer
);


pg_restore: warning: errors ignored on restore: 2

cleanオプションを指定すると、restore前にデータベースオブジェクトを削除する

  -c, --clean                  clean (drop) database objects before recreating

postgres@8040c0c21fcb:/home/postgres$ pg_restore -p 5433 -c -d postgres < sample.dump

エラー出ない

postgres@8040c0c21fcb:/home/postgres$

ディレクトリコピーによるバックアップ&リストア

サーバを停止してデータベースクラスタをコピーする方法
- オフラインバックアップ(コールドバックアップ)
- 物理バックアップ
  - テーブル構造やデータを意識しない
  - 【補】別のメジャーバージョンへのリストア不可

PITR: Point In Time Recovery

公式
ロールフォワードに使うログ

PITRの概要

base backup
- ある時点でのデータベース全体のバックアップ
WAL: Write Ahead Log
- すべての変更の記録 (=差分)
- 16MBのファイル(ログセグメント)に記録されていく
  - ファイルサイズはinitdbの--wal-segsize=SIZE_IN_MBで設定できる
- 古いものをアーカイブして再利用していく

PITRによるバックアップの前準備/設定

公式
wal_level
- WALに書き込まれる情報の度合い (下のものほど情報が多い)
  - minimal
    - 即時シャットダウン後のクラッシュリカバリに必要な最低限のみ
    - PITR不可
  - replica (デフォルト)
    - WALアーカイブ、レプリケーション対応
  - logical
    - 論理デコード対応
- postmaster
archive_mode
- WALセグメントをarchive_commandでWALアーカイブ入りするかどうか
  - off
    - しない
  - on
    - する
  - always
    - する
    - 普段はonと変わらない
    - アーカイブリカバリ、スタンバイモード中も有効
archive_command
- WALセグメントをWALアーカイブとしてコピーするためのシェルコマンド
- cpとかcopyとか
- プレースホルダ
  - %p
    - ファイルパス
  - %f
    - ファイル名
- 例
  - cp %p /mnt/server/archivedir/%f'
- $PGDATA/pg_wal/のWALがコピーされていく

PITRによるベースバックアップ

【補】公式/CHECKPOINT
- 全てのデータファイルをディスクに書き出す
- PostgreSQL拡張
いつ行われる
- checkpoint_timeoutに設定した時間が前回checkpointから経過した
  - デフォルト5min
- WALファイルの総サイズがmax_wal_sizeを超えたら
  - デフォルト1GB
  - WALセグメント1つのサイズはデフォルト16MBなので64 + 1ファイル (バージョン12時点)
- pg_start_backup()関数による強制実行
- などなど
バックアップにはPostgreSQLサーバーの停止の必要なし
- cf. リストアには必要
流れ
1. pg_start_backup()関数呼び出し
  - チェックポイントの強制実行
2. データベースクラスタのディレクトリを丸ごとバックアップ
  - tgzとかで
3. pg_stop_backup()関数呼び出し
  - バックアップモード終了
コンフィグ設定
- archive_modeをonに
- archive_commandを設定
  - とりあえずダミーのexit 0にした(雑)

  # - Archiving -
   
  #archive_mode = off       # enables archiving; off, on, or always
+ archive_mode = on     # enables archiving; off, on, or always
                # (change requires restart)
  #archive_command = ''     # command to use to archive a logfile segment
+ archive_command = 'exit 0'        # command to use to archive a logfile segment
                # placeholders: %p = path of file to archive
                #               %f = file name only
                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
  #archive_timeout = 0      # force a logfile segment switch after this
                # number of seconds; 0 disables

pg_start_backup()関数実行

postgres@8040c0c21fcb:/home/postgres$ psql -p 5433 -c "SELECT pg_start_backup('label', true);"

 pg_start_backup 
-----------------
 0/4000028
(1 row)

(この間にデータベースクラスタディレクトリを丸ごとバックアップする)

tarとかmvとか

pg_stop_backup()関数実行

postgres@8040c0c21fcb:/home/postgres$ psql -p 5433 -c "SELECT pg_stop_backup();"

NOTICE:  all required WAL segments have been archived
 pg_stop_backup 
----------------
 0/4000138
(1 row)

PITRによるリカバリの手順

ながれ
1. ベースバックアップのリストア
2. ロールフォワードリカバリの準備
  1. ベースバックアップに残存している古いWALファイル($PGDATA/pg_wal/)を削除
  2. 未アーカイブのWALファイルを$PGDATA/pg_wal/へコピー
3. recovery.confを設定してPostgreSQLを起動
  - PostgreSQL11のドキュメント
    - recovery.confはrecovery.doneになる
  - 【補】PostgreSQL12からはpostgresql.confへ統合された

postgresql.conf

...
# - Archive Recovery -

# These are only used in recovery mode.

#restore_command = ''       # command to use to restore an archived logfile segment
                # placeholders: %p = path of file to restore
                #               %f = file name only
                # e.g. 'cp /mnt/server/archivedir/%f %p'
                # (change requires restart)
#archive_cleanup_command = ''   # command to execute at every restartpoint
#recovery_end_command = ''  # command to execute at completion of recovery
...

未アーカイブのWALファイルのコピーを行わなかった場合、WALアーカイブの最新の部分までがリカバリされる
- つまり、ベースバックアップ時点にリカバリされる
- 障害発生時点まではロールフォワードしない

COPY文と\copyコマンド

	COPY文	\copyコマンド
処理場所	サーバサイド	クライアントサイド
filename	シングルクォートで囲む、絶対パス	シングルクォートで囲まない、相対パス

\copyコマンドはN/Wを介した通信が発生する
- 大容量ファイルの場合はCOPY文を使おう

COPY文

postgres@8040c0c21fcb:/home/postgres$ psql -c "\h COPY"

Command:     COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

主要なオプション
- DELIMITER 'delimiter_character'
  - 各行の列の区切り文字
  - デフォルト水平タブ
- FORMAT format_name
  - csvとか
- HEADER [ boolean ]
  - csvのヘッダ行の入出力をするか否か
STDINの場合\.を、ファイルの場合EOFを読むまで入出力する
pg_dumpで平文バックアップしたSQLの中身

COPY public.tab1 (col1, col2, col3) FROM stdin;
1   1   1
2   2   2
\.

\copyコマンド

\copy { table [ ( column_list ) ] | ( query ) } { from | to } { 'filename' | program 'command' | stdin | stdout | pstdin | pstdout } [ [ with ] ( option [, ...] ) ]

2020-01-19

OSS-DB試験対策緑本 ch5 設定ファイル

OSS-DB PostgreSQL 勉強メモ

www.shoeisha.co.jp

postgresql.conf
接続と認証 (CONNECTIONS AND AUTHENTICATION)
クライアント接続デフォルト (CLIENT CONNECTION DEFAULTS)
エラー報告とログ取得 (ERROR REPORTING AND LOGGING)
SET文/SHOW文の使い方
- SET文
- SHOW文
pg_hba.conf

postgresql.conf

公式

postgres@00aa48792429:/$ cat $PGDATA/postgresql.conf

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
...

最初からいろいろコメントアウトされてる

...
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'       # use data in another directory
                    # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
                    # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
                    # (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = ''         # write an extra PID file
                    # (change requires restart)
...

設定反映タイミングには複数ある
公式
- internal
  - ユーザが直接変更不可能
  - SHOW文で設定値確認可能
- postmaster
  - サーバー起動時にのみ設定可能
  - 設定変更の反映にpg_ctl restart必要
    - reloadではなく
- sighup
  - サーバー再起動せずに設定反映可能
  - SIGHUPで反映
    - pg_ctl reload
    - pg_ctl kill HUP <pid>
- superuser-backend
  - サーバー再起動せずに設定反映可能
  - スーパーユーザによるコネクション確立時に設定可能
- backend
  - サーバー再起動せずに設定反映可能
  - 一般ユーザによるコネクション確立時に設定可能
- superuser
  - サーバ再起動不要、スーパーユーザによるSET文でも反映可能
- user
  - サーバ再起動不要、一般ユーザによるSET文でも反映可能

	スーパーユーザ	一般ユーザも
コネクション確立時	superuser-backend	backend
SET文	superuser	user

postgres@8040c0c21fcb:/$ psql -p 5433 -c "SHOW max_connections"

 max_connections 
-----------------
 100
(1 row)

max_connectionsを書き換えてみる

- max_connections = 100           # (change requires restart)
- #max_connections = 100           # (change requires restart)
+ max_connections = 123         # (change requires restart)

そのまま再度読んでみる

postgres@8040c0c21fcb:/$ psql -p 5433 -c "SHOW max_connections"

 max_connections 
-----------------
 100
(1 row)

変わらない

 max_connections 
-----------------
 100
(1 row)

SIGHUP送ってみる

postgres@8040c0c21fcb:/$ pg_ctl reload -D /home/postgres/data -o "-p 5433"

反映されないものがあるぞ、とエラー出る

server signaled
2020-01-19 07:19:51.088 UTC [94] LOG:  received SIGHUP, reloading configuration files
2020-01-19 07:19:51.088 UTC [94] LOG:  parameter "max_connections" cannot be changed without restarting the server
2020-01-19 07:19:51.088 UTC [94] LOG:  configuration file "/home/postgres/data/postgresql.conf" contains errors; unaffected changes were applied

postgres@8040c0c21fcb:/$ psql -p 5433 -c "SHOW max_connections"

変わらない
- サーバー再起動を要するパラメータだから
  - (change requires restart)

 max_connections 
-----------------
 100
(1 row)

サーバ再起動

postgres@8040c0c21fcb:/$ pg_ctl restart -D /home/postgres/data -o "-p 5433"

2020-01-19 07:21:19.933 UTC [94] LOG:  received fast shutdown request
waiting for server to shut down....2020-01-19 07:21:19.937 UTC [94] LOG:  aborting any active transactions
2020-01-19 07:21:19.938 UTC [94] LOG:  background worker "logical replication launcher" (PID 101) exited with exit code 1
2020-01-19 07:21:19.939 UTC [96] LOG:  shutting down
2020-01-19 07:21:19.954 UTC [94] LOG:  database system is shut down
 done
server stopped
waiting for server to start....2020-01-19 07:21:20.051 UTC [438] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-01-19 07:21:20.052 UTC [438] LOG:  listening on IPv4 address "0.0.0.0", port 5433
2020-01-19 07:21:20.052 UTC [438] LOG:  listening on IPv6 address "::", port 5433
2020-01-19 07:21:20.057 UTC [438] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2020-01-19 07:21:20.071 UTC [439] LOG:  database system was shut down at 2020-01-19 07:21:19 UTC
2020-01-19 07:21:20.077 UTC [438] LOG:  database system is ready to accept connections
 done
server started

再度読んでみる

postgres@8040c0c21fcb:/$ psql -p 5433 -c "SHOW max_connections"

 max_connections 
-----------------
 123
(1 row)

反映された

接続と認証 (CONNECTIONS AND AUTHENTICATION)

公式

listen_addresses

デフォルトlocalhost
- TCPはローカルループバックのみ許可
- リモートからは繋げない
他の特殊値
- 0:0:0:0: 任意のIPv4アドレスで接続許可
- ::: 任意のIPv6アドレスで接続許可
- *: 任意のIPアドレスで接続許可
- 空: Unix ドメインソケット通信でのみ接続可能
サーバ側のIPアドレスを指定することに注意
- ネットワークインタフェースが複数あったりするので
- クライアント側のIPアドレスではない
postmaster

port

デフォルト5432
先述のlisten_addressesで指定される全IPアドレスに対して共通
postmaster

max_connections

典型的にはデフォルト100
postmaster

クライアント接続デフォルト (CLIENT CONNECTION DEFAULTS)

search_path

スキーマ検索パスを設定する
【補】スキーマ: データベース中の名前空間みたいなやつ
- 公式/Schemas
- テーブル、型、関数、演算子等を含む
【補】スキーマ一覧のメタコマンドが\dnなのはたぶんnamespaceから来ているのでは

postgres@8040c0c21fcb:/$  psql -c "\dn"

  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

デフォルトで"$user", public

postgres@8040c0c21fcb:/$  psql -c "SHOW search_path"

   search_path   
-----------------
 "$user", public
(1 row)

設定可能タイミング: user
- SETで上書き可能

default_transaction_isolation

デフォルトread committed
設定可能タイミング: user

client_encoding

設定可能タイミング: user

エラー報告とログ取得 (ERROR REPORTING AND LOGGING)

log_destination

...
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'     # Valid values are combinations of
                    # stderr, csvlog, syslog, and eventlog,
                    # depending on platform.  csvlog
                    # requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off        # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

設定値(enum)
- stderr
- csvlog
  - logging_collector (boolean)がonであること
- syslog
- eventlog
  - windows用
設定可能タイミング: sighup

logging_collector

# This is used when logging to stderr:
#logging_collector = off        # Enable capturing of stderr and csvlog
                    # into log files. Required to be on for
                    # csvlogs.
                    # (change requires restart)

ログをファイルに書き出すか否か
デフォルトoff
log_destinationにcsvlogを設定する場合はonにする必要がある
設定可能タイミング: postmaster

log_directory

#log_directory = 'log'           # directory where log files are written,
                    # can be absolute or relative to PGDATA

デフォルト: $PGDATAに対する相対log/
$PGDATAに対する相対パス、または絶対パスを設定可能
設定可能タイミング: sighup

log_filename

#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                    # can include strftime() escapes

strftimeのエスケープが使える
設定可能タイミング: sighup

log_min_messages

ログレベル

# - When to Log -

#log_min_messages = warning     # values in order of decreasing detail:
                    #   debug5
                    #   debug4
                    #   debug3
                    #   debug2
                    #   debug1
                    #   info
                    #   notice
                    #   warning
                    #   error
                    #   log
                    #   fatal
                    #   panic

デフォルトwarning
下のものほど深刻
- warning
  - 想定外動作
- error
  - 特定のトランザクションで問題発生
  - そのトランザクションだけabortされる
  - 他のトランザクションに影響なし
- log
  - DBAが着目すべき動作ログ
- fatal
  - 特定のセッションで問題発生
  - そのセッションだけ切断される
  - 他のセッションに影響なし
- panic
  - 致命的なエラー発生
  - 全セッション強制切断
  - PostgreSQLは停止する
設定可能タイミング: superuser

log_line_prefix

ログ行頭に出力する内容

# - What to Log -

...

#log_line_prefix = '%m [%p] '       # special values:
                    #   %a = application name
                    #   %u = user name
                    #   %d = database name
                    #   %r = remote host and port
                    #   %h = remote host
                    #   %p = process ID
                    #   %t = timestamp without milliseconds
                    #   %m = timestamp with milliseconds
                    #   %n = timestamp with milliseconds (as a Unix epoch)
                    #   %i = command tag
                    #   %e = SQL state
                    #   %c = session ID
                    #   %l = session line number
                    #   %s = session start timestamp
                    #   %v = virtual transaction ID
                    #   %x = transaction ID (0 if none)
                    #   %q = stop here in non-session
                    #        processes
                    #   %% = '%'
                    # e.g. '<%u%%%d> '

デフォルト: '%m [%p] '
- タイムスタンプ(ミリ秒)とPID
  - 【補】Y-m-d H:i:s.uフォーマット
設定可能タイミング: sighup

SET文/SHOW文の使い方

SET文

postgres@8040c0c21fcb:/$ psql -c "\h SET"

Command:     SET
Description: change a run-time parameter
Syntax:
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

URL: https://www.postgresql.org/docs/12/sql-set.html

公式
SESSIONとLOCALの2種類ある
- 省略するとSET SESSION

	SET SESSION	SET LOCAL
トランザクション実行中	設定される	設定される
commit後	設定値残る	設定値残らない
rollback後	設定値残らない	設定値残らない

SHOW文

postgres@8040c0c21fcb:/$ psql -c "\h SHOW"

Command:     SHOW
Description: show the value of a run-time parameter
Syntax:
SHOW name
SHOW ALL

URL: https://www.postgresql.org/docs/12/sql-show.html

公式
そのセッションの設定値が表示される
- SET文で上書きされている場合、他のセッションでは異なる値が見えることがある
セッションデフォルト値の確認

postgres@8040c0c21fcb:/$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# SHOW search_path;

   search_path   
-----------------
 "$user", public
(1 row)

スキーマを作ってSETでsearch_path上書きしてみる

postgres=# CREATE SCHEMA exam_schema;
CREATE SCHEMA

postgres=# \dn

    List of schemas
    Name     |  Owner   
-------------+----------
 exam_schema | postgres
 public      | postgres
(2 rows)

postgres=# SET search_path TO 'exam_schema';
SET

設定反映を確認

postgres=# SHOW search_path;

 search_path 
-------------
 exam_schema
(1 row)

別のターミナルを開いて、別のセッションで確認してみる

postgres@8040c0c21fcb:/$ psql -c "SHOW search_path"

   search_path   
-----------------
 "$user", public
(1 row)

このセッションでは上書きしていないので、セッションデフォルトの"$user", publicが設定されている

pg_hba.conf

クライアント認証の設定
- hba: host-based authentication
公式
設定の書式

local      database  user  auth-method  [auth-options]
host       database  user  address  auth-method  [auth-options]
hostssl    database  user  address  auth-method  [auth-options]
hostnossl  database  user  address  auth-method  [auth-options]
host       database  user  IP-address  IP-mask  auth-method  [auth-options]
hostssl    database  user  IP-address  IP-mask  auth-method  [auth-options]
hostnossl  database  user  IP-address  IP-mask  auth-method  [auth-options]
hostgssenc database  user  IP-address  IP-mask  auth-method  [auth-options]
hostnogssenc database  user  IP-address  IP-mask  auth-method  [auth-options]

マッチするものが複数ある場合、ファイルの先頭側のものが評価される

接続方式(1列目)

local
- Unix ドメインソケット通信用の設定
- この設定がないとUnix ドメインソケット接続不可
host
- TCP/IP通信用の設定
- 後述のhostssl/hostnossl, hostgssenc/hostnogssencにもマッチする
- これを設定してクライアントを許可しても、PostgreSQLサーバー設定のlisten_addressesを適切に設定しないと接続できないことに留意する
  - 無設定時のデフォルトはlocalhostなのでローカルループバックのみ
hostssl/hostnossl
- 接続がSSLで暗号化されている/されていない場合にのみマッチ
hostgssenc/hostnogssenc
- 【補】PostgreSQL version 12から追加されたみたい
- 接続がGSSAPIで暗号化されている/されていない場合にのみマッチ
- GSSAPI: Generic Security Standard Application Programming Interface
  - (usually a part of the Kerberos installation)
  - 利用するためにはGSSAPIサポートを有効化してPostgreSQLをビルドすること
    - 公式/インストール手順
    - ./configureの--with-gssapiオプションでGSSAPIサポートの有無を設定できる

アドレス

TCP/IP接続の場合に設定
- CIDRアドレス
  - 192.168.100.0/24とか
- アドレス + ネットマスク
  - アドレス + 255.255.255.0とか

認証方式

trust
- 公式/Trust認証
- ざる認証
- 単一ユーザでローカル接続するとき有用
- 複数ユーザの場合は適さない
  - Unixドメインソケット通信に制限するならアリ
    - ファイルシステムのパーミッションで守れる
  - その場合、host 127.0.0.1 ...を削除すること
reject
- 全拒否
- 特定のホストをブラックリスト入りさせるのに有用
パスワード認証系
- 公式/パスワード認証
- scram-sha-256
  - challenge-response
    - 平文パスワードが盗聴されたりサーバ上から盗まれる心配はない
  - 暗号学的ハッシュでパスワードを永続化する
  - いちばん安全
  - しかし古いクライアントでサポートされていない
- md5
  - challenge-response
  - scram-sha-256よりはセキュアでない
    - ハッシュが盗まれる心配はある
    - MD5は今や安全でないので...
- password
  - パスワードを平文で送信する
  - ので、SSLで通信経路を暗号化していないとやばい
    - 【補】そのためにhostsslとかを使うのかな

2020-01-18

OSS-DB試験対策緑本 ch4 標準付属ツール

OSS-DB PostgreSQL 勉強メモ

www.shoeisha.co.jp

共通的なオプション
pg_ctl
ツールからPostgreSQLへの接続
- データベースユーザ
createuser
dropuser
createdb
dropdb
psql

共通的なオプション

-V (--version)
- バージョン表示

postgres@00aa48792429:~/data$ pg_ctl --version

pg_ctl (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1)

-? (--help)
- pg_ctl共通、および各サブコマンドのヘルプ表示
- 【所感】 -hじゃないのね
-t (--timeout=SECS)
- 最大待ち時間
- 未指定時のデフォルト60秒
- 時間内に処理が完了しない場合、pg_ctlのexitコードは0以外になる
-w --wait
- 操作の完了を待つ (デフォルト)
-W --no-wait
- 操作の完了を待たない
-D (--pgdata)
- データベースクラスタ指定

pg_ctl

PostgreSQLが稼働するホスト上でのみ実行可能
PostgreSQLの管理ユーザでのみ実行可能
- rootはPostgreSQLの管理ユーザになれないので実行不可

pg_ctl initdb

pg_ctl initdbはinitdbと同じ
initdbに渡すオプションを-oでまとめて渡す
- -D (--pgdata)はpg_ctl共通オプションなので直接渡せる

postgres@00aa48792429:~/data$ pg_ctl initdb -D /home/postgres/data4 -o "--encoding=UTF8 --no-locale"

pg_ctl start

pg_ctl共通オプションで最大待ち時間を秒数で指定できる
- -t(--timeout=SECS)
時間内に処理が完了しない場合でもPostgreSQLサーバの起動自体はバックグラウンドで行われる

postgres@00aa48792429:~/data$ pg_ctl start -t 0 -D /home/postgres/data4 -o "-p 5436"

waiting for server to start... stopped waiting
pg_ctl: server did not start in time
2020-01-18 18:39:52.041 UTC [978] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-01-18 18:39:52.042 UTC [978] LOG:  listening on IPv4 address "0.0.0.0", port 5436
2020-01-18 18:39:52.042 UTC [978] LOG:  listening on IPv6 address "::", port 5436
2020-01-18 18:39:52.046 UTC [978] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5436"
2020-01-18 18:39:52.059 UTC [979] LOG:  database system was shut down at 2020-01-18 18:39:44 UTC
2020-01-18 18:39:52.062 UTC [978] LOG:  database system is ready to accept connections

postgres@00aa48792429:~/data$ echo $?

pg_ctl startは異常終了している

【補】サーバ自体は起動している

postgres@00aa48792429:~/data$ pg_ctl status -D /home/postgres/data4

pg_ctl: server is running (PID: 978)
/usr/lib/postgresql/12/bin/postgres "-D" "/home/postgres/data4" "-p" "5436"

pg_ctl stop

-m (--mode=MODE)
- pg_ctl stop特有のオプション

Options for stop or restart:
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"

公式
3つのモードがある
- s, smart
  - すべてのアクティブなクライアントの切断を待つ
  - すべてのオンラインバックアップの完了を待つ
  - サーバーがhot standbyならば、リカバリとストリームレプリケーションの終了を待つ
- f, fast (デフォルト)
  - アクティブなクライアントの切断を待たない
    - 実行中のトランザクションはロールバックされ、全てのクライアントは強制的に切断される
  - オンラインバックアップの完了を待たない
- i, immediate
  - 全サーバープロセスを直ちにabortする
  - 次回サーバー起動時にクラッシュリカバリが必要になる
kill -9使っちゃだめ

pg_ctl restart

PostgreSQL再起動
すでに停止している場合は単に起動のみ行う

pg_ctl reload

【補】SIGHUP相当
設定ファイルを再読み込みさせる
- postgresql.conf
- pg_hba.conf
reloadではなくrestartでないと反映されない設定項目もある
- 【補】接続とかリソースまわりとか

pg_ctl status

postgres@00aa48792429:~/data$ pg_ctl start -D /home/postgres/data4 -o "-p 5555"

waiting for server to start....2020-01-18 20:26:18.830 UTC [1105] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-01-18 20:26:18.830 UTC [1105] LOG:  listening on IPv4 address "0.0.0.0", port 5555
2020-01-18 20:26:18.830 UTC [1105] LOG:  listening on IPv6 address "::", port 5555
2020-01-18 20:26:18.833 UTC [1105] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5555"
2020-01-18 20:26:18.846 UTC [1106] LOG:  database system was shut down at 2020-01-18 20:25:34 UTC
2020-01-18 20:26:18.850 UTC [1105] LOG:  database system is ready to accept connections
 done
server started

postgres@00aa48792429:~/data$ pg_ctl status -D /home/postgres/data4

pg_ctl: server is running (PID: 1105)
/usr/lib/postgresql/12/bin/postgres "-D" "/home/postgres/data4" "-p" "5555"

postgres@00aa48792429:~/data$ pg_ctl stop -m smart  -D /home/postgres/data4

2020-01-18 20:26:40.193 UTC [1105] LOG:  received smart shutdown request
waiting for server to shut down....2020-01-18 20:26:40.198 UTC [1105] LOG:  background worker "logical replication launcher" (PID 1112) exited with exit code 1
2020-01-18 20:26:40.198 UTC [1107] LOG:  shutting down
2020-01-18 20:26:40.213 UTC [1105] LOG:  database system is shut down
 done
server stopped

postgres@00aa48792429:~/data$ pg_ctl status -D /home/postgres/data4

pg_ctl status -D /home/postgres/data4
pg_ctl: no server running
postgres@00aa48792429:~/data$

pg_ctl kill

シグナル送信
Windows環境で、killコマンドがない場合に

postgres@00aa48792429:~/data$ pg_ctl start -D /home/postgres/data4 -o "-p 5555"

waiting for server to start....2020-01-18 20:30:22.999 UTC [1127] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-01-18 20:30:22.999 UTC [1127] LOG:  listening on IPv4 address "0.0.0.0", port 5555
2020-01-18 20:30:22.999 UTC [1127] LOG:  listening on IPv6 address "::", port 5555
2020-01-18 20:30:23.003 UTC [1127] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5555"
2020-01-18 20:30:23.017 UTC [1128] LOG:  database system was shut down at 2020-01-18 20:26:40 UTC
2020-01-18 20:30:23.021 UTC [1127] LOG:  database system is ready to accept connections
 done
server started

プロセス番号はpg_ctl statusで取得できる

postgres@00aa48792429:~/data$ pg_ctl status -D /home/postgres/data4

pg_ctl: server is running (PID: 1127)
/usr/lib/postgresql/12/bin/postgres "-D" "/home/postgres/data4" "-p" "5555"

1127にhangup送信して設定再読込
- pg_ctl reload相当

postgres@00aa48792429:~/data$ pg_ctl kill HUP 1127

2020-01-18 20:31:05.598 UTC [1127] LOG:  received SIGHUP, reloading configuration files
postgres@00aa48792429:~/data$

利用可能なシグナル

Allowed signal names for kill:
  ABRT HUP INT KILL QUIT TERM USR1 USR2

pg_ctlコマンドとの対応
- TERM
  - pg_ctl stop -m smart
  - 【補】killコマンドデフォルト、15。正常終了
- INT
  - pg_ctl stop -m fast
  - 【補】Ctrl + Cのやつ
- QUIT
  - pg_ctl stop -m immediate
  - 【補】終了とコアダンプ

ツールからPostgreSQLへの接続

postgres@00aa48792429:~/data$ psql -?

...
Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
...

-h (--host)
- 接続先ホスト
- 省略時、PGHOST環境変数
- それもない場合、ローカルのUnix ドメインソケット
-p (--port)
- ポート番号
- 省略時、PGPORT環境変数
- それもない場合、コンパイル時に指定したデフォルトポート番号
  - configure時に--with-pgport=NUMBERで指定していなければ5432
-U (--username)
- データベースユーザ名
- 省略時、PGUSER環境変数
- それもない場合、実行したOSユーザ名
  - ログイン権限があること

データベースユーザ

OSとは別にユーザの概念がある
データベースクラスタで共通
ユーザには権限を設定できる
- GRANT/REVOKE文で設定する、テーブル単位のものとは別
【補】role
【補】\duメタコマンドで見れるやつ

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

PostgreSQLにはグループ、ユーザの概念がある
現在ではすべてRoleに統合されている
- ユーザに関する操作を行った際にroleと出力されるのはそのため

createuser

公式
CREATE ROLE文のラッパ

Usage:
  createuser [OPTION]... [ROLENAME]

ROLENAME
- ユーザ名
  - 先述の通り、ユーザとグループは「ロール」に統合されている
  - 【所感】Composite Patternみたいな
OPTION
- 接続オプション、createuser固有オプション
ロールに関するオプション

ロール	付与する	付与しない
Superuser	`-s`, `--superuser`	`-S`, `--no-superuser` (default)
Create DB	`-d`, `--createdb`	`-D`, `--no-createdb` (default)
Create role	`-r`, `--createrole`	`-R`, `--no-createrle` (default)
Login	`-l`, `--login` (default)	`-L`, `--no-login`

それ以外のオプション
- -P (--pwprompt)
  - パスワードを設定する
  - 【補】-pじゃないのはportに使われてるから
- --interactive
  - 対話的に設定する場合に指定
- -e (--echo)
  - createuserが実行したSQLを出力

postgres@00aa48792429:~/data$ createuser -S -d -R -l -e aoba

SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE aoba NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;

postgres@00aa48792429:~/data$ psql 

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 aoba      | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

【補】no-loginはグループ等で使用する

postgres@00aa48792429:~/data$ createuser -L gochiusa
postgres@00aa48792429:~/data$ createuser syaro -g gochiusa

postgres@00aa48792429:~/data$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 aoba      | Create DB                                                  | {}
 gochiusa  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {gochiusa}

dropuser

Usage:
  dropuser [OPTION]... [ROLENAME]

削除するには、Create role権限を持っていること
- Superuserの削除にはSuperuser権限も必要

postgres@00aa48792429:~/data$ dropuser -i -e

Enter name of role to drop: aoba

Role "aoba" will be permanently removed.
Are you sure? (y/n) y

SELECT pg_catalog.set_config('search_path', '', false);
DROP ROLE aoba;

createdb

公式
createdb固有のオプション

  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy

-O, --owner=OWNER
- データベースの所有者
- スーパーユーザでのみ指定可能
-T, --template=TEMPLATE
- template0とか指定するやつ
DB作れるユーザを作る

postgres@00aa48792429:~/data$ createuser -r -d -l -S  aoba

postgres@00aa48792429:~/data$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \du

                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 aoba      | Create role, Create DB                                     | {}
 gochiusa  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {gochiusa}

青葉ちゃんでDBつくる

postgres@00aa48792429:~/data$ createdb -U aoba --template=template0

データベース名を省略するとOSユーザ名と同名のデータベースが作成される
- postgresは作成済なのでエラー

createdb: error: database creation failed: ERROR:  database "postgres" already exists

postgres@00aa48792429:~/data$ createdb -U aoba -e --template=template0 aoba

SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE aoba TEMPLATE template0;

作成したデータベースを確認

postgres@00aa48792429:~/data$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \l

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 aoba      | aoba     | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

postgres=# \q

aobaはスーパーユーザではないので、他の人のデータベースは作成できない

postgres@00aa48792429:~/data$ createdb -U aoba -O syaro -e syaro

SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE syaro OWNER syaro;
createdb: error: database creation failed: ERROR:  must be member of role "syaro"

スーパーユーザでなら作れる

postgres@00aa48792429:~/data$  createdb -O syaro -e syaro

SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE syaro OWNER syaro;

dropdb

DB消すやつ

postgres@00aa48792429:~/data$ dropdb --interactive -e syaro

Database "syaro" will be permanently removed.
Are you sure? (y/n) y
y
SELECT pg_catalog.set_config('search_path', '', false);
DROP DATABASE syaro;

psql

データベースへの接続と切断

PostgreSQLへの接続、SQLコマンドの発行、結果の表示などを行うコマンド
-l, --list
- \lメタコマンドと同じ

postgres@00aa48792429:~/data$ psql -l

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 aoba      | aoba     | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

-c, --command
- 指定したコマンド(メタコマンド、SQL)を実行して終了
  - メタコマンドとSQL混在は不可能

postgres@00aa48792429:~/data$ psql -c "\l"

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 aoba      | aoba     | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

-f, --file
- ファイルからコマンドを流し込む
- メタコマンドとSQLコマンド混在可能

postgres@00aa48792429:~/data$ echo '\l' > commands && psql -f commands && rm commands

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 aoba      | aoba     | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

-U, --username
- 接続ユーザ名 (共通オプション)
- スーパーユーザだとプロンプトが=#
- それ以外だとプロンプトが=>

postgres@00aa48792429:~/data$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \q

postgres@00aa48792429:~/data$ psql -U aoba -d aoba

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

aoba=> \q

-d, --dbname
- 接続するデータベース名
- -dを省略すると、最初の引数がデータベース名として解釈される

postgres@00aa48792429:~/data$ psql aoba

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

aoba=# \q

SQLコマンドの入力

postgres@00aa48792429:~/data$ psql -U aoba

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

aoba=> SELECT current_user, current_date;
SELECT current_user, current_date;
 current_user | current_date 
--------------+--------------
 aoba         | 2020-01-19
(1 row)

セミコロンまでで1文

aoba=> SELECT
SELECT
aoba-> current_user
current_user
aoba-> ,
,
aoba-> current_date
current_date
aoba-> ;
;
 current_user | current_date 
--------------+--------------
 aoba         | 2020-01-19
(1 row)

aoba=> \q

2行目以降、=>から->に変わる
- SQLコマンドが最後まで入力されていないことを意味する

メタコマンド

こちらは改行で終了を解釈
規則的な名前の一覧系コマンド

メタコマンド	説明
\du	データベースユーザ一覧
\dt	テーブル一覧
\di	インデックス一覧
\dv	ビュー一覧
\ds	シーケンス一覧
\dS	システムカタログ一覧
\df	関数一覧
\dp	権限(privileges)一覧

system catalog
- スキーマメタデータ、内部的な情報(internal bookkeeping information)を格納する
その他

メタコマンド	説明
\l (\list)	データベース一覧
\d PATTERN	テーブル、インデックス、ビュー、シーケンスの構成情報一覧 PATTERNには`*`とか`?`とかが使える
\d	テーブル、ビュー、シーケンス一覧
\z	\dpのエイリアス
\copy	PostgreSQLとpsqlの間でテーブルデータをコピー
\password [USERNAME]	指定のユーザ(省略時は現在のユーザ)のパスワード変更
\c (\connect) [DBNAME]	指定のデータベース(省略時は現在接続中のデータベース)に再接続
\x	拡張表示モードのトグル
\?	メタコマンドのヘルプ
\h	SQLコマンドのヘルプ

拡張表示モード
- 結果の1行を複数行に分けて表示するやつ
- 【補】MySQLの\Gみたいなやつ

aoba=# \x

Expanded display is on.

aoba=# \du

List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | aoba
Attributes | Create role, Create DB
Member of  | {}
-[ RECORD 2 ]----------------------------------------------------------
Role name  | gochiusa
Attributes | Cannot login
Member of  | {}
-[ RECORD 3 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}
-[ RECORD 4 ]----------------------------------------------------------
Role name  | syaro
Attributes | 
Member of  | {gochiusa}

cf. 拡張表示オフ

aoba=# \x

Expanded display is off.

aoba=# \du

                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 aoba      | Create role, Create DB                                     | {}
 gochiusa  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {gochiusa}

コマンドのバッチ実行

psqlの-fコマンドでファイルを流し込む

postgres@00aa48792429:~$ cat sample.sql

select current_user, current_date;
\du

postgres@00aa48792429:~$ psql -f sample.sql

 current_user | current_date 
--------------+--------------
 postgres     | 2020-01-19
(1 row)

                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 aoba      | Create role, Create DB                                     | {}
 gochiusa  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {gochiusa}

2020-01-17

OSS-DB試験対策緑本 ch3 インストール

OSS-DB PostgreSQL 勉強メモ

www.shoeisha.co.jp

PostgreSQLのインストール
データベースクラスタ
- PG_VERSION
- base/
- global/
- log/
- pg_wal/
- postgresql.conf
- pg_hba.conf
- postmaster.pid
データベース
テンプレートデータベース
initdb
- ロケール
- エンコーディング

※インストール方法そのものは試験範囲外

PostgreSQLのインストール

事前準備

postgresユーザ作っとく
ソースコードからビルドする場合に必要なもの
- build-essential
  - なかみ
    - dpkg-dev
    - g++
    - gcc
    - lib6-dev
    - make
- readline
  - libreadline6-devとか
  - psqlでコマンドライン編集・ヒストリ機能を利用するのに必要
  - configureの --without-readline オプションで無効化可能
- zlib
  - zlib1g-devとか
  - pg_dump, pg_restore機能に必要
  - configureの --without-zlib オプションで無効化可能

ソースコードの取得

tarball拾ってくる

インストール

./configure <オプション>
- --prefixでインストール先を指定
make
- make allとおなじ
- 【補】contribも利用したければmake world
make install

インストール後の設定

パス通す
- PATHに/usr/local/bin/pgsql/bin
- LD_LIBRARY_PATHに/usr/local/bin/pgsql/lib

起動と停止

公式
データベースクラスタの作成: initdb

$ initdb --help

 [-D, --pgdata=]DATADIR     location for this database cluster
 ...
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
 ...

$ initdb --pgdata=/home/postgres/data --locale=C

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/postgres/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /home/postgres/data -l logfile start

例: ポート5433でサーバ起動

pg_ctl start --pgdata /home/postgres/data -w -o "-p 5433"

psqlでサーバに接続

postgres@00aa48792429:/$ psql -p 5433


psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# SELECT now();

              now              
-------------------------------
 2020-01-18 16:26:41.277869+00
(1 row)

postgres=# \q

postgres@00aa48792429:/$

【補】同一のデータベースクラスタに対して複数のPostgreSQLサーバーを起動しようとするとエラー
- postmaster.pidファイルで判別している

postgres@00aa48792429:~/data$ pg_ctl start --pgdata /home/postgres/data -w -o "-p 5434"

pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2020-01-18 17:46:34.325 UTC [844] FATAL:  lock file "postmaster.pid" already exists
2020-01-18 17:46:34.325 UTC [844] HINT:  Is another postmaster (PID 833) running in data directory "/home/postgres/data"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.

停止

postgres@00aa48792429:~/data$ pg_ctl stop --pgdata /home/postgres/data

2020-01-18 17:48:47.202 UTC [833] LOG:  received fast shutdown request
waiting for server to shut down....2020-01-18 17:48:47.205 UTC [833] LOG:  aborting any active transactions
2020-01-18 17:48:47.205 UTC [833] LOG:  background worker "logical replication launcher" (PID 840) exited with exit code 1
2020-01-18 17:48:47.206 UTC [835] LOG:  shutting down
2020-01-18 17:48:47.221 UTC [833] LOG:  database system is shut down
 done
server stopped

【補】指定のデータベースクラスタに対してPostgreSQLサーバが起動していない場合、エラー

postgres@00aa48792429:~/data$ pg_ctl stop --pgdata /home/postgres/data

pg_ctl: PID file "/home/postgres/data/postmaster.pid" does not exist
Is server running?
postgres@00aa48792429:~/data$

アンインストール

ソースコードのディレクトリで

$ make uninstall

【補】なかみ
- config/Makefile

...
uninstall:
    rm -f '$(DESTDIR)$(pgxsdir)/config/install-sh'
    rm -f '$(DESTDIR)$(pgxsdir)/config/missing'

けしてるだけ

データベースクラスタ

ls -lA $PGDATA

total 132
drwx------  5 postgres postgres  4096 Jan 18 16:05 base
drwx------  2 postgres postgres  4096 Jan 18 16:26 global
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_commit_ts
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_dynshmem
-rw-------  1 postgres postgres  4537 Jan 18 16:05 pg_hba.conf
-rw-------  1 postgres postgres  1636 Jan 18 16:05 pg_ident.conf
drwx------  4 postgres postgres  4096 Jan 18 16:10 pg_logical
drwx------  4 postgres postgres  4096 Jan 18 16:05 pg_multixact
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_notify
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_replslot
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_serial
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_snapshots
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_stat
drwx------  2 postgres postgres  4096 Jan 18 16:35 pg_stat_tmp
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_subtrans
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_tblspc
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_twophase
-rw-------  1 postgres postgres     3 Jan 18 16:05 PG_VERSION
drwx------  3 postgres postgres  4096 Jan 18 16:05 pg_wal
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_xact
-rw-------  1 postgres postgres    88 Jan 18 16:05 postgresql.auto.conf
-rw-------  1 postgres postgres 26588 Jan 18 16:05 postgresql.conf
-rw-------  1 postgres postgres    36 Jan 18 16:05 postmaster.opts
-rw-------  1 postgres postgres    94 Jan 18 16:05 postmaster.pid

`PG_VERSION`

postgres@00aa48792429:~/data$ cat PG_VERSION

メジャーバージョンが格納されている
- 【補】メジャーバージョンが異なるとファイル形式が異なり、互換性がないため

`base/`

各データベースのサブディレクトリを格納

postgres@00aa48792429:~/data$ ls -lA  ${PGDATA}/base

total 20
drwx------ 2 postgres postgres  4096 Jan 18 16:05 1
drwx------ 2 postgres postgres  4096 Jan 18 16:05 13407
drwx------ 2 postgres postgres 12288 Jan 18 16:26 13408

`global/`

クラスタ内のデータベース間で共有するデータ
- ユーザ情報等

`log/`

ログ

`pg_wal/`

WAL
Write Ahead Log
- トランザクションログ

`postgresql.conf`

公式

`pg_hba.conf`

公式
クライアント認証情報を設定するファイル

postgres@00aa48792429:~/data$ cat $PGDATA/pg_hba.conf

【補】postgresコンテナのデフォルト

...

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all trust

postmaster.pid

起動中のPostgreSQLサーバのPIDなどを記録

postgres@00aa48792429:~/data$ cat postmaster.pid

cat postmaster.pid
1
/var/lib/postgresql/data
1579363515
5432
/var/run/postgresql
*
  5432001    163840
ready

【補】同じデータベースクラスタに対して複数のPostgreSQLを起動できないようにする役割がある

データベース

postgres=# SELECT oid, datname FROM pg_database;

  oid  |  datname  
-------+-----------
 13408 | postgres
     1 | template1
 13407 | template0
(3 rows)

【補】oid: Object ID
- base/ディレクトリ内のものに対応しているっぽい

postgres@00aa48792429:~/data$ ls -lA  ${PGDATA}/base

total 20
drwx------ 2 postgres postgres  4096 Jan 18 16:05 1
drwx------ 2 postgres postgres  4096 Jan 18 16:05 13407
drwx------ 2 postgres postgres 12288 Jan 18 16:26 13408

【補】メタコマンドでも出せる

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

テンプレートデータベース

データベースを作成する際、テンプレートを元にコピーする
- デフォルトはtemplate1
template1: 変更可能、デフォルトはtemplate0と同じ内容
template0: 変更不可
- template1に登録済のデータベースオブジェクトをコピーしたくないときに選択する
- 【補】Null Object Patternみたいなかんじ
  - 「テンプレートを選択しない」のではなく、「空のテンプレート」を選択することで設計を単純化しているものと思われる

initdb

initdb --help

initdb initializes a PostgreSQL database cluster.

Usage:
  initdb [OPTION]... [DATADIR]

Options:
  -A, --auth=METHOD         default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP connections
      --auth-local=METHOD   default authentication method for local-socket connections
 [-D, --pgdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
  -T, --text-search-config=CFG
                            default text search configuration
  -U, --username=NAME       database superuser name
  -W, --pwprompt            prompt for a password for the new superuser
  -X, --waldir=WALDIR       location for the write-ahead log directory
      --wal-segsize=SIZE    size of WAL segments, in megabytes

Less commonly used options:
  -d, --debug               generate lots of debugging output
  -k, --data-checksums      use data page checksums
  -L DIRECTORY              where to find the input files
  -n, --no-clean            do not clean up after errors
  -N, --no-sync             do not wait for changes to be written safely to disk
  -s, --show                show internal settings
  -S, --sync-only           only sync data directory

Other options:
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit

If the data directory is not specified, the environment variable PGDATA
is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.

rootでは実行できない

root@00aa48792429:/# initdb

initdb: error: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.

(PostgreSQLの)管理ユーザ
- initdbを実行してデータベースクラスタを作成したOSユーザ
- postgresで実行したならpostgres氏
管理ユーザだけが$PGDATAディレクトリへのアクセス権を有する
- 【補】 postgres:postgresの700

postgres@00aa48792429:~/data$ ls -ld $PGDATA

drwx------ 19 postgres postgres 4096 Jan 18 16:05 /var/lib/postgresql/data

PostgreSQLの起動/停止などを行えるのも管理ユーザのみ
デフォルトで、管理ユーザと同名のデータベースユーザがスーパーユーザ権限で作成される

postgres=# \du
\du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

別名のスーパーユーザを作成したい場合は-U (--username)で指定
- 【補】スーパーユーザ名によらず、デフォルトのデータベースはpostgres

postgres@00aa48792429:~/data$ initdb /home/postgres/data2 -U aoba
postgres@00aa48792429:~/data$ psql -p 5434 -U aoba -d postgres

ロケール

      --locale=LOCALE       set default locale for new databases
      --no-locale           equivalent to --locale=C

ロケールとは
- 文字の並び順
  - 例: 全角数字を半角数字と同様に数値順でソートしたい場合
    - 通常は辞書順になる
- 文字の分類
- ログメッセージの言語
- 通貨や数字
- 日時の書式
使用可能なロケール一覧

postgres@00aa48792429:~/data$ locale -a

C
C.UTF-8
en_US.utf8
POSIX

ロケールは無効が推奨
- 検索性能が低くなる等、問題が発生しうるため
- --no-locale または --locale=C
ロケールはデータベース単位で指定できる
- 【補】createdbコマンドの--localeオプションで指定できる

エンコーディング

  -E, --encoding=ENCODING   set default encoding for new databases

データベース単位
エンコーディングとロケールとは互換性があること
- さもないとエラーの原因になる
- ロケール無効時は考慮する必要なし
C/S両サイドでのエンコーディングがある
- データベースエンコーディング(サーバエンコーディング)
  - 文字をバイト列としてデータベースに格納する際のエンコーディング
- クライアントエンコーディング
  - 文字の自動変換とか
  - 公式

client_encoding (string)

Sets the client-side encoding (character set). The default is to use the database encoding. The character sets supported by the PostgreSQL server are described in Section 23.3.1.

公式/サポートされている文字セット
- 通常、UTF8, EUC_JP, SJISなど
- SJISはクライアントサイドのみ

2020-01-16

OSS-DB試験対策緑本 ch2 データベースの基礎知識

PostgreSQL OSS-DB 勉強メモ

www.shoeisha.co.jp

データベースマネジメントシステム
- データベースマネジメントシステムの機能
データモデル
- 概念データモデル
- 論理データモデル
SQLの基礎
- SQLの規格
- SQLの分類
データベース設計
- 概念データモデルの作成
- 論理データモデルへの変換
正規化

データベースマネジメントシステム

データを一元管理
- 安全性・一貫性の確保
  - 整合性
  - 権限
- 生産性の向上
  - アプリケーションごとにデータ管理を実装しなくていい
- データ操作の標準的な手段

データベースマネジメントシステムの機能

5つの柱
- データの機密保護を実現
  - 【補】GRANT/REVOKE
  - きめ細やか
    - リソースごとユーザごとに権限付与
    - 列単位
- トランザクションを制御
  - 同時実行制御による不整合防止も
- データの整合性の維持
  - 形式チェック、データの相互関係のチェックなど
  - 【補】ドメイン、check制約、FK制約とかかな
- 障害からの安全な復旧
  - 障害発生直前の状態に復旧させることができる
    - バックアップとログを用いて
- アプリケーションプログラムのためのインタフェースの提供
  - DMLの提供

データモデル

対象世界
- データベースを構築する範囲
データモデル
- 対象世界をモデル化したもの
- 概念データモデル(概念モデル)
  - 対象世界を抽象化
  - 特定の種類のDBMSには依存しない
- 論理データモデル(論理モデル)
  - 概念データモデルを、データベースとして実装可能な形に変換したもの

概念データモデル

ERモデルがよく用いられる
- Entity (実体)
- Relationship (関連)
  - カーディナリティ
    - 関連の多重度
    - 1とか多とか

論理データモデル

DBMSで実装可能なデータモデル
当然、DBMSに依存
- RDBMSは一種に過ぎない
種類
- 階層モデル
  - 親と子は1対多
- ネットワークモデル
  - 親と子は多対多
- 関係モデル
  - データの論理的な構造の決定を遅延でき、柔軟性があるのが特徴
    - 【補】JOINして初めて階層構造が生じる、的な意味かな
    - cf. 階層モデル、ネットワークモデルではデータベース構築時にデータの論理的な構造が決定される
関係モデルの言葉
- tuple、組、row
- attribute、属性、column
- domain、定義域
- degree、次数
  - attributeの数
- cardinality、基数
  - tupleの数

SQLの基礎

SQLの規格

ANSIとISOによる標準化
例: SQL:2016
- PTF: Polymorphic Table Functionとか

SQLの分類

DDL: Data Definition Language
- CREATE
- ALTER
- DROP
- 【補】TRUNCATE TABLEもこれ
  - cf. 全件DELETEはDML
DML: Data Manipulation Language
- CRUDの4つ
DCL: Data Control Language
- GRANT/REVOKE
- BEGIN/COMMIT/ROLLBACK

データベース設計

概念データモデルの作成

ERDを書く
この時点では多対多とかは気にしない
- RDBMS依存の図ではないから
属性はまだEntityを具現できる最低限でいい

論理データモデルへの変換

RDBMS上で実装できるところまで落とし込む
- 多対多を連関Entityで1対多にほぐす
- 詳細な属性も明確化
- 属性のデータ型やデータ長も明確化
- 正規化

正規化

更新時異常を防ぐために行う
更新時異常とは
- 冗長データ
- 不整合
「更新」
- 広義(updateだけじゃない)
- CRUDのR以外ぜんぶ

候補キーと主キー

候補キーはNULLを含むこともあるっぽい

非正規形(非第一正規形)

繰り返し項目が存在する
- tel1, tel2, tel3 カラムとか
複数の値を含む属性から構成される
- カンマ区切りとか

第一正規形

【補】定義は「表をRelationとして扱える」こと
- NULLが含まれると第一正規形違反
繰り返し項目がない
複数の値を含む属性がない

第二正規形

部分関数従属がないこと
部分関数従属
- 候補キー{A, B}に対して A -> Xなる関数従属 (Xは非キー属性)

第三正規形

推移的関数従属がないこと
推移的関数従属
- 非キー属性から非キー属性への関数従属

ボイスコッド正規形(BCNF)

非キー属性から、候補キーを構成する属性への関数従属がないこと
- 候補キー{A, B}{, 非キー属性Xに対してX->A`なる関数従属があったりするとBCNFではない
ボイスコッド正規形を目指すと非可逆になってしまうことがある
無損失分解になるように注意する

2020-01-15

OSS-DB試験対策緑本 ch1

OSS-DB PostgreSQL 勉強メモ

www.shoeisha.co.jp

一般的特徴
- オープンソースデータベースの普及
- PostgreSQLの特徴
ライセンス
コミュニティと情報収集

一般的特徴

オープンソースデータベースの普及

商用だけじゃなくなったね、という話
自分たちで育てる

PostgreSQLの特徴

オープンソース
長い歴史
- history
  1. POSTGRES (1986)
    - カリフォルニアバークレー校 Michael Stonebraker
  2. Postgres95 (1995)
  3. PostgreSQL 6.0 (1996)
  4. PostgreSQL 11 (2018)
標準SQLの大部分ややその他の最新機能のサポート
- Supported Features
  - Recursive queryとか
様々なユーザ拡張
- Arrays of user-defined types
- User-defined cast functions
- User-defined functions with no overloading
- User-defined stored procedures with no overloading
標準準拠
- featuresにいろいろ書いてある
扱いやすいライセンス
- 無償
  - BSDライク
  - 商用/非商用問わず
多言語対応
- 日本語含むエンコーディングに対応
  - 【補】CHAR(1)は1バイトではなく1文字
マルチプラットフォーム
- Linux, Window等対応
  - 【補】Windows対応は8.0からだったはず
    - それより前はCygwinとか必要だった
レプリケーション
- 同期/非同期レプリケーション
  - PostgreSQL 9系から
  - ホットスタンバイとストリーミングレプリケーション
  - streaming replicaton
- ロジカルレプリケーション
  - PostgreSQL 10から
  - logical replication

ライセンス

license

PostgreSQL is released under the PostgreSQL License, a liberal Open Source license, similar to the BSD or MIT licenses.

BSDベース
- BSD, MITに似ている

PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

Portions Copyright © 1996-2020, The PostgreSQL Global Development Group

Portions Copyright © 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

著作権表示
- PostgreSQL Global Development Group
- カリフォルニア大
使用・複製・改変・再配布の許諾
免責
無保証

コミュニティと情報収集

開発コミュニティ

OSSなので開発もオープンな場で
- メーリス
- face to faceも
PostgreSQL Global Development Group
- 【補】PostgreSQLライセンス条項の中の著作権表示にもあった名前
- 誰でも参加できる
  - メーリス登録にも審査等なし
- ソースコード・バイナリコードを配布している
  - 開発中のものもダウンロード可
  - 【補】github
    - 自分でビルドすれば手に入る

バージョン

versioning policy
バージョニングが途中から変わった
- ~9系
  - x.y.z
  - x.yが「メジャー」
    - 【所感】だから「9『系』」って言うんですね
  - zが「マイナー」
- 10~
  - x.z
  - xが「メジャー」
  - zが「マイナー」
開発中のものはalpha, beta, release candidate

Major versions usually change the internal format of system tables and data files. These changes are often complex, so we do not maintain backward compatibility of all stored data

メジャーアップデートは永続化データに後方互換なし
- dump/restore
- pg_upgradeモジュールでデータベースクラスタをバージョンアップする
マイナーアップデートでは互換あり
- ふつうdump/restore不要
  - サーバ停止、バイナリ差し替え、サーバ再起動でOK
- アップグレードのリスクを低減するために修正対象は(重大なものに)絞られる
  - 頻繁に見舞われる不具合
  - セキュリティ問題
  - データ破損

マニュアル

オンラインマニュアル
原文は英文
フランス語、日本語翻訳版もある

ユーザ会

利用者始点のコミュニティ
日本PostgreSQLユーザ会(JPUG)
- NPO法人
Let's Postgres