MySQLしか知らないDBAがPostgreSQLをインストールしてみる
今回はPostgreSQLをインストールして触ってみようと思います。 タイトルの通り僕は仕事でMySQLしか触ってこなかったのでPostgreSQLを触るのは初めてです。 まずは、深いところまで踏み込まずMySQLとのノリの違いをなんとなく掴むのが目的です。
PostgreSQLはMySQLより高機能でOracleに近いというイメージがあります。 個人的にはMySQLが一つのことをシンプルに行う、一方でPostgreSQLがリッチな機能があり多目的な用途で利用できるというイメージです。 ライセンスなどの理由でOracleから違うDBMSに移行する際は、PostgreSQLがその対象になるケースも多いようです。
例えば結合一つとっても、MySQLはちょっと前までNLJ(Nested Loop Join)しかサポートしませんでした。(MySQL 8.0.18でようやくハッシュ結合がサポートされるようになりました) これには背景があって、もともとMySQLは組み込み系のシステムで使われていたようです。そのため複雑な処理を実装しなかったわけですね。
一方でPostgreSQLはNLJはもちろん、ハッシュ結合、ソートマージ結合をサポートしているようです。 といっても、最近のMySQLはどんどんとできることが増えてきたので最終的には機能の面で両者に違いは無くなってくるのではないでしょうか。
さて、前置きが長くなってしまいましたが、さっそくPostgreSQLをインストールしてみましょう。 環境はVM上のUbuntu 20.04 LTSです。 最初なのでソースコンパイルはちょっとハードルが高そうなので、まずはaptリポジトリを利用してのインストールを試みます。
今回使うUbuntuのバージョンではデフォルトでPostgreSQLの最新バージョンをインストールできるようです。
sudo apt update sudo apt upgrade sudo apt install postgresql-12
下記の通りインストールは簡単にできました。
koreshiki@koreshiki-ubuntu:~$ psql -V psql (PostgreSQL) 12.2 (Ubuntu 12.2-4)
PostgreSQLをインストールした時点でpostgresqlというOSユーザが作成されます。
koreshiki@koreshiki-ubuntu:~$ grep postgres /etc/passwd
postgres:x:127:133:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash
デフォルトではパスワードの設定は行われないため、rootユーザでsuするしかないようです。 ここではpasswdコマンドでpostgresユーザのパスワードを設定します。
こちらの記事を参考にさせていただきました。
koreshiki@koreshiki-ubuntu:~$ sudo passwd postgres [sudo] koreshiki のパスワード: 新しいパスワード: 新しいパスワードを再入力してください: passwd: パスワードは正しく更新されました
これでpostgresユーザにスイッチできるようになります。
koreshiki@koreshiki-ubuntu:~$ su - postgres パスワード: postgres@koreshiki-ubuntu:~$
ここからすでにMySQLと結構ノリが違いますね。 MySQLはMySQLサーバにログインするときにOSユーザを切り替える必要はありません。 インストールした時点でMySQLサーバのrootユーザが作成されパスワードはエラーログに出力されます。(エラーログの設定をしていない場合は標準出力に出力されます)
データベースにログインするためにはpostgresユーザの状態でpsqlコマンドを実行します。
postgres@koreshiki-ubuntu:~$ psql psql (12.2 (Ubuntu 12.2-4)) Type "help" for help. postgres=#
この状態がmysqlクライアントでログインしている状態に該当するのでしょうか。
データベースの方のpostgresユーザのパスワードも変えておきます。
postgres=# alter role postgres with password 'post'; ALTER ROLE
ここまでできたらPostgreSQLを再起動しておきます。(ついでに自動起動も有効にしておきます)
koreshiki@koreshiki-ubuntu:~$ sudo systemctl restart postgresql koreshiki@koreshiki-ubuntu:~$ sudo systemctl enable postgresql Synchronizing state of postgresql.service with SysV service script with /lib/systemd/systemd-sysv-install. Executing: /lib/systemd/systemd-sysv-install enable postgresql
まずはプロセスがどのように見えるか確認してみます。
koreshiki@koreshiki-ubuntu:~$ ps -ef | grep postgre[s] | grep 12 postgres 41147 1 0 00:04 ? 00:00:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf postgres 41149 41147 0 00:04 ? 00:00:00 postgres: 12/main: checkpointer postgres 41150 41147 0 00:04 ? 00:00:00 postgres: 12/main: background writer postgres 41151 41147 0 00:04 ? 00:00:00 postgres: 12/main: walwriter postgres 41152 41147 0 00:04 ? 00:00:00 postgres: 12/main: autovacuum launcher postgres 41153 41147 0 00:04 ? 00:00:00 postgres: 12/main: stats collector postgres 41154 41147 0 00:04 ? 00:00:00 postgres: 12/main: logical replication launcher root 41261 40858 0 00:05 pts/1 00:00:00 su - postgres postgres 41262 41261 0 00:05 pts/1 00:00:00 -bash postgres 41491 41262 0 00:45 pts/1 00:00:00 /usr/lib/postgresql/12/bin/psql postgres 41505 41147 0 00:46 ? 00:00:00 postgres: 12/main: postgres testdb [local] idle postgres 41600 41597 0 00:48 pts/2 00:00:00 /usr/lib/postgresql/12/bin/psql postgres 41729 41147 0 01:15 ? 00:00:00 postgres: 12/main: postgres testdb [local] idle
MySQLはマルチスレッドのシングルプロセスでした。 一方、PostgreSQLは(Oracleも)1接続ごとに1つのプロセスが生成されます。
では、基本的な操作をしてみます。
postgres=# CREATE DATABASE testdb; CREATE DATABASE postgres=# \c testdb; You are now connected to database "testdb" as user "postgres". testdb=# CREATE TABLE t1(a int primary key, b varchar(20)); CREATE TABLE testdb=# INSERT INTO t1 (a, b) VALUES (1, 'a'), (2, 'b'), (3, 'c'); INSERT 0 3 testdb=# SELECT * FROM t1; 1 | a 2 | b 3 | c
MySQLでいうUSE dbは\c DBとなります。
基本的なDDL・DMLコマンドは同じですね。
各リザルトの表示はMySQLと多少異なりますが、些細な問題でしょう。
続いてデータベース・テーブルの一覧表示など管理者がよく使うであろうコマンドを見てみます。
testdb=# show databases; ERROR: unrecognized configuration parameter "databases"
当然ですが、MySQLで使えた方法は使えないようです。
ただ、エラーメッセージ的にshowコマンド自体は存在して設定パラメータを確認できるコマンドのようですね。(MySQLでいうSHOW VARIABLESみたいなものでしょうか)
testdb=# \l postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres testdb | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | testdb=# \d public | t1 | table | postgres testdb=# \d t1 a | integer | | not null | b | character varying(20) | | |
データベース一覧の確認は\l、テーブル一覧の確認は\d、テーブル定義の確認(MySQLでいうDESC)は\d tableで行います。
ところで、このプロンプト非常にわかりづらいですね。 最低限、接続しているユーザくらいは分かったほうが運用ミスなどを減らせるはずです。
もちろんmysqlクライアントと同じく、プロンプトは変更できるようです。
とりあえず自分が普段よく使う形に近いものにしました。
postgres=# \set PROMPT1 '%n@%m:[%/]%# ' postgres@[local]:[testdb]#
PROMPT1という名前からもわかるようにPROMPT2も3も存在するようですが、今回はとりあえず触れません。
個人的に%#というのが便利でいいですね。
これはスーパーユーザであれば#, その他のユーザであれば>を表示してくれるものです。(mysqlにも欲しい！MySQL Shellなら頑張ればできるかな？)
毎回プロンプトの設定を行うのは面倒なので、設定ファイルとかあれば便利です。
PostgreSQLの場合~/.psqlrcがそれに該当します。
postgres@koreshiki-ubuntu:~$ cat ~/.psqlrc \set PROMPT1 '%n@%m:[%/]%# ' postgres@koreshiki-ubuntu:~$ psql psql (12.2 (Ubuntu 12.2-4)) Type "help" for help. postgres@[local]:[postgres]#
もう少しデータ操作をしてみます。
PK制約によるエラー
postgres@[local]:[testdb]# INSERT INTO t1 VALUES (4, 'd'); INSERT 0 1 postgres@[local]:[testdb]# INSERT INTO t1 VALUES (4, 'd'); ERROR: duplicate key value violates unique constraint "t1_pkey" DETAIL: Key (a)=(4) already exists.
存在しない行の削除(MySQLと同じくエラーにはならない)
postgres@[local]:[testdb]# SELECT * FROM t1; a | b ---+--- 1 | a 2 | b 3 | c 4 | d (4 rows) postgres@[local]:[testdb]# DELETE FROM t1 WHERE a = 100; DELETE 0
カラム数が合わないINSERT
postgres@[local]:[testdb]# INSERT INTO t1 VALUES (100, 'zzz', 1000); ERROR: INSERT has more expressions than target columns LINE 1: INSERT INTO t1 VALUES (100, 'zzz', 1000); ^ postgres@[local]:[testdb]# INSERT INTO t1 VALUES (100); INSERT 0 1 postgres@[local]:[testdb]# SELECT * FROM t1; a | b -----+--- 1 | a 2 | b 3 | c 4 | d 100 | (5 rows)
カラム数がテーブルのカラム数より多い場合エラーとなるが、カラム数未満だとなんとエラーになりません。
これはちょっと気を付けないと怖いですね。
(なるべくNOT NULL制約を設けるのが安全でしょうか)
ちょっと面白い事象
postgres@[local]:[testdb]# SELECT * FROM t1; a | b -----+--- 1 | a 2 | b 3 | c 4 | d 100 | (5 rows) postgres@[local]:[testdb]# UPDATE t1 SET b = 'hoge' WHERE a = 2; UPDATE 1 postgres@[local]:[testdb]# SELECT * FROM t1; a | b -----+------ 1 | a 3 | c 4 | d 100 | 2 | hoge (5 rows)
これはちょっとMySQLしか知らないと妙に思えます。 (UPDATEした後もSELECTすれば1, 2, 3, ... と表示されることを期待することが多いでしょう)
※なお、原則としてORDER BYをつけていないSELECTは一切の順番を保証しないので、この動作は問題ありません。
なぜこのような挙動になるかというと、PostgreSQLは追記型のアーキテクチャを採用しているからです。 (こちらの記事を参考にさせていただいています)
つまり、UPDATEした際にはa=2, b='b'となる行を参照不可にして、a=2, b='hoge'という新しい行を追加します。
参照不可となった行はVACUUMと呼ばれるガベージコレクションのような操作によって再利用されます。
追記型を参照することによってMVCCの実装がシンプルになるそうです。
このあたりのアーキテクチャの概要は今後調べていこうと思います。
クエリ実行計画
postgres@[local]:[testdb]# EXPLAIN SELECT * FROM t1 WHERE a = 1; QUERY PLAN ------------------------------------------------------------------- Index Scan using t1_pkey on t1 (cost=0.15..8.17 rows=1 width=62) Index Cond: (a = 1) (2 rows) postgres@[local]:[testdb]# EXPLAIN SELECT * FROM t1 WHERE b = 'a'; QUERY PLAN ---------------------------------------------------- Seq Scan on t1 (cost=0.00..21.25 rows=4 width=62) Filter: ((b)::text = 'a'::text) (2 rows)
変数の確認・設定(ロックタイムアウトを1秒に設定)
postgres@[local]:[testdb]# SHOW lock_timeout; lock_timeout -------------- 0 (1 row) postgres@[local]:[testdb]# SET lock_timeout = 1000; SET postgres@[local]:[testdb]# SHOW lock_timeout; lock_timeout -------------- 1s (1 row)
MySQLとそこまで変わりません。
T1# BEGIN; BEGIN T1# SELECT * FROM t1; a | b -----+------ 1 | a 3 | c 4 | d 100 | 2 | hoge (5 rows) -- 違うセッションから T2# BEGIN; BEGIN T2# SELECT * FROM t1; 1 | a 3 | c 4 | d 100 | 2 | hoge -- 元のセッションに戻る T1# UPDATE t1 SET b = 'piyo' WHERE a = 2; UPDATE 1 -- 再度違うセッションから、ロックタイムアウトによるエラー T2# UPDATE t1 SET b = 'hogehoge' WHERE a = 2; ERROR: canceling statement due to lock timeout CONTEXT: while updating tuple (0,8) in relation "t1" T2# SELECT * FROM t1; ERROR: current transaction is aborted, commands ignored until end of transaction block
ロックが解放されるまで同様のエラーが出るようです。
トランザクション分離レベル
T1# BEGIN; BEGIN T1# SELECT * FROM t1; a | b -----+------ 1 | a 3 | c 4 | d 100 | 2 | hoge (5 rows) -- 違うセッションから T2# BEGIN; BEGIN T2# SELECT * FROM t1; 1 | a 3 | c 4 | d 100 | 2 | hoge -- 元のセッションで更新してCOMMIT T1# UPDATE t1 SET b = 'piyo' WHERE a = 2; UPDATE 1 T1# COMMIT; COMMIT -- 再度違うセッションから T2# SELECT * FROM t1; 1 | a 3 | c 4 | d 100 | 2 | piyo
ここからもわかるようにPostgreSQLのデフォルトトランザクション分離レベルはREAD COMMITTEDです。(Oracleも同様です)
COMMITした内容は別のトランザクションから参照できてしまいます。
さて、ほんの一部ですがPostgreSQLをインストールして動かしてみました。 次回はもう少し実践で使いそうなコマンドやツールなど調べてみます。 とりえあえず、今回はこのあたりで。
