インフラ

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;

以降、例はyumで書いていきますが、Linuxディストリビューターに応じて、そこは変えて下さい。


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

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

dnf list|grep postgresql;

で検索してみて、引っかかるものがあったら

PGVER=10;
yum remove postgresql$PGVER;
yum remove postgresql$PGVER-server;

といった形でアンインストールする。

なお、$PGVERの部分は、自分の環境に合わせて変えて下さい。

加えて、CentOS8の場合は

dnf module disable postgresql;

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;

$INSTALL install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$OSVER-x86_64/pgdg-redhat-repo-latest.noarch.rpm

$INSTALL install postgresql$PGVER;
$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

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)
);

テーブルの削除

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

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

pg_dumpall -b  -h ホスト名 -p ポート データベース名 > バックアップファイル名.sql

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

pg_dump -h ホスト名 -p ポート データベース名 > バックアップファイル名.sql

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

pg_restore < バックアップファイル名.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/11/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/11/data/pg_hba.conf
を編集して、接続を許可したいホスト・ユーザ条件を指定する

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

host    replication     replicauser        psql-slave1           md5

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

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

systemctl stop posgresql-11

postgresユーザーになる

sudo su - postgres

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

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

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

Slaveサーバーを起動

systemctl start postgresql-11

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

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 test (testid int);
\dt

スレーブサーバーにもテーブルが出来ている事を確認

\dt

スレーブサーバーでは削除できない事を確認

DROP TABLE test;

マスターサーバーでテーブルを削除

DROP TABLE test;

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

マスターサーバーで

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

スレーブサーバー

/usr/pgsql-11/bin/pg_ctl promote

性能のベンチマーク

posgresユーザーで

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

でテストDBを作成して

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

で50同時接続&1000回テストでパフォーマンス計測

結果例

tps = 452.560665 (including connections establishing)
tps = 452.577579 (excluding connections establishing)

ファイアーウォールの設定

各VPSがファイアーウォールを提供しているのなら各VPSのファイアーウォールの機能を、そうでなければ firewall-cmdでLinux自体にfirewallを設定する。

Digital Oceanの場合、Privateアドレスでの接続でも、VPS管理画面でのFirewall設定が影響するので注意。

DBのポートはWebサーバーに対してだけあけ、sshログインも自分の管理するサーバー等、特定のIPアドレスからのみログインできるようにするのがセキュリティ上良い。

なお、セキュリティとスペックの分離状
WebサーバーとDBサーバーを置くサーバーは出来るのなら分けた方が良いが
コストの削減でWebサーバーと同一の所に置くのもあり。

ただ、DBのポートをインターネットに公開しないように注意(Webサーバーからローカルアクセスでのみアクセスできるように設定すべき)。