PostgreSQLの基本(ユーザー追加DB作成)

PostgreSQLは、GUIを伴うphpPgAdminなどのDB操作ツールがあり便利だが、これらがPostgreSQL本体の更新に追いついていない感もある。基本操作について言えば、コマンドラインから専用コマンドを使う方が確実である。本稿では、PostgreSQLの専用コマンドの一部を紹介する。

認証

peer認証

debianのPostgreSQLでは、DBの管理者がPostgreSQLのユーザーとしてpostgresで登録されている。このユーザはlinuxのユーザとしても登録されており、既定値設定では、linuxユーザpostgresとしてログインしなければPostgreSQLに接続し操作することができない。この設定をpeer認証と呼ぶ。peer認証の場合パスワードは必要ない。この設定は次のファイルにある。

/etc/postgresql/9.6/main/pg_hba.conf

local   all             postgres                                peer
local   all             all                                     peer

管理者権限

以降の説明のうちpsqlコマンドとpg_dumpコマンドを除いて、PostgreSQLの管理ユーザーであるpostgresで操作する前提で記載した。rootではDBの操作は行わない。また、環境変数usernameにDBユーザー名を、環境変数databasenameにデータベース名をそれぞれ設定する。途中のcdコマンドは、linuxユーザーpostgresのhomeデレクトリィでる/var/lib/postgresqlに移動する。

suコマンドの実行にはroot権が必要

su postgres
cd 
username=DBユーザー名
dbname=データベース名

パスワードを環境変数で指定

環境変数 PGPASSWORD にパスワードを設定すると、下記のツールは自動的にパスワードとして利用する。環境変数は、root権があれば他のプロセスから読み出すことができるので注意が必要だ。他にもここでは触れないが、~/.pgpassword使う方法がある。

PGPASSWORD=パスワード

ユーザーの追加と削除

ユーザー作成

createuser -P  $username

-Pでパスワードを設定する。(対話形式)

参考/パスワード生成のワンライナー

perl -e "@c = (a..z,A..Z, 0..9); print @c[rand @c] for 1..10"

ユーザー削除

dropuser  $username

データベースの追加と削除

データベース作成

createdb -O $username $dbname

規定文字コードはUTF8になる。

データベース削除

createdb  $dbname

データベーススキーマの表示

pg_dump  -s $dbname -U $username -w

データベースバックアップ

Linuxのユーザーpostgresで操作するときは、デレクトリィのパーミッションに注意する。

pg_dump $dbname -U $username -w | gzip  ${dbname}.sql.gz

データベース復元

postgresなどの管理者権限で行う。

createdb -O $username $databasename
zcat ${dbname}.sql.gz | psql $dbname -U $username -w

データベースの操作

psqlコマンド

psql $dbname -U $username -w

psqlでの対話モードには、通常のSQLの他に\を使ったコマンドが使える。

コマンド	意味
\q	終了
\t	テーブルリスト
\l	データベースのリスト
?	\コマンドのヘルプ