PostgreSQLのセットアップでいつもハマル僕のための覚書

PostgreSQLには苦手意識を持っている。MySQLならこんな簡単なのに・・・・。と。単に使う機会が少ないので覚えないだけだったりするのだけど。。。

Roleの作成

MySQLのユーザーに対応するのがPostgreSQLのRoleの概念。

とりあえず、postgresのスーパーユーザーでpsqlコマンドでログインした後には一般ユーザー的なそれを作っておく。

postgres=# CREATE ROLE foo WITH LOGIN PASSWORD 'foo_password'

pg_hba.conf をいじってログインできるようにする。

ホスト別のアクセスコントロール。ここで、peerとなっているとPostgresのロールとUnixログインのアカウントが結び付けられる。つまり、先ほど作ったpostgresのロールfooでログインしたい場合、

psql -U foo -W

で求められるプロンプトに入力するのはた際に先ほどのfoo_passwordではなく、Unixログインアカウントfooのパスワードなのである。

ということで、pg_hba.confを下記のように変更。md5というのは、create roleで設定したパスワードにするということ。

local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

psql -Uでログインできない

MySQLのmysqlコマンドのノリでログインしようとするとログインできない。

$ psql -U foo -W

とすると、パスワードがあっててもログイン後に即座に「foo」というデータベースにつなげようとするのでこのデータベースがありませんエラーでログアウトされてしまう。そこで、必ずあるデータベースであるpostgresに接続指定する。

$ psql -U foo -W -d postgres

で何とかログインする。

データベースを作るにはテンプレートが必要

create databaseしようとしたらエラーが出た。

No relations found.
postgres=# create database  "xxx_development" ENCODING = 'unicode'
postgres-# ;
ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)
HINT:  Use the same encoding as in the template database, or use template0 as template.

...templateってなんだっけ・・・と思って調べる・・・

blog.netandfield.com

PostgreSQL の createdb は、実際には template1 という空の DB をコピーして新しい DB を作成する。
で、この template1 という DB には initdb した時に指定した文字コード情報が登録されているのだ。
だから、「UTF-8 の文字コードを使う DB をコピーして、EUC-JP の文字コードを使う DB は作れんでぇ」と怒られてる。

じゃあ、どうすればいいか？・・・だが、エラーメッセージに HINT として示されているように、template0 を使えば良いわけね。
template0 は、文字コードの情報を持っていない、本当にピュアな DB なので、他の文字コードの DB の元ネタになるってわけだね。

ということで、

postgres=# create database  "xxx_development" ENCODING = 'unicode' --template=template0

として作成完了。

template0がなんかLATIN1になってるからやっぱりUTF8で作れない

Ubuntu16のまんまその状態だとこうなってた。

postgres=# \l
                                      List of databases
            Name             |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------------------------+----------+----------+---------+-------+-----------------------
 enju_leaf_junoe_development | junoe    | UTF8     | C       | C     |
 enju_leaf_junoe_test        | junoe    | UTF8     | C       | C     |
 postgres                    | postgres | LATIN1   | en_US   | en_US |
 template0                   | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
                             |          |          |         |       | postgres=CTc/postgres
 template1                   | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
                             |          |          |         |       | postgres=CTc/postgres

template2をUTF8で作る。（これがベストなのかは知らない）

postgres@local:~$ createdb template2 --locale=C --encoding=UTF8 --template=template0

そのあと、このtemplate2を基にしてDBを作成する。

tohokuaikiのチラシの裏

技術的ネタとか。