UbuntuでPostgreSQLの操作メモ

PostgreSQLを使うにあたって調べたことのメモです（自分用）。
適宜、追記・訂正すると思います。

環境： Ubuntu 18.04.1 LTS

インストール

$ apt-get install -y postgresql-10

起動と停止

起動

$ /etc/init.d/postgresql start

停止

$ /etc/init.d/postgresql stop

最初の設定など

テーブルを作成してデータをインサート出来るようになるまでにいくつかやることがある。

ロールの作成

PostgreSQLはユーザ的なものとしてロールの設定が必要。
今回はデフォルトで用意されているpostgresでログインして作業を進める。
なおpostgresは初期状態ではパスワード未設定なのでsudo passwd postgresで設定する。

$ su - postgres
$ psql
Type "help" for help.
postgres=#

以下は作成例。

postgres=# create role test01 LOGIN CREATEDB PASSWORD 'test01';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test01    | Create DB

ロールはcreate roleで作成する。
ロールの属性を後から変更するときはalter role test01 LOGINなどを指定する。
属性を外したいときは、対になるNOLOGINが用意されているのでそれを指定する。
ロールの削除はdrop roleを使う。

詳しい属性については以下を参照。
https://www.postgresql.jp/document/9.4/html/sql-createrole.html

クライアント認証

このまま作成したロール権限でログインしようとしても以下のエラーが出る。
これはpeer認証がオンになっているためで、ユーザ名がOSのユーザ名と一致していないと認証できない。
http://www.postgresql.jp/document/9.1/html/auth-methods.html#AUTH-PEER

$ psql --username test01 --password
Password for user test01: 
psql: FATAL:  Peer authentication failed for user "test01"

そのため以下のように書き換える。

$ vim /etc/postgresql/10/main/pg_hba.conf 
# "local" is for Unix domain socket connections only
# local   all             all                                     peer
local   all             all                                     trust

なおpg_hba.confの場所がわからない場合、スーパーユーザであるpostgresなどで以下のクエリを実行すれば取れる。

# SHOW hba_file;
/etc/postgresql/10/main/pg_hba.conf

DB作成

DBを作成してロールを設定する。

postgres=# create database test01 owner test01;
CREATE DATABASE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test01    | Create DB                                                  | {}

接続

これで作成したロールを使って接続できる。
※psqlはオプションを省略するとOSのユーザ名が設定される。

$ psql --username test01 --password
Password for user test01: 
psql
Type "help" for help.
test01=>

テーブル操作

最低限の環境は整ったのでテーブルを作成してみる。

create table test (
  id serial,
  text varchar(500)
);

idで指定しているserial型はPostgreSQLの独自型でtest_id_seqというシーケンスも自動生成してくれる模様。

insert->selectも試してみたが問題なし。
これで一先ずテーブルの操作が出来ることを確認できた。

test01=> insert into test values(nextval('test_id_seq'), 'test2');
INSERT 0 1
test01=> select * from test;
 id |  text   
----+---------
  1 | test1
  2 | test2
(2 rows)

DB操作（psql）

\dでリレーション一覧
\duでロール一覧
\lでDB一覧
\dtでテーブル一覧