PostgreSQLのインストール・設定・使い方
- MySQL(MariaDB)とPostgreSQLのどちらを選ぶべきかまず判断
- そもそも自分でDBを設定・運用する事が妥当なのかの判断
- インストールと設定
- 性能のベンチマーク
- 開発
- 運用
MySQL(MariaDB)とPostgreSQLのどちらを選ぶべきかまず判断
MariaDBとMySQLとPostgreSQLどれを選ぶべきか選択フローチャートを参考に決めて下さい。
そもそも自分でDBを設定・運用する事が妥当なのかの判断
データベースは、業務上ミッションクリティカルな部分であり、またサラリーマン的に働く場合には、その設定・運用・教育の人件費にもコストがかかると考えると、企業人として関わる場合には自分でインストール・設定・運用するよりも、サービス側にDB運用の多くを任せられる、DBAAS(Database As A Service)型のマネージドDBサービスを利用した方が、費用対効果的にも妥当となる事が多い。
ただ、個人の財布でサービスを運用する場合、月数千円でも節約したい、という気持ちになるのも妥当であり、その場合には、ようこそ、データベース運用者の道へ、という事になります。
インストールと設定
この文章はPostgeSQL16以降に対応しています。
Linuxディストリビューター配布のをインストールしたい場合
OSのディストリビューション付属ので安心アップデートをしたいのなら
CentOS7以前
yum install postgresql; yum install postgresql-server;
CentOS8以降
dnf install postgresql; dnf install postgresql-server;
Ubutu系
apt-get install postgresql; apt-get install postgresql-server;
以降、例はdnfで書いていきますが、Linuxディストリビューターに応じて、そこは変えて下さい。
Macでインストールしたい場合
brew install postgresql;
Macはついでにここで全部起動・接続まで書くと
brew services start postgresql
接続
psql postgres
PostgreSQL公式の最新版をインストールしたい場合
PostgreSQL本家の方の更新に任せたいのならばまず
dnf list --installed|grep postgresql
で検索してみて、引っかかるものがあったら
PGVER=10; dnf remove postgresql$PGVER; dnf remove postgresql$PGVER-server;
といった形でアンインストールする。
なお、$PGVERの部分は、自分の環境に合わせて変えて下さい。
加えて、CentOS8の場合は
dnf module disable postgresql; dnf module enable postgresql:$PGVER;
CentOS7以下の場合は
/etc/yum.repos.d/CentOS-Base.repo
の
[base]と[updates]のセクションに
exclude=postgresql*
を追加してCentOSのディストリビューションの方からインストールされないようにして
# Almalinux/CentOS/RHEL's version OSVER=9; #PostgreSQL's version PGVER=16; #CentOS7以前ならyum INSTALL=dnf; yes | $INSTALL install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$OSVER-x86_64/pgdg-redhat-repo-latest.noarch.rpm $INSTALL install -y postgresql$PGVER; $INSTALL install -y postgresql$PGVER-server; /usr/pgsql-$PGVER/bin/postgresql-$PGVER-setup initdb; systemctl enable postgresql-$PGVER; systemctl start postgresql-$PGVER;
※OSVERとPGVERの部分が何になるかは、その時の最新版の状況次第
Dockerでのインストール
Dockerを用意した上で
version: '3.4'
services:
postgres:
image: postgres:latest
ports:
- "5432:5432"
restart: always
volumes:
# This data source path should be changed based on env
- ./_data/docker/psql:/var/lib/postgresql
- ./postgres/docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
- ./backups/postgresql:/backup
をdocker-compose.ymlに書いて
docker-composer up
ユーザーの認証方式をPeerからPasswordに変更する
最近のPostgreSQLのデフォルトの認証方式はPeer方式=UnixのユーザーIDと同じならただpsqlと打つだけでパスワード無しでPostgreSQLにログイン出来る方式。
ただ、これだとリモートからのアクセスや、ソフトによっては問題がある為、パスワードでのログイン形式に変える。
まずは、デフォルトの最上位権限ユーザーのpostgresユーザーにパスワードをセットしておく
sudo su - postgres;
でpostgresユーザーになって
psql;
でログインして
ALTER USER postgres WITH PASSWORD '何かのパスワード';
でパスワードを設定
そしてshellに戻ってrootを持つ権限で
VER=16; sudo vi /var/lib/pgsql/$VER/data/pg_hba.conf
とファイルの編集モードに入って
local all all peer
のpeerという所をscram-sha-256と変更
PGVER=16; sudo systemctl restart postgresql-$PGVER;
でサーバーを再起動する。
最後に
psql --user=postgres --pass
と打って、パスワードが要求された上で、設定したパスワードでログイン出来る事を確認
外部のサーバーからの接続
/var/lib/pgsql/$PGVER/data/postgresql.conf
の中を
#listen_addresses = 'localhost' listen_addresses = '*'
と変更して、localhost以外からも接続出来るようにする。
変更後、反映にはpostgresqlの再起動が必要
その他configの変更
sudo vi /var/lib/pgsql/$PGVER/data/postgresql.conf;
#自分のサーバーのスペックに合わせて。PostgreSQL専用なら25-50%位まで使わせて問題ないのでは shared_buffers = 256MB timezone = 'Asia/Tokyo' log_timezone = 'Asia/Tokyo'
変更後、反映にはpostgresqlの再起動が必要
データベース全体をバックアップ
pg_dumpall -c --host=ホスト名 --user=ユーザー名 > バックアップファイル名.sql
DBのユーザーも含めてまとめて０からまとめて最初の生成をするにはこちらを使う。
データベースを指定してのバックアップ
pg_dump -Fc --host=ホスト名 --user=ユーザー名 データベース名 > バックアップファイル名.ct
-Fc は圧縮された形式のファイルを吐いてくれる。
付けない場合の1/7位のサイズになるので、基本的には付けて使おう。
日常のバックアップにはこちらを使う。
パスワードを聞かれないようにcronでdumpするには、コマンドを実行するユーザーの
~/.pgpass
に
*:*:*:*:パスワード
と書いておく。
なお、.pgpassのファイルパーミッションは600である必要がある。
テーブルを指定してのバックアップ
pg_dump -Fc --host=ホスト名 --user=ユーザー名 --table=テーブル名 データベース名 > バックアップファイル名.ct
ダンプしたファイルからリストア
-Fcで出力していたら
pg_restore --host=ホスト名 --user=ユーザー名 -v -f バックアップファイル名.ct;
-vはより詳しい経過出力をする
-cを付けると一旦テーブルを空にしてからrestoreの処理をする
-Fcを付けていない(=ただのSQL)で出力していたら
psql --host=ホスト名 --user=ユーザー名 -f バックアップファイル名.sql;
なお、ここら辺をgzip圧縮をかけたままやりたいのなら、dumpは
psql --host=ホスト名 --user=ユーザー名 | gzip > バックアップファイル名.sql.gz;
データのリストアは
gzip -cd バックアップファイル名.sql.gz | psql --user=ユーザー名 --host=ホスト名
とすれば良い
マスター&スレーブサーバーによるレプリケーション構成の構築
スレーブサーバー用途のインスタンスの用意
VPSが提供している機能として
マスターサーバーのスナップショットからスレーブサーバーのインスタンスが作れるのなら
設定してあるマスターサーバーからスナップショットをとって
スレーブサーバーのインスタンスを立ち上げましょう。
マスターサーバーとスレーブサーバーのローカルアドレスのIPアドレスを把握して
それを使うサーバーそれぞれの
/etc/hosts
に定義する事で名前でアクセスできるようにしておきましょう。
例
10.1.1.2 psql-master1 10.1.1.3 psql-slave1
マスターサーバの設定
オリジナル状態のconfigファイルをコピーしてバックアップ
PGVER=16; cp /var/lib/pgsql/$PGVER/data/postgresql.conf ~/;
以下の項目を編集
archive_command='cp %p /tmp/%f' archive_mode=on listen_addresses = '*' max_wal_senders = 10 # wal_keep_segments = 10 # postgresql 12 wal_keep_size = 160 # postgresql 13以降 wal_level = replica
Replication用のユーザを作る
CREATE USER replicauser REPLICATION LOGIN ENCRYPTED PASSWORD 任意のパスワード; ALTER ROLE postgres NOREPLICATION;
作られたRoleを確認
\du
PGVER=16;
/var/lib/pgsql/$PGVER/data/pg_hba.conf
を編集して、接続を許可したいホスト・ユーザ条件を指定する
psql-slave1上のreplicauserからのreplicationをmd5認証でのアクセスを認めたいのなら
host replication replicauser psql-slave1 md5
スレーブサーバーでの設定
まずposgresqlサーバーが動いているのなら止める
PGVER=16; systemctl stop posgresql-$PGVER;
PGVER=16; rm -rf /var/lib/pgsql/$PGVER/data/*;
とデータを空にする
マスターからのバックアップを先に作ったreplicauserのアカウントを使ってコマンドで行う
ちなみにこれはrootユーザではなく、postgresユーザで行う必要がある。
PGVER=16; pg_basebackup -h prod-psql2 -D /var/lib/pgsql/$PGVER/data -U replicauser -R -P;
Slaveサーバーを起動
PGVER=16; systemctl start postgresql-$PGVER;
レプリケーションが正常に稼働しているかの確認
psqlでPostgresqlのマスターサーバーにログインして
select usename, application_name, client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn from pg_stat_replication;
と打ってみる
結果例
replicauser | walreceiver | 10.130.227.245 | streaming | 0/1601C9F8 | 0/1601C9F8 | 0/1601C9F8 | 0/1601C9F8
後は実際にマスターサーバーでテーブルを作ってみて、そのテーブルがSlaveに作られている事を確認してみる
テーブルの作成
まずはマスターサーバーで
create table test (id int); \dt
スレーブサーバーにもテーブルが出来ている事を確認
\dt
スレーブサーバーでは削除できない事を確認
DROP TABLE test;
マスターサーバーでテーブルを削除
DROP TABLE test;
マスターとスレーブサーバーの切り替え
マスターサーバーで
PGVER=16; /usr/pgsql-$PGVER/bin/pg_ctl stop -m immediate
スレーブサーバー
PGVER=16; /usr/pgsql-$PGVER/bin/pg_ctl promote
マスターサーバーをスレーブの役割にするには、上でスレーブを追加したように、一旦外してから、データをクリアして、同期して、という方法が一つ簡単な方法としてはある
ファイアーウォールの設定
各VPSがファイアーウォールを提供しているのなら各VPSのファイアーウォールの機能を、そうでなければ firewall-cmdでLinux自体にfirewallを設定する。
Digital Oceanの場合、Privateアドレスでの接続でも、VPS管理画面でのFirewall設定が影響するので注意。
DBのポートはWebサーバーに対してだけあけ、sshログインも自分の管理するサーバー等、特定のIPアドレスからのみログインできるようにするのがセキュリティ上良い。
なお、セキュリティとスペックの分離状
WebサーバーとDBサーバーを置くサーバーは出来るのなら分けた方が良いが
コストの削減でWebサーバーと同一の所に置くのもあり。
ただ、DBのポートをインターネットに公開しないように注意(Webサーバーからローカルアクセスでのみアクセスできるように設定すべき)。
PostgreSQLによるIP制限の制御
PGVER=16;
/var/lib/pgsql/$PGVER/data/postgresql.conf
を編集して
listen_addresses = '*'
の様に記述して、他からのアクセスを許可。
PGVER=16;
/var/lib/pgsql/$PGVER/data/pg_hba.conf
を編集して
host all all 10.10.11.235/32 md5
の様に、アクセスを許可するホストのIPアドレス領域を記述する。
後は
PGVER=16; systemctl restart postgresql-$PGVER;
と打って、PostgreSQLを再起動させて反映
性能のベンチマーク
pgbenchによるベンチマーク
posgresユーザーでログインしてまずtestdbを作る
CREATE DATABASE testdb;
それからtestdbの中身をコマンドで作る
PGVER=16; /usr/pgsql-$PGVER/bin/pgbench -i testdb -U postgres
そしてテストを実行
PGVER=16; /usr/pgsql-$PGVER/bin/pgbench -c 50 -t 1000 testdb -U postgres
で50同時接続&1000回テストでパフォーマンス計測
結果例
tps = 452.560665 (including connections establishing) tps = 452.577579 (excluding connections establishing)
sysbenchによるベンチマーク
sysbenchでのテストを動かすには、Postgresqlの認証をPeer認証からPassword認証に先に変えておく必要がある。
dnf install -y sysbench;
でsysbenchをインストール
ログインしてから
create database sbtest; CREATE USER sbtest PASSWORD 'sbtesT2020#desu'; GRANT ALL ON DATABASE sbtest TO sbtest; ALTER DATABASE sbtest OWNER TO sbtest;
データ用意
sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --table-size=1000000 --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=sbtest --pgsql-password=sbtesT2020#desu --pgsql-db=sbtest --time=60 --db-ps-mode=disable prepare;
テストの実行
sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --table-size=100000 --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=sbtest --pgsql-password=sbtesT2020#desu --pgsql-db=sbtest --time=60 --db-ps-mode=disable --threads=8 run;
以下のような結果が得られる
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Running the test with following options:
Number of threads: 8
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 1592654
write: 454190
other: 227914
total: 2274758
transactions: 113531 (1891.88 per sec.)
queries: 2274758 (37906.64 per sec.)
ignored errors: 230 (3.83 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0082s
total number of events: 113531
Latency (ms):
min: 1.43
avg: 4.23
max: 1005.43
95th percentile: 6.67
sum: 479822.48
Threads fairness:
events (avg/stddev): 14191.3750/168.48
execution time (avg/stddev): 59.9778/0.00
開発
PostgreSQLの基本的なコマンド
一番最初のデータベースヘの接続
一番最初はrootユーザーで
sudo su - postgres; psql;
もしくはpostgresユーザが存在しなければpsqlユーザーをトライ
sudo su - psql; psql;
以後ユーザーはpostgresユーザーを使う前提で進める。
データベースの作成
例えばtestというデータベースを作るのならpsqlでログインしてから
CREATE DATABASE test;
で作れるが、psqlコマンドでログインしなくてもpostgresのユーザー使用状態で
createdb test;
でshellコマンドからDBを作る事も出来る。
データベースのリスト
\list
省略版として
1 | \l |
でも同じ結果が得られる
使用データベースの切り替え
\connect データベース名
省略版として
\c データベース名
でも同じ結果が得られる
スキーマの作成
例えばtestというスキーマを作るのなら
CREATE SCHEMA test;
現在のスキーマの確認
select current_schema();
スキーマの一覧確認
\dn
スキーマを変更する
SET search_path = $SCHEMA;
テーブルの作成
一例
CREATE TABLE people (
id SERIAL NOT NULL,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (id)
);
テーブルの構造の確認
shellで
pg_dump $DBNAME -U $USERNAME -s -t $TABLENAME;
テーブルの削除
DROP TABLE people;
データベースのテーブルのリスト
\dt
ユーザーの作成
CREATE USER xxx PASSWORD 'yyy';
ユーザーの削除
DROP USER ユーザー名
ユーザーに指定データベースに対する権限付与
GRANT ALL ON DATABASE データベース名 TO ユーザー名;
権限を付与したユーザーでのログイン
psql -d データベース名 -h ホスト名又はホストのIP -U ユーザーID;
ユーザーに指定スキーマの全テーブルに対する権限付与
スキーマ例はpublicスキーマに所属する全テーブルに対する権限の付与
GRANT ALL ON ALL TABLES IN SCHEMA public To exampleuser;
サーバーで現在実行されているSQLの確認
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
もしくは
SELECT state,query_start,query FROM pg_stat_activity where state = 'active' and query not like '%pg_stat_activity%';
実行時間が長いSQLの実行の停止
SELECT pg_cancel_backend(プロセスID);
停止すら出来ないスタックしたプロセスがある場合
SELECT pg_cancel_backend(プロセスID);
これはPostgreSQLのプロセス自体再起動してしまうので注意
PostgreSQLサーバーとの接続の終了
1 | \q |
データの型
PostgreSQLの型には以下の通りのものがある。
| 型名 | エイリアス | 説明(英語) |
| bigint | int8 | signed eight-byte integer |
| bigserial | serial8 | autoincrementing eight-byte integer |
| bit [ (n) ] | fixed-length bit string | |
| bit varying [ (n) ] | varbit [ (n) ] | variable-length bit string |
| boolean | bool | logical Boolean (true/false) |
| box | rectangular box on a plane | |
| bytea | binary data (“byte array”) | |
| character [ (n) ] | char [ (n) ] | fixed-length character string |
| character varying [ (n) ] | varchar [ (n) ] | variable-length character string |
| cidr | IPv4 or IPv6 network address | |
| circle | circle on a plane | |
| date | calendar date (year, month, day) | |
| double precision | float8 | double precision floating-point number (8 bytes) |
| inet | IPv4 or IPv6 host address | |
| integer | int, int4 | signed four-byte integer |
| interval [ fields ] [ (p) ] | time span | |
| json | textual JSON data | |
| jsonb | binary JSON data, decomposed | |
| line | infinite line on a plane | |
| lseg | line segment on a plane | |
| macaddr | MAC (Media Access Control) address | |
| macaddr8 | MAC (Media Access Control) address (EUI-64 format) | |
| money | currency amount | |
| numeric [ (p, s) ] | decimal [ (p, s) ] | exact numeric of selectable precision |
| path | geometric path on a plane | |
| pg_lsn | PostgreSQL Log Sequence Number | |
| point | geometric point on a plane | |
| polygon | closed geometric path on a plane | |
| real | float4 | single precision floating-point number (4 bytes) |
| smallint | int2 | signed two-byte integer |
| smallserial | serial2 | autoincrementing two-byte integer |
| serial | serial4 | autoincrementing four-byte integer |
| text | variable-length character string | |
| time [ (p) ] [ without time zone ] | time of day (no time zone) | |
| time [ (p) ] with time zone | timetz | time of day, including time zone |
| timestamp [ (p) ] [ without time zone ] | date and time (no time zone) | |
| timestamp [ (p) ] with time zone | timestamptz | date and time, including time zone |
| tsquery | text search query | |
| tsvector | text search document | |
| txid_snapshot | user-level transaction ID snapshot | |
| uuid | universally unique identifier | |
| xml | XML data |
SQL
JSONB型のSELECT
例えばexampleテーブル内のitemsというJSON項目の中のname項目の値を得たければ
SELECT items->>'name' FROM example;
WHERE句で使う為には、型変換を使って、きちんと比較できるようにする。
SELECT items->>'name' FROM example WHERE (items->>'name')::integer = 1;
JSONB型の値の更新
例えばexampleテーブル内のitemsというJSON項目の中のname項目の値だけを3に更新したければ
UPDATE
example
SET
items = items::jsonb || json_build_object(
'name', 3
)::jsonb;
ちょっと複雑に見えるかもしれませんが、こういう文法だと諦めて覚えましょう。
運用
Vacuumによる定期的なディスクの無駄の除去
PostgreSQLはデータの更新・削除で無駄な利用領域が発生するが
vacuum;
でその無駄を省ける。
どれだけ現在無駄が発生しているかは
SELECT relname, n_live_tup, n_dead_tup, CASE n_dead_tup WHEN 0 THEN 0 ELSE round(n_dead_tup*100/(n_live_tup+n_dead_tup) ,2) END AS ratio FROM pg_stat_user_tables ;
で確認できる
ディスク容量の確認
du -sh /var/lib/pgsql/;
で確認できる
各データベースがどれだけ使っているのか知りたければ
select datid,datname from pg_stat_database;
と打ち、データベースに対応して出てきたdatidとして出てきた番号を含むディレクトリ
/var/lib/pgsql/$PGVER/data/base/$datid
以下の容量を
du -sh /var/lib/pgsql/$PGVER/data/base/$datid;
といった形で確認すれば良い
速度チューニング
PostgreSQLが使えるメモリ量は、速度の大きなチューニングポイントの一つ。
サーバーが搭載しているメモリ量次第だが
/var/lib/pgsql/16/data/postgresql.conf
の
shared_buffersの値を上げれば、それだけPostgreSQLサーバーが多くメモリを使える。
値を変更したら
systemctl restart postgresql-16
といったコマンドでPostgreSQLを再起動して、パラメーター変更を反映する必要がある。
ShellからSQLを実行して結果をCSV(タブ区切り)に出力
SQL="..."; DBNAME="..."; DBHOST="..."; CSVFILE="..."; psql $DBNAME -h $DBHOST -c $SQL -A -F $'\t' > $CSVFILE;
PosgreSQLサーバーのバージョンアップ&スペックアップ
代替サーバーにDumpデータを入れ込んで、バージョンアップ&スペックアップをする場合
Dumpデータを取り込んで、それをそのままマスターに切り上げるというのならば、
取り込んだ後にReplicationの対象として追加し、
後でそれをマスターに切り替える、といった方法が考えられるかと思います。
また、PostgreSQLのバージョンアップとも組み合わせると考えると、本番サーバーで実行する前に、ステージング等開発環境で一旦テストしてからの方が良いでしょう。
ステージング環境での作業
まずは旧サーバーでベンチマークを取る
このページ内のベンチマークの方法を参照
３回は実行して、中央値を取れる様にしておきましょう。
取得例
pgbench
[PostgreSQL 12]
tps = 283.523293 (including connections establishing)
tps = 300.369871 (including connections establishing)
tps = 306.893579 (including connections establishing)
[PostgreSQL 13]
tps = 270.828900 (including connections establishing)
tps = 254.806414 (including connections establishing)
tps = 282.964234 (including connections establishing)
sysbench
queries: 214298 (3569.99 per sec.)
ignored errors: 31 (0.52 per sec.)
queries: 222664 (3710.33 per sec.)
ignored errors: 38 (0.63 per sec.)
queries: 199176 (3316.97 per sec.)
ignored errors: 22 (0.37 per sec.)
現在のデータベースのデータをdump
それから現在のPostgreSQLサーバを停止
それから必要ならば新しい版のPostgreSQLをインストール
それから新しい版のPostgreSQLを設定
それからこのページ内のベンチマークの方法を参照してベンチマークを取得。バージョンアップをしてパフォーマンス的に問題ない事を確認する。
そしてそれから新しい版のPostgreSQLにdumpしたデータをインポート
挙動を確認して問題ない事を確認する。
本番での移行作業
本番で新しいサーバーをインスタンスとして立ち上げて設定
マスターとスレーブを作って、直近にdumpしたデータをインポート
挙動に問題ない事を確認したら、使うサーバーを切り替える。
書き込み含めて問題ないことを確認する。
WALファイルの削除によるディスク容量確保
例えば
/tmp/wal/
以下にWALファイルを保持している場合、そのディレクトリ以下に最新の20個のファイル以外は10分毎に削除する、といった設定にしたい場合には、そのwalファイルを削除する権限を持ったユーザーで、crontab -eで以下の様な設定をする。
[/code]
*/10 * * * * cd /tmp/wal;ls -1tr /tmp/wal | head -n -20 | xargs -d ‘\n’ rm -f —
[/code]