PostgreSQL リファレンス

PostgreSQL のインストール

　ここでは Ubuntu における PostgreSQL のインストールからユーザーの設定、PostgreSQL へのログインについて簡単に解説します。

　インストールには Ubuntu や CentOS などのディストリビューションからインストールする方法と、PostgreSQL のサイトからソースをダウンロードしインストールする方法があります。
　開発環境をディストリビューションを利用して構築するのなら、PostgreSQL もディストリビューションを利用してインストールすることをお勧めします。

PostgreSQLのインストール

　管理者権限で、aptによってインストールします。

$ sudo apt install postgresql

　インストールしただけの状態で公のサーバーとして利用するなら問題があります。スーパーユーザーの設定、アクセスログの記録などのセキュリティー設定や記録ディレクトリの設定などが必要になります。

　初期のスーパーユーザーはpostgresになっています。さらにパスワードを設定しなければなりません。
　詳しくは初期状態でのpostgresユーザーによるログインを参照して下さい。

$ sudo apt install postgresql-10 postgresql-server-dev-10 libpq-dev

PostgreSQLの操作（ psql ）

　psql は端末から利用できる PostgreSQL 用のインターフェイスです。

　OS の端末から psql コマンドでPostgreSQLのコマンドラインインターフェイスにログインします。

$ psql
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.
...

　psql コマンドでデータベース名を指定しない場合はユーザー名と同じデータベース名に接続されます。

　詳しくはpsqlを参照して下さい。

　初期状態ではpostgresで PostgreSQL のコマンドラインインターフェイスにログインしてみると思います。 しかし、初期状態のpostgresユーザーは Ubuntu においてはパスワードが設定されていません。
　詳しくは初期状態でのpostgresユーザーによるログインを参照して下さい。

　PostgreSQL からログアウトする場合は以下のコマンドです。

=# \q

ユーザー（ロール）の管理

ロールについて

　PostgreSQLでは、ユーザーはロールとして管理します。データベースへのログインや操作権限はロールで管理されます。

　PostgreSQLへのログインや操作権限はユーザーごとに管理するのではなく、ロールごとに管理します。ロールにロールを登録することでグループとして管理することもできます。
　ロールはデータベース・クラスタ（インスタンス）ごとに管理されます。データベースごとには管理ができないので注意してください。

　ロールの作成と設定は以下の手順で行います。

データベース（テーブルなどを含む）
↓
ロールの作成
	ロール名
↓
ロールの設定
	データベース作成許可、ロールの追加許可、PostgreSQLへのログイン、パスワード設定
↓
データベースやテーブルなどにロールを設定

　ロールの管理はスーパーユーザーかロールの追加権限のあるユーザーで行います。
初期状態では「postgres」ユーザーがスーパーユーザーです。

（「postgres」というユーザー名は知られている名称ですので公のサーバーでの使用は好ましくありません。
　ただし、予め postgres ユーザーの代替えユーザーを設定しておかない限りは、postgres ユーザーはサーバーの起動で使用しますので OS 上から削除するのは後々不具合が起こることがあります。
　postgres をそのまま使用する場合には、パスワードの設定や外部から postgres でログインできないようにするといった対処が必要でしょう。）

ロールの一覧

　 psqlにログインし、\duコマンドを実行します。

$ psql
...

=# \du

テーブルごとのロール設定

　テーブルごとのロールの設定を確認することができます。\dpコマンドでテーブルを指定します。

=# \dp table_name

　詳しくは「PostgreSQLユーザー（ロール）管理」を参照してください。

　外部からPostgreSQLのデータ操作をするには、PostgreSQLへのログイン認証、ロールの設定、テーブルごとのロール設定が必要になります。
　ログイン認証、ロールの設定、テーブルごとの設定については「ログイン認証とロールの設定」を参照して下さい。

　スーパーユーザーまたはデータベース・クラスタ（インスタンス）を追加した場合は、 Peer 認証を設定しないと PostgreSQL にアクセスできません。
　ローカルで PostgreSQL へのアクセスにおいて、ver 8.1 より後では OS のユーザーと PostgreSQL でのロールは一致していません。ローカルでのデータベースへのアクセスには Peer 認証を設定する必要があるかもしれません。

ロールとデータベース・クラスタ

　ロールはデータベース・クラスタ（インスタンス）ごとに管理することができます。
　データベース・クラスタごとにロールを管理することができるため、１つのサーバーで事業所や個人ごとにデータベースを作成するといったユーザーのグループを分けたいときにはデータベース・クラスタを利用します。

rolename@pc $ psql -p 5435 -d postgres

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5435" failed:
FATAL:  Peer authentication failed for user "rolename"

# Database administrative login by Unix domain socket
local   all             postgres                                peer
local   all             rolename                                peer

データベースの管理（確認、作成、接続、削除）

データベースの管理としては主に既存データベースの一覧で確認、データベースの作成、削除、オーナー管理、ディレクトリ管理があります。ここでは簡単に説明します。

　詳しくは、データベースの管理（一覧、作成、接続、削除）を参照して下さい。

$ psql -l

データベースの作成

　まず最初にデータを統括するためのデータベースを作成しなければなりません。データベースにデータを収めるためのテーブルやビュー、トリガといったスクリプトなどが入ります。

# CREATE DATABASE new_database OWNER owner_name;

　ログイン可能なユーザーを追加し、そのユーザーでpsqlコマンドを実行するとデータベースがないというエラーが返ってくることがあります。 そのような場合は、ユーザー名のデータベースを作ります。

# CREATE DATABASE user_name OWNER user_name;

　詳しくは「データベースの管理（一覧、作成、接続、削除）」を参照してください。

　データベースはデータベース管理システムによって統括して管理されています。
　データベース管理システムの中にデータ・クラスタがあり、データベース・クラスタは複数のデータベースを管理しています。

　データベース・クラスタについてはデータベース・クラスタ（データベース・インスタンス）を参照してみてください。

テーブル定義

　テーブルとは、データベースにおいてデータを格納するために用意する表のようなものです。

CREATE TABLE table_name(
	id	SERIAL PRIMARY KEY,
	name	VARCHAR( 30 )
);

postgres=# \dt

postgres=# \dt table_name

　テーブルの操作に関して詳しくはテーブルを参照してください。

SQLファイルの読み込み

　SQLファイルを、PostgreSQLのコマンドラインインターフェイスから読み込み、実行することができます。

database=> \i DataBase/sql_file.sql

　読み込むsqlファイルはカレントディレクトリからファイル名で辿ります。読み込み先のデータベース名は確認するようにします。

　詳しくはSQLファイルの読み込みと実行を参照してください。

PostgreSQLの自動起動（デーモンプロセス設定）

　Ubuntu のパッケージから PostgreSQL をインストールしたら、標準ではコンピュータの起動時に PostgreSQL も起動します。
　もしも PostgreSQL を自動で起動しないように設定するには、 PostgreSQL の自動起動（デーモンプロセス設定）を参照してください。

データのエクスポートとインポート

　psqlコマンドプロントで利用する\COPYコマンドを使う方法があります。
　\COPYコマンドによってPostgreSQLデータベースのテーブルレコードの出力と取り込みをすることができます。

詳しくはデータのエクスポートとインポートについてを参照してください。

その他

　PostgreSQL を運用する上での Tips です。

日本語のソート

日本語をソートすると上手くいかない場合があります。多くの場合、LC_COLLATE の設定が英語になっています。その場合のソートの文字列の指定方法などについてです。