インフラ

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=12;
#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=12;
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=12;
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である必要がある。


ダンプしたファイルからリストア

-Fcで出力していたら

pg_restore --host=ホスト名 --user=ユーザー名  < バックアップファイル名.ct;

-Fcを付けていない(=ただのSQL)で出力していたら

psql --host=ホスト名 --user=ユーザー名 -f バックアップファイル名.sql;

マスター&スレーブサーバーによるレプリケーション構成の構築

スレーブサーバー用途のインスタンスの用意

VPSが提供している機能として
マスターサーバーのスナップショットからスレーブサーバーのインスタンスが作れるのなら
設定してあるマスターサーバーからスナップショットをとって
スレーブサーバーのインスタンスを立ち上げましょう。

マスターサーバーとスレーブサーバーのローカルアドレスのIPアドレスを把握して
それを使うサーバーそれぞれの
/etc/hosts
に定義する事で名前でアクセスできるようにしておきましょう。

10.1.1.2 psql-master1
10.1.1.3 psql-slave1

マスターサーバの設定

オリジナル状態のconfigファイルをコピーしてバックアップ

cp /var/lib/pgsql/11/data/postgresql.conf ~/;

以下の項目を編集

archive_command='cp %p /var/lib/pgsql/12/backups/%f'
archive_mode=on
listen_addresses = '*'
max_wal_senders = 10
wal_keep_segments = 10
wal_level = replica

Replication用のユーザを作る

CREATE USER replicauser REPLICATION LOGIN ENCRYPTED PASSWORD 任意のパスワード;
ALTER ROLE postgres NOREPLICATION;

作られたRoleを確認

\du

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

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

host    replication     replicauser        psql-slave1           md5

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

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

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

とデータを空にする

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

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

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

Slaveサーバーを起動

systemctl start postgresql-12;

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

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-12/bin/pg_ctl stop -m immediate

スレーブサーバー

/usr/pgsql-12/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/12/data/postgresql.conf
を編集して

listen_addresses = '*'

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

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

host	all		all		10.10.11.235/32		md5

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

後は

systemctl restart postgresql-12

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

性能のベンチマーク

pgbenchによるベンチマーク

posgresユーザーで

/usr/pgsql-12/bin/pgbench -i testdb

でテストDBを作成して

/usr/pgsql-12/bin/pgbench -c 50 -t 1000 testdb

で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';

実行時間が長いSQLの実行の停止

SELECT pg_cancel_backend(プロセスID);

停止すら出来ないスタックしたプロセスがある場合

SELECT pg_cancel_backend(プロセスID);

これはPostgreSQLのプロセス自体再起動してしまうので注意


PostgreSQLサーバーとの接続の終了

1
\q

データの型

PostgreSQLの型には以下の通りのものがある。

bigintint8signed eight-byte integer
bigserialserial8autoincrementing eight-byte integer
bit [ (n) ]fixed-length bit string
bit varying [ (n) ]varbit [ (n) ]variable-length bit string
booleanboollogical Boolean (true/false)
boxrectangular box on a plane
byteabinary data (“byte array”)
character [ (n) ]char [ (n) ]fixed-length character string
character varying [ (n) ]varchar [ (n) ]variable-length character string
cidrIPv4 or IPv6 network address
circlecircle on a plane
datecalendar date (year, month, day)
double precisionfloat8double precision floating-point number (8 bytes)
inetIPv4 or IPv6 host address
integerint, int4signed four-byte integer
interval [ fields ] [ (p) ]time span
jsontextual JSON data
jsonbbinary JSON data, decomposed
lineinfinite line on a plane
lsegline segment on a plane
macaddrMAC (Media Access Control) address
macaddr8MAC (Media Access Control) address (EUI-64 format)
moneycurrency amount
numeric [ (p, s) ]decimal [ (p, s) ]exact numeric of selectable precision
pathgeometric path on a plane
pg_lsnPostgreSQL Log Sequence Number
pointgeometric point on a plane
polygonclosed geometric path on a plane
realfloat4single precision floating-point number (4 bytes)
smallintint2signed two-byte integer
smallserialserial2autoincrementing two-byte integer
serialserial4autoincrementing four-byte integer
textvariable-length character string
time [ (p) ] [ without time zone ]time of day (no time zone)
time [ (p) ] with time zonetimetztime of day, including time zone
timestamp [ (p) ] [ without time zone ]date and time (no time zone)
timestamp [ (p) ] with time zonetimestamptzdate and time, including time zone
tsquerytext search query
tsvectortext search document
txid_snapshotuser-level transaction ID snapshot
uuiduniversally unique identifier
xmlXML data

SQL

JSON型のSELECT

例えばexampleテーブル内のitemsというJSON項目の中のname項目の値を得たければ

SELECT items->>'name' FROM example;

WHERE句で使う為には、型変換を使って、きちんと比較できるようにする。

SELECT items->>'name' FROM example WHERE (items->>'name')::integer = 1;

運用

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;