【備忘録】PostgreSQL コマンド集（for Mac）

Homebrew からの操作

■初期化

$ initdb /usr/local/var/postgres -E utf8

■起動

$ brew services start postgresql

■停止

$ brew services stop postgresql

■再起動

$ brew services restart postgresql

コマンドラインからの操作

■サーバの起動

$ pg_ctl start -D /usr/local/var/postgres

■サーバの終了

$ pg_ctl stop -D /usr/local/var/postgres

■サーバーが起動しているかどうかの確認

$ ps aux | grep postgres

■オーナーの変更

$ psql -U オーナー名 template1

template1=# ALTER DATABASE テーブル名 OWNER TO 新しいユーザー名;

■postgresユーザーの追加

$ createuser -s -P postgres

Enter password for new role: [Enter]

Enter it again: [Enter]

NOTICE:  empty string is not a valid password, clearing password

■データベースの確認

# psql -l

■バージョンの確認

# psql -version

■インストールされているか確認

# which psql

■サービスに登録されているか確認

# ls /etc/init.d/

●サービスの起動

$ postgres -D /usr/local/var/postgres

●データベースに接続

$ psql -d <DB_NAME> -U user -h host

* -d: データベース名(未指定だと、ログインユーザー名のデータベースに接続する)

* -U: ユーザ名(未指定だと、ログインユーザー名になる)

* -h: ホスト名(未指定だと、localhostになる)

※データベースへ接続するユーザを指定

$ psql -U <USER_NAME> 

※ユーザーを指定してデータベースに入る

$ psql -U <USER_NAME> <DB_NAME>

psql上で使うコマンド

●ユーザ一覧を表示

postgres=# \du;

■ユーザの作成：

postgres=# createuser -U <USER_NAME>  -P user1

Enter password for new role: （パスワードを入力）

Enter it again:（パスワードを入力）

●データベース一覧を表示

postgres=# \l;

●データベース作成

postgres=# create database <DB_NAME>;

postgres=# create -U postgres --owner=user1 example

■データベースの削除

postgres=# drop database <DB_NAME>;

●他のデータベースに接続

postgres=# \c <DB_NAME>;

●データベース削除

postgres=# drop database <DATABASE_NAME>;

●接続中のデータベースの情報を表示

postgres=# \conninfo;

●psqlの終了

postgres=# \q;

●テーブルの作成（SQLコマンド）

postgres=# CREATE TABLE customers

example-# ( id INTEGER PRIMARY KEY,

example(# name VARCHAR(20) NOT NULL,

example(# address VARCHAR(20)); ← セミコロンで文を閉じる

CREATE TABLE

●テーブル定義を確認

postgres=# \d tablename

tablenameには任意のテーブル名を入れる。

●テーブル一覧を表示

postgres=# \dt;

●テーブルのアクセス権限を表示

postgres=# \z <TABLE_NAME>;

●ユーザ一覧を表示

postgres=# \du

●現在のユーザーを表示

postgres=# select current_user;

●カレントディレクトリ変更

postgres=# \cd directory

カレントディレクトリをdirectoryに変更する。

●CSV形式のファイルをテーブルに挿入

postgres=# \copy tablename from filename DELIMITER AS ','

●ファイルからコマンドを実行

postgres=# \i filename.sql

ファイルから入力を読み取り、実行する。

●コマンドラインの履歴の表示

postgres=# \s

\sの後にファイル名を入力すると、そのファイル名に結果を出力する。

●'\'に関するヘルプの表示

postgres=# \?

●シェル上のコマンドを使いたい場合

postgres=# \! command

commandの部分にlsやpwdを入れるとpsql上でもシェル上のコマンドが実行できる。

●CollateとCTypeのデフォルトが en_US.UTF-8 なので変更

もし運用開始しちゃってたら一旦ダンプとってから。

CREATE DATABASE newdb WITH template template0 encoding 'utf8' lc_collate 'ja_JP.UTF-8' lc_ctype 'ja_JP.UTF-8';

もし運用するDB名が決まってたらリネーム

\c postgres

drop database maindb; # 最初に作られてたやつ消すとか

alter database newdb rename to maindb;

\c maindb