PostgreSQL入門(第1回):Linuxへのインストールと設定方法を徹底解説

本記事では、PostgreSQLの概要を紹介し、Linux環境におけるバージョン9.3のインストールと設定の手順を詳しく解説します。

はじめに

PostgreSQLは、世界で最も先進的なオープンソースのリレーショナルデータベース管理システム(RDBMS)です。Apple、IMDB、Skype、Uber、Lockheed Martin、Verizonなど、多くの大手企業や組織がPostgreSQLを採用しています。このRDBMSは1986年にカリフォルニア大学バークレー校のPOSTGRESプロジェクトの一環として誕生し、コアプラットフォームにおいて30年以上にわたる活発な開発が続けられてきました。

PostgreSQLは主要なすべてのOSで動作し、2001年からACID準拠となっています。ACIDとは、以下の4つの要素の頭文字を取ったものです。

• 原子性(Automicity):トランザクション全体が成功するか、まったく実行されないかのどちらかであることが保証されます。
• 一貫性(Consistency):すべてのデータが定義されたルール(制約、カスケード、トリガーなど)に従い、有効な状態であることが保証されます。
• 分離性(Isolation):すべてのトランザクションは独立して実行されます。トランザクションは、まだ完了していない他のトランザクションのデータを読み取ることはできません。
• 耐久性(Durability):トランザクションをコミットした後は、その直後にシステムクラッシュが発生しても、データはシステム内に保持されます。

人気の地理空間データベース拡張「PostGIS」をはじめとする強力なアドオンを備えているため、多くの人々や組織にとってPostgreSQLがオープンソースリレーショナルデータベースの第一選択となっているのは当然と言えるでしょう。

画像出典: https://postgresql-database.blogspot.com/2013/08/postgresql-architecture.html

サポート

本番環境向けのサポートSLAは、以下の企業から提供されています。

• https://www.enterprisedb.com
• https://www.2ndquadrant.com/
• https://www.revsys.com/
• https://imperoit.com/PostgreSQL_Support.htm

サポート対象バージョン:Current(12)/ 11 / 10 / 9.6 / 9.5 / 9.4
開発中バージョン:devel
サポート終了バージョン:9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2

インストールと設定

以下の手順に従って、Postgres 9.3をインストールおよび設定します。

Linux 7.1へのPostgres 9.3のインストール

Red Hat Linux 7.1にPostgres 9.3をインストールするには、まずOSのバージョンを確認します。

[root@snwdbsolpeprod01 ~]# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.1 (Maipo)

空のフォルダを作成する

データベースのインストール用に空のフォルダを作成します。

[root@snwdbsolpeprod01 mnt]# mkdir postt
[root@snwdbsolpeprod01 postt]# pwd
/mnt/postt

RPMをダウンロードする

Postgresのインストールを開始するために、OSのバージョンに対応したRPM(Red Hat Package Manager)を以下のコマンドでダウンロードします。

[root@snwdbsolpeprod01 postt]# wget https://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-redhat93-9.3-2.noarch.rpm

RPMをインストールする

以下のコマンドでRPMパッケージをインストールします。

[root@snwdbsolpeprod01 postt]# rpm -ivh pgdg-redhat93-9.3-2.noarch.rpm

追加パッケージをインストールする

RPMのインストール後、DBソフトウェアを導入するためのPostgreパッケージをいくつかインストールする必要があります。

[root@snwdbsolpeprod01 postt]# yum install postgresql-contrib.x86_64
[root@snwdbsolpeprod01 postt]# yum install postgresql93-server.x86_64

PGDATAの場所を設定する

データの保存場所を決定します。デフォルト以外の場所にデータを保存したい場合は、PostgreSQLサービスのsysconfigファイルを編集し、PGDATA引数を変更します。

vi /etc/rc.d/init.d/postgresql
vi /etc/sysconfig/pgsql/postgresql

注意:sysconfig/pgsql配下にPostgreSQLのファイルが存在しない場合は、新規作成し、データの保存先を示す行を追加してください。以下に例を示します。

[root@snwdbsolpeprod01 pgsql]# cd /etc/sysconfig/pgsql/
[root@snwdbsolpeprod01 pgsql]# vi postgresql
[root@snwdbsolpeprod01 pgsql]# cat postgresql
PDGATA=/mnt/postt

データベースを初期化する

最初のコマンド(一度だけ必要)は、PGDATA内のデータベースを初期化することです。

service <name> initdb

たとえば、バージョン9.3の場合は以下のようになります。

service postgresql-9.3 initdb

または、次のコマンドでも実行できます。

/usr/pgsql-9.3/bin/postgresql93-setup initdb

[root@snwdbsolpeprod01 data]# /usr/pgsql-9.3/bin/postgresql93-setup initdb
Initializing database ... OK

Postgresの自動起動を設定する

OS起動時にPostgreSQLを自動的に開始させたい場合は、以下のコマンドを使用します。

[root@snwdbsolpeprod01 data]# chkconfig postgresql-9.3 on

注意:'systemctl enable postgresql-9.3.service'へ要求が転送されます。

PostgreSQLサービスを開始する

PostgreSQLサービスを開始するには、以下のコマンドを実行します。

[root@snwdbsolpeprod01 data]# systemctl start postgresql-9.3.service

データベースを設定する

postgresql.confを更新することで、データベースを簡単に設定できます。以下に例を示します。

vi /var/lib/pgsql/9.3/data/postgresql.conf

以下の項目を変更します。

listen_address = '*'
port = 15000
max_connections=300
shared_buffers = 8192MB                 # min 128kB
                                        # (change requires restart)
temp_buffers = 128MB                    # min 800kB
max_prepared_transactions = 20          # zero disables the feature

log_destination = 'csvlog'
logging_collector = on
log_directory = '/mnt/pgsql/logs'
log_filename = 'postgresql-%a.log'

#------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------

autovacuum = on
# Enable autovacuum subprocess? 'on'
                                        # requires track_counts to also be on.
#log_autovacuum_min_duration = -1       # -1 disables, 0 logs all actions and
                                        # their durations, > 0 logs only
                                        # actions running at least this number
                                        # of milliseconds.
autovacuum_max_workers = 3              # max number of autovacuum subprocesses
                                        # (change requires restart)
autovacuum_naptime = 10080min           # time between autovacuum runs
autovacuum_vacuum_threshold = 1000      # min number of row updates before
                                        # vacuum
#autovacuum_analyze_threshold = 50      # min number of row updates before
                                        # analyze
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
                                        # (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000        # maximum Multixact age
                                        # before forced vacuum
                                        # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit

データベース接続設定を行う

データベース接続設定を制限または管理するには、以下のコマンドを実行します。

vi /var/lib/pgsql/9.3/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
local   all             postgres                                md5
local   all             postgres                                ident
# IPv4 local connections:
# IPv6 local connections:
host    all             all             ::1/128                 ident
host    all             all             0.0.0.0/0               md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

ファイアウォールを設定する

ファイアウォール上のポートを設定するには、以下のコマンドを実行します。

iptables -I INPUT -p tcp --dport 15000 --syn -j ACCEPT

service iptables save

service iptables restart

service postgresql-9.3 restart

ユーザーまたはロールを作成する

データベースに新しいユーザーやロールを作成するには、以下のコマンドを実行します。

su – postgres

psql -p 15000

postgres=# CREATE ROLE OCT1 LOGIN
  UNENCRYPTED PASSWORD 'test@123'
  INHERIT REPLICATION;

テーブルスペースを作成する

データベースに新しいテーブルスペースを作成するには、以下のコマンドを実行します。

postgres=# CREATE TABLESPACE OCT1_tablespace
  OWNER ilusr
  LOCATION '/usrdata/pgsql/data/oct';

データベースを作成する

新しいデータベースを作成するには、以下のコマンドを実行します。

postgres=# CREATE DATABASE OCT1
  WITH ENCODING='UTF8'
   OWNER=test
   LC_CTYPE='en_US.UTF-8'
   CONNECTION LIMIT=-1
   TABLESPACE=OCT1_tablespace;

基本コマンド

運用で役立つ基本的な管理コマンドを紹介します。

PostgreSQLの停止と起動

/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data stop
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data start
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D opt/PostgreSQL/9.4/data –m smart stop #wait for complete the transactions
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m fast stop #Immediate stop
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m immediate stop #Abort the DB
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m smart restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m fast restart
/opt/PostgreSQL/9.3/bin/pg_ctl -D /mnt/postt/data –m immediate restart

PostgreSQLのバージョンを確認する

postgres=# select version();

特定のデータベースレベルでのアクティビティを確認する

select pid,backend_xid,backend_xmin,query from pg_stat_activity ;

テーブルの状態を分析する

select relname,last_autoanalyze,last_analyze,n_mod_since_analyze from pg_stat_all_tables;

テーブルの物理パスを調べる

postgres=# SELECT pg_relation_filepath('testpitr1');

 pg_relation_filepath
 ----------------------
 base/13003/16399

[postgres@postgres221 data]$ ls -l /mnt/postt/data/base/13003/16399
-rw------- 1 postgres postgres 256024576 Feb 21 06:36 /mnt/postt/data/base/13003/16399

インスタンスまたはクラスタ内のスキーマ名を取得する

select schema_name from information_schema.schemata;

select nspname from pg_catalog.pg_namespace;

post_gre=# \dn
   List of schemas
   Name       |  Owner
--------------+----------
 kailash_test | postgres
 public       | postgres
(2 rows)

インスタンスまたはクラスタ内のデータベース名を取得する

template1=# select datname from pg_database;
 template1
 template0
 post_gre

 template1=# \l
 post_gre  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres          +
           |          |          |             |             | postgres=CTc/postgres +
           |          |          |             |             | kailash_s=CTc/postgres
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres           +
           |          |          |             |             |

template1=# select usename from pg_catalog.pg_user;
 kailash
 kailash_s
 postgres

template1=# \du
 kailash   |                                                | {}
 kailash_s | Superuser, Create role, Create DB              | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

まとめ

PostgreSQLのキャッチフレーズは「世界で最も先進的なオープンソースデータベース」ですが、単なるリレーショナルデータベースではなく、オブジェクトリレーショナルデータベースでもあります。この特徴により、MySQL、MariaDB、Firebirdなどの他のオープンソースSQLデータベースに対して優位性を持っています。社内のRDBMSをクラウド上のオープンソースRDBMSへ移行する際には、AWSクラウドでPostgreSQLを選択するのが自然な判断となるでしょう。

このブログシリーズのパート2では、PostgreSQLのバックアップ、リストア、リカバリについて解説します。

コメントやご質問がある場合は、フィードバックタブをご利用ください。チャットでもお気軽にお問い合わせいただけます。

当社のデータベースサービスについて詳しくはこちらをご覧ください。