OSSDB 間違えた問題 テスト

スキーマの行数確認コマンド

select relname, n_live_tup from pg_stat_user_tables where schemaname=<schema_name>;

ロジカルレプリケーション

create subscription <name> for table sample1,sample2
create publication <name> sub1 connection 'host=,port=5432,user=postgres,dbname=postgres' publication <name> with (enabled=false);

・with句
enabled=falseとするとサブスクリプションは作成されるがレプリケーションの動作は開始されない

複製元と複製先でDB名が異なっておいても問題ない。
create publicationのときに名称がかぶっていてはいけない（エラーになる）

論理バックアップとリストアにおけるコマンドの関係

	pg_dump	pg_dumpall
形式	テキスト形式以外	テキスト形式
リストア	pg_restore	psqlコマンド

pg_dumpall
→
・パックアップに設定ファイルは含まれない
・postgresqlを稼働したまま起動しないといけない（論理バックアップはそう）
・テキスト形式で出力される

\copyコマンド

クライアントとサーバー間
delimiter
delimiter as

pg_dump

[主な接続オプション]

connect_opt	exp.	環境変数	デフォルト
-U		PGUSER	OS
-h			UNIX
-d			DB名
-p		PGPORT	5432

[pg_dumpの主なオプション]

opt.	説明
-F	省略するとテキスト形式
p	plain
c	カスタム形式(バイナリ形式)
t	tar形式
-f	バックアップの出力先指定

pg_restore

オプション	説明
-d --dbname	リストアを行う先のデータベース名を指定。省略→テキスト形式のSQL文が出力される
-c --clean	リストア前にデータベースオブジェクト削除
-j	リストア処理を同時に実行するジョブ数を指定
-1 --single-transaction	リストア処理を一つのトランザクションとして実行

バックアップ＆リストアの流れ

pg_dump -Fc -f file2.dump
dropdb postgres
createdb postgres
psql
>>\dt
>>did not have relation.
>>\q
pg_restore -j 2 -d postgres file2.dump

メジャーバージョンアップ時のバックアップについて
まずファイルの構成が変わる可能性がある。そのため物理バックアップのようなDB情報を保存したデータファイルを直接使用するバックアップ方法は整合性が失うことがある。そのため論理バックアップを行う。そうすることでテーブル構造とデータを意識したSQLファイルやバイナリファイルを出力し、移行がスムーズにできる。

物理バックアップ
→オフラインでtar rsync、オンラインでpg_basebackupなどの方法がある。
論理バックアップ
→pg_dump,pg_dumpallを使用

PITR（Point time Recovery）の際に必要な準備

para	説明	デフォルト
wal_level	replica or logical	replica
archice_mode	on or always	off
archive_command	walアーカイブとして保存するシェルコマンドの指定　ex)'cp %p[path]/%'	空文字

メジャーバージョン、マイナーバージョン

マイナーヴァージョンアップは1回/2,3か月程度の頻度。

・リリースノート
各リリースでなされた重要な変更点や、主要機能や移行に関する問題点が記載される。

Postgresqlの特徴	詳細
多言語対応	日本語を含む、様々なencodingに対応している。
OSS	複製、改変自由。ソースコードの公開不要。無償の商用利用。
ライセンスの種類	BSDライセンスをベースにしたPostgreSQLライセンス。再配布する際には著作権表示と条文を表示しなければならない。完全に著作権を放棄しているわけではない。MITライセンスに似ている。
マニュアル	ネット上にオンラインマニュアルとして掲載されている。

DBMSの役割

DBMSの主な機能	詳細
整合性維持	不正なデータや重複データをチェックし、整合性を保つ
障害回復機能	障害発生時に復旧を行い障害発生前の状態に回復する。バックアップやリカバリ機能によりデータベースを復元する
トランザクション管理	トランザクション実行中の一連の処理を途中で停止しないよう、データベースの操作の一貫性を保証する
同時実行制御	複数のユーザが同時に実行しても矛盾が生じないようにする。DB上の不整合の発生を防ぐ。

pg_ctl restart

最大の待ち時間に停止が完了しない場合は失敗として処理されるが、Postgresqlの停止は引き続き行われる。

pg_resetwal

walファイルについて
トランザクションのログを残すための一般的な手法。
利点（https://www.postgresql.jp/document/8.0/html/wal.htmlより）
・ディスクへの書き込みが大幅に減る
・データページの一貫性
１．存在しないテーブル行をインデックス行を指す
２．分割処理中にインデックス行が失われる
３．部分的にしかデータぺーじが書かれていないために完全にテーブルやインデックスのページの内容が壊れてしまう。

コマンド	説明
-D --pgdata	対象となるデータベースクラスタを指定する
-n --dry-run	実際には変更処理を行わない。変更内容の出力のみを行う
-f --force	制御情報が読み取れない場合でも強制的に実行する

dropuser

データベースやテーブルを所有するユーザーはdropuser ができない。

postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | tbl  | table | postgres
postgres=# alter table tbl owner to kuma;
ALTER TABLE
postgres@9dc74f27d78a:/root$ dropuser kuma
dropuser: error: removal of role "kuma" failed: ERROR:  role "kuma" cannot be dropped because some objects depend on it
DETAIL:  owner of sequence public.tbl_id_seq
owner of table public.tbl

テーブルtblをkumaに渡したところ、dropuserができなかった

createuser

管理者ユーザーでなくても一般ユーザーでcreateuserができた

postgres@9dc74f27d78a:/root$ createuser -U kuma watanabe
postgres=# \du
                             List of roles
 Role name |                         Attributes                    
 kuma      | Superuser, Create role, Create DB
 watanabe  | 

作成したユーザー（watanabe）は実行したユーザーの設定が反映されることはない。

コマンド	説明
-P --pwprompt(passwordではない！)	パスワードを設定する
-r --createrole	新しいユーザーをスーパーユーザーとして作成する
-D --no-createdb	新しいユーザの作成を禁止する

createdb

オプション	説明
-E --encoding	データベース内で使用するエンコーディングを指定する
-O --owner	新しいDBの所有者となるユーザを指定する
-l --locale	DBのロケールを指定する
-T --template	TemplateDBを指定する。なにも指定しない場合はTemplate1になる。その場合エンコーディングとロケールは変更不可。エンコーディングとロケールを別のものにしたい場合はtemplate0を指定したうえで変更を行う。

pg_controldata

initdbを実行したデータベースクラスタの管理ユーザで行う必要がある。

postgres@9dc74f27d78a:/root$ pg_controldata
pg_control version number:            1300
Catalog version number:               202307071
Database system identifier:           7346843529858322470
Database cluster state:               in production
pg_control last modified:             Mon 18 Mar 2024 11:41:43 PM UTC
Latest checkpoint location:           0/1A3CFD0
Latest checkpoint's REDO location:    0/1A3CF98
Latest checkpoint's REDO WAL file:    000000010000000000000001
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:827
Latest checkpoint's NextOID:          24616
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        723
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  827

-Dオプションでデータベースクラスタの指定ができる

psql

オプション	説明
-l --list	すべてのDBのリストを表示した後、psqlを終了する
-c --command	指定したコマンドの実行結果を表示した後、psqlを終了する
-s --single-step	各コマンドごとに実行するかキャンセルかの確認を行う(シングルステップモード)
-1 --single-transaction	複数のコマンドを一つのトランザクションで実行する

procedure

postgres=# create procedure sample_proc(int) language plpgsql as $$
begin
insert into tbl(c1) values ($1);
commit;
end;$$;
CREATE PROCEDURE
postgres=# \df
                          List of functions
 Schema |    Name     | Result data type | Argument data types | Type 
--------+-------------+------------------+---------------------+------
 public | sample      |                  | IN integer          | proc
 public | sample_proc |                  | IN integer          | proc
(2 rows)

プロシージャの特徴
・プロシージャ内でcommit.rollbackといったトランザクション制御を行える
・実行にはcallを使用する

プロシージャと関数の違い

内容	プロシージャ	関数
return	なし	あり（プロシージャのように使うならreturn void）
呼び出し方	call proc()	select test(int)

関数について

CREATE OR REPLACE FUNCTION s_func(varchar(10)) RETURNS SETOF INTEGER AS $$
SELECT id FROM sample WHERE staff = $1 ORDER BY id LIMIT 2;
$$ LANGUAGE SQL strict;

・setof オプション
実行結果が複数ある場合は複数件返される
・strictオプション
引数にnullが含まれる場合は処理を実行せずnull返しする
・LanguageはSQL/pgSQL/plpgなどがある

offset

postgres=# select * from member;
 id |   name   
----+----------
  1 | sato
  2 | suzuki
  3 | watanabe
  4 | kato
(4 rows)

offset 1→結果の取得時に一件スキップする

postgres=# select * from member order by id desc limit 3 offset 1;
 id |   name   
----+----------
  3 | watanabe
  2 | suzuki
  1 | sato
(3 rows)

sub-command

cascade

対象のスキーマにオブジェクトが存在する場合cascadeを設定することで関連オブジェクトをまとめて削除することができる。

postgres=# drop schema public;
ERROR:  cannot drop schema public because other objects depend on it
DETAIL:  table public.test depends on schema public
table public.tbl01 depends on schema public
function public.sample(integer) depends on schema public
function public.sample_proc(integer) depends on schema public
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

postgres=# drop schema public cascade;
NOTICE:  drop cascades to 11 other objects
DETAIL:  drop cascades to table public.test
drop cascades to table public.tbl01
…
DROP SCHEMA

distinct

重複を除去する２つの記載方法

１．postgres=# select distinct g_no from mebo;
２．postgres=# select distinct on (g_no) g_no from mebo;

serialと generate always as identityの違い

共通点はどちらも自動採番の連番を指定しなくても割り振ってくれるところ。暗黙的にシーケンスが生成される。
しかし、両者の相違点はinsertでその列を指定したときに差がでる

generated always as identity

postgres=# create table test1(no integer generated always as identity);
CREATE TABLE
postgres=# insert into test1 values(2);
ERROR:  cannot insert a non-DEFAULT value into column "no"
DETAIL:  Column "no" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

型をserialに指定

postgres=# create table test(no serial);
CREATE TABLE
postgres=# insert into test values(2);
INSERT 0 1

自由度という点ではserialではあるが予期せぬ挿入があっては困るということならば「generated always as identity」が良い。
ちなみに「generated by default as identity」と指定すると、明示的に値を指定してもよくなる。要するに「id serial」と変わらない。

domain

オリジナルのタイプ（型）をつくることができる

postgres=# create domain us_pos_code as text
postgres-# check(
postgres(# value ~ '^\d{5}$' or value ~ '^\d{5}$-\d{4}$'
postgres(# );
CREATE DOMAIN

上の例はvalueが5桁かどうかをチェックする型である。
チェック制約でカラムの値を表すときはvalueキーワードを使う。
・DEFAULT句は、ドメインデータ型の列のデフォルト値を指定する

join

inner join

書式が３つある
1.using(key)
2.natural inner join
3.on tbl_a.key=tbl_b.key

1.using(key)

postgres=# select * from member inner join department using(g_no);
 g_no | no |   name   | g_name 
------+----+----------+--------
    2 |  1 | ishikawa | somu
    2 |  3 | kodama   | somu

2.natural inner join

postgres=# select * from member natural inner join department;
 g_no | no |   name   | g_name 
------+----+----------+--------
    2 |  1 | ishikawa | somu
    2 |  3 | kodama   | somu
(2 rows)

naturalは共通keyを勝手にpostgresqlが読んでjoinしてくれる。
using()とnaturalを挙動が同じでkey列が最左列に移動。

3.on tbl_a.key=tbl_b.key

postgres=# select * from member inner join department on member.g_no=department.g_no;
 no |   name   | g_no | g_no | g_name 
----+----------+------+------+--------
  1 | ishikawa |    2 |    2 | somu
  3 | kodama   |    2 |    2 | somu
(2 rows)

keyの直接指定は２列分できる。

型

文字列型

型名	説明
text	上限１GBの可変長文字列
varchar(n)	n省略→text型同様、上限1GB
char(n)	n省略→character(1)と同じ。n桁に満たない場合空白文字でpadding

格納しようとした値の文字数が文字数制限のある型の指定以上の場合はエラーとなる。ただし、制限を超えている文字が半角空白文字のみの場合は、指定文字数まで自動で切り詰められて格納される。

数値型

型名	サイズ	説明
smallint	2 bite	16bit符号付き
integer,int	4 bite	32bit符号付き
bigint	8 bite	64bit符号付き
real	4 bite	浮動小数点　最低6桁の精度
double precison	8 bite	最低15桁の精度
numeric,decimal	可変長	最大1000桁までの精度

postgres=# select '' is null;
 ?column? 
----------
 f
(1 row)

nullに対して、一般的な演算子を用いることはできない

binary型

型名	サイズ	説明
BYTEA	1 or 4biteと実データの長さ	可変長のバイナリ列を格納

OID型

型名	サイズ	説明
OID	4bite	オブジェクトを識別するためのID値を格納。テーブル、インデックスに対して使用。

日付・時刻型

型名	サイズ	説明	記載例
DATE	4bite	時刻なし日付	Jan 01 2017,2017-01-01
TIME(without timezone)	8bite	マイクロ秒まで表示	14:00:00.0000
TIME with timezone	12bite	マイクロ秒まで表示	14:00:00.0000+9
TIMESTAMP(without timezone)	8bite	マイクロ秒まで表示	2017-01-01 14:00:00.000
TIMESTAMP with timezone	8bite		2017-01-01 14:00:00.000+9
interval	16bite	時間間隔を格納できる	interval '1 year','1 year'::interval

論理型

型名	サイズ	説明	真を表す文字	偽を表す文字
boolean bool	1bite	真か偽 or null	't' 'true' 'y' 'yes' 'on' '1' TRUE	'f' 'false' 'n' 'no' 'off' '0' FALSE

json型とjsonb型

	json	jsonb
空白やキーの順序	保持される	保持されない
重複	そのまま保持	最後の値のみ保持
処理の実行	処理を実行するたびにデータの解析	データの格納時に行うため高速
その他		インデックスをサポート、バイナリ形式

jsonbの場合はjsonpath式を使って特定の要素を指定することが可能である。

declareカーソル

オプション１

option	説明
insensitive	デフォルト
scroll	データの順方向・逆方向のどちらにもカーソルを動かせるようにする
no scroll	データの順方向にカーソルを動かせるように指定する。逆方向エラー

オプション２

option	説明
with hold	トランザクションがコミットされた後もカーソルを使用できるようにする
without hold	トランザクション内でのみカーソルを使用できるようにする（デフォルト）

fetch

カーソルの移動と同時にデータを取得するコマンド
・forward/next　順方向に一つ
・backward/prior　逆方向に一つ
・all/forward all　順方向に全部のデータ取得
・backward all 逆方向に全部のデータ取得
・forward
・backward

Sequence

increment by
start
minvalue
maxvalue

create sequence sample_seq cache 5 no cycle;

・no cycle
シーケンスが上限または下限に達したら、エラーがでる
・cache
メモリに格納できるシーケンスの番号の量指定。検索の高速化に使用できる。

postgres=# create sequence sample_seq increment by -5 start 1000 minvalue 100 maxvalue 1000;
CREATE SEQUENCE
postgres=# \d sample_seq
                    Sequence "test.sample_seq"
  Type  | Start | Minimum | Maximum | Increment | Cycles? | Cache 
--------+-------+---------+---------+-----------+---------+-------
 bigint |  1000 |     100 |    1000 |        -5 | no      |     1

inherits

継承がどのように行われる確認をしたい。

postgres=# select * from sample1;
 no |   name
----+----------
  1 | aoki
  2 | yamada
  3 | tanabe
  4 | hasimoto
(4 rows)

postgres=# create table sample2(tel integer) inherits(sample1);
CREATE TABLE
postgres=# \d sample2;
                                   Table "public.sample2"
 Column |         Type          | Collation | Nullable |               Default
--------+-----------------------+-----------+----------+-------------------------------------
 no     | integer               |           | not null | nextval('sample1_no_seq'::regclass)
 name   | character varying(10) |           |          |
 tel    | integer               |           |          |
Inherits: sample1

この状態でsample2に以下のコードを実行

postgres=# insert into sample2 values(10,'koyama',1212);
INSERT 0 1
postgres=# select * from sample1;
 no |   name
----+----------
  1 | aoki
  2 | yamada
  3 | tanabe
  4 | hasimoto
 10 | koyama
(5 rows)

postgres=# select * from sample2;
 no |  name  | tel
----+--------+------
 10 | koyama | 1212
(1 row)

レコードが更新されたのをsample1にも対応されなければ、整合性がとれなくなる。しかし、列が増えなくても、整合性は保たれる。
よって、結果は既存の列に1レコード追加されたような形になる。

materialized view

create materialized view

create materialized view if not exists family_avg_data as select 
a.no,min(b.age) from family_data a,member_data b where 
a.no=b.family_no group by a.no with no data;

・with no data
データが投入されなくなる。投入する場合はREFRESH MATERIALIZED VIEWコマンドを使用。
・if not exist
すでに同じ名前のマテリアライズドビューが作成済みの場合もエラーにならない。

alter materialized view

alter materialized view family_avg_data owner to current_user;

・if exists
存在しない場合もえらーにならない
・current_user/session_user
所有者を予約語で指定できる

drop materialized view

drop materialized view family_avg_data;

・cascade/restrict
cascadeはマテビューに依存するオブジェクトも削除される。
restrictは依存するオブジェクトがあったら削除されない。

refreash materialized view

refreash materialized view [concurrently] family_avg_data;

・concurrently
マテビューの更新中もselect処理をブロックしない。
・with data/with no data
更新後にデータが投入されるかされないか。

create trigger

特定の更新処理は行われたタイミングで指定した関数の呼び出しができる

関数(log_time_write())の定義

create function log_time_write() returns trigger as $$ 
begin
insert into log_memory(time) values(now());
return null;
end;
$$ language plpgsql;

trigger作成

create trigger sample_trg before insert 
or update or delete on sample execute 
procedure log_time_write();

log_memoryテーブル作成

create table log_memory(no serial,time date);

ここまでが準備。念のため確認として、

postgres=# \d sample
                Table "test.sample"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 no     | integer |           |          | 
 name   | text    |           |          | 
Foreign-key constraints:
    "sample_no_fkey" FOREIGN KEY (no) REFERENCES member(no)
Triggers:
    sample_trg BEFORE INSERT OR DELETE OR UPDATE ON sample FOR EACH STATEMENT EXECUTE FUNCTION log_time_write()

Triggerが指定されていることを確認

insertしてみる

insert into sample (name) values('yamana');
INSERT 0 1
insert into sample (name) values('tetuto');
INSERT 0 1
insert into sample (name) values('berako');
INSERT 0 1
select * from log_memory;
 no |    time    
----+------------
  1 | 2024-03-19
  2 | 2024-03-19
  3 | 2024-03-19
(3 rows)

行ごととSQLごとの更新に対して行うものの2通りがある
・FOR [EACH] ROW
対象テーブルの行ごとの更新に対してトリガーを実行します。

・FOR [EACH] STATEMENT(デフォルト)
対象テーブルのSQLごとの更新に対してトリガーを実行します。

drop trigger

drop trigger sample_trg on sample;

日時の取得

select now()-'3 month'::interval;
select now()-interval '3 month'

コツは'3 month'というように期間を''で囲う。そのあとにintervalを前後どちらかに置く。

create table →英数字以外の日本語でもできる

postgres=# create table わたる(id serial);
CREATE TABLE

プリペアド文

SQLの書き換えで行う一連の流れ（関数、ルールの使用→SQLの構文解析→実行計画作成）を一度実行するだけで済む。

prepare sample_prepare(varchar) as select 
member_info.name,address_info.address from
member_info,address_info where member_info.aid=address_info.addres
sid and address_info.address=$1;
PREPARE

execute sample_prepare('tokyo');
   name   | address 
----------+---------
 tanaka   | tokyo
 maruyama | tokyo
(2 rows)

$1に指定した引数が入るようにしている

プリペアド文の主な関数
１．PREPARE　作成
２．EXECUTE　実行
３．DEALLOCATE　削除