VPS

PostgreSQLのインストール・設定・使い方

目次

MySQL(MariaDB)とPostgreSQLのどちらを選ぶべきかまず判断

MariaDBとMySQLとPostgreSQLどれを選ぶべきか選択フローチャートを参考に決めて下さい。

そもそも自分でDBを設定・運用する事が妥当なのかの判断

データベースは、業務上ミッションクリティカルな部分であり、またサラリーマン的に働く場合には、その設定・運用・教育の人件費にもコストがかかると考えると、企業人として関わる場合には自分でインストール・設定・運用するよりも、サービス側にDB運用の多くを任せられる、DBAAS(Database As A Service)型のマネージドDBサービスを利用した方が、費用対効果的にも妥当となる事が多い。

ただ、個人の財布でサービスを運用する場合、月数千円でも節約したい、という気持ちになるのも妥当であり、その場合には、ようこそ、データベース運用者の道へ、という事になります。

インストールと設定

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ディストリビューターに応じて、そこは変えて下さい。


PostgreSQL公式の最新版をインストールしたい場合

PostgreSQL本家の方の更新に任せたいのならばまず

dnf list|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のディストリビューションの方からインストールされないようにして

#CentOS's version
OSVER=8;
#PostgreSQL's version
PGVER=13;
#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

yes | $INSTALL install postgresql$PGVER;
yes | $INSTALL install 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=13;
sudo vi /var/lib/pgsql/$VER/data/pg_hba.conf 

とファイルの編集モードに入って

local   all             all                                     peer

のpeerという所をmd5と変更

host    all             all             127.0.0.1/32            ident

という所を

host    all             all             127.0.0.1/32            md5

と変更する

PGVER=13;
systemctl restart postgresql-$PGVER;

でサーバーを再起動する。

最後に

psql --user=postgres --pass

と打って、パスワードが要求された上で、設定したパスワードでログイン出来る事を確認


外部のサーバーからの接続

/var/lib/pgsql/$PGVER/data/postgresql.conf
の中を

#listen_addresses = 'localhost'
listen_addresses = '*'

と変更して、localhost以外からも接続出来るようにする。

変更後、反映にはpostgresqlの再起動が必要


その他configの変更

#自分のサーバーのスペックに合わせて。PostgreSQL専用なら25-50%位まで使わせて問題ないのでは
shared_buffers = 256MB

timezone = 'Asia/Tokyo'

log_timezone = 'Asia/Tokyo'

変更後、反映にはpostgresqlの再起動が必要


データベース全体をバックアップ

pg_dumpall -c --host=ホスト名 --user=ユーザー名 > バックアップファイル名.sql

DBのユーザーも含めてまとめて0からまとめて最初の生成をするにはこちらを使う。


データベースを指定してのバックアップ

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ファイルをコピーしてバックアップ

cp /var/lib/pgsql/13/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

/var/lib/pgsql/13/data/pg_hba.conf
を編集して、接続を許可したいホスト・ユーザ条件を指定する

psql-slave1上のreplicauserからのreplicationをmd5認証でのアクセスを認めたいのなら

host    replication     replicauser        psql-slave1           md5

スレーブサーバーでの設定

まずposgresqlサーバーが動いているのなら止める

systemctl stop posgresql-13
rm -rf /var/lib/pgsql/13/data/*

とデータを空にする

マスターからのバックアップを先に作ったreplicauserのアカウントを使ってコマンドで行う

pg_basebackup -h psql-master1 -D /var/lib/pgsql/13/data -U replicauser --write-recovery-conf -P

正常に行われれば/var/lib/pgsql/13/data/recovery.confにその接続情報は保存されて書き出される

Slaveサーバーを起動

systemctl start postgresql-13;

レプリケーションが正常に稼働しているかの確認

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;

マスターとスレーブサーバーの切り替え

マスターサーバーで

/usr/pgsql-13/bin/pg_ctl stop -m immediate

スレーブサーバー

/usr/pgsql-13/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制限の制御

/var/lib/pgsql/13/data/postgresql.conf
を編集して

listen_addresses = '*'

の様に記述して、他からのアクセスを許可。

/var/lib/pgsql/13/data/pg_hba.conf
を編集して

host	all		all		10.10.11.235/32		md5

の様に、アクセスを許可するホストのIPアドレス領域を記述する。

後は

systemctl restart postgresql-13

と打って、PostgreSQLを再起動させて反映

性能のベンチマーク

pgbenchによるベンチマーク

posgresユーザーでログインしてまずtestdbを作る

CREATE DATABASE testdb;

それからtestdbの中身をコマンドで作る

/usr/pgsql-13/bin/pgbench -i testdb -U postgres

そしてテストを実行

/usr/pgsql-13/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 'sbtest2020desu';
GRANT ALL ON DATABASE sbtest 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=sbtest2020desu --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=sbtest2020desu --pgsql-db=sbtest --time=60 --db-ps-mode=disable --threads=8 run;

以下のような結果が得られる

SQL statistics:
    queries performed:
        read:                            223552
        write:                           63776
        other:                           31956
        total:                           319284
    transactions:                        15930  (265.29 per sec.)
    queries:                             319284 (5317.13 per sec.)
    ignored errors:                      38     (0.63 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0455s
    total number of events:              15930

Latency (ms):
         min:                                    4.21
         avg:                                   30.14
         max:                                   92.93
         95th percentile:                       38.94
         sum:                               480138.67

Threads fairness:
    events (avg/stddev):           1991.2500/4.47
    execution time (avg/stddev):   60.0173/0.01

開発

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/12/data/postgresql.conf

shared_buffersの値を上げれば、それだけPostgreSQLサーバーが多くメモリを使える。
値を変更したら
systemctl restart postgresql-12
といったコマンドでPostgreSQLを再起動して、パラメーター変更を反映する必要がある。


ShellからSQLを実行して結果をCSV(タブ区切り)に出力

SQL="...";
DBNAME="...";
DBHOST="...";
CSVFILE="...";
psql $DBNAME -h $DBHOST -c $SQL -A -F $'\t' > $CSVFILE;

PosgreSQLサーバーのバージョンアップ&スペックアップ

代替サーバーにDumpデータを入れ込んで、バージョンアップ&スペックアップをする場合

Dumpデータを取り込んで、それをそのままマスターに切り上げるというのならば、
取り込んだ後にReplicationの対象として追加し、
後でそれをマスターに切り替える、といった方法が考えられるかと思います。

また、PostgreSQLのバージョンアップとも組み合わせると考えると、本番サーバーで実行する前に、ステージング等開発環境で一旦テストしてからの方が良いでしょう。

ステージング環境での作業
まずは旧サーバーでベンチマークを取る

このページ内のベンチマークの方法を参照
3回は実行して、中央値を取れる様にしておきましょう。

取得例
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]