各VPSの設定

PostgreSQLのインストール・設定・使い方(Cent OS系の場合)

Contents

MySQL(MariaDB)とPostgreSQLのどちらを選ぶかの決定要素

まず、自分が使うフレームワークの正式サポート状況を確認しましょう。

WordPressもそのDBを使いたいのなら、MySQL(MariaDB)が前提で作られているので、MySQL/MariaDBを選びましょう。

Djangoでアプリを作りたい場合には、PostgreSQLが前提で作られているので、PostgreSQLを選びましょう。
また、JSON型を使って、その項目内で検索をしたい場合には、どうしてもNative Indexがまだ張れないMySQL(MariaDB)は速度が出ないので、PostgreSQLを選びましょう。
また、地図系(GIS)のアプリを作る場合にも、PostgreSQLの方が強いです。

基本的に出来る事はPostgreSQLの方が多いので、PostgreSQLとMySQL(MariaDB)の選択において、得手不得手もアプリ的にも必須条件がないのなら、PostgreSQLの方を採用して、後で問題になる事はないでしょう。

なお、運用ツール的には、Workbench等、MySQL(MariaDB)の方がより良いものが提供されている傾向にあります。


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

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

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


インストールと設定

OSのディストリビューション付属ので安心アップデートをしたいのなら

yum install postgresql;
yum install postgresql-server

PostgreSQL本家の方の更新に任せたいのならば
/etc/yum.repos.d/CentOS-Base.repo

[base]

[updates]
のセクションに

exclude=postgresql*

を追加してCentOSのディストリビューションの方からインストールされないようにして

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm;
yum install postgresql11;
yum install postgresql11-server;

/usr/pgsql-11/bin/postgresql-11-setup initdb;
systemctl enable postgresql-11;
systemctl start postgresql-11;

※11の部分が何になるかは、その時の最新版の状況次第


PostgreSQLの基本的なコマンド

一番最初のデータベースヘの接続

一番最初はrootユーザーで

sudo su - psql;
psql;

データベースの作成

例えばtestというデータベースを作るのなら

CREATE DATABASE test;

データベースのリスト

\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 SCHEMA test TO xxx;

権限を付与したユーザーでのログイン

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サーバーからローカルアクセスでのみアクセスできるように設定すべき)。