コンテンツにスキップ

PostgreSQL

インストール

Mac

1
2
brew upgrade
brew install postgresql

諸注意: 市民的MySQLとの違い

  • PostgreSQLではデータベースユーザーをロールと呼ぶ
  • コマンドラインでユーザー名を省略してログインしようとするとOSのログインユーザー名でデータベースに接続しようとする

\コマンド: メタコマンド

  • \d: 全てのテーブルのリスト

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

postgres=# \?

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

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

docker連携

  • .envからPOSTGRES_USERを削除するとdocker起動時にワーニングが出るため残しておく.

psqlコマンドメモ

1
2
psql -c "SELECT current_time"
psql -f example.sql

psqlログイン後に使うコマンド

psqlの終了

1
postgres=# \q

relation does not existが出てきたとき

  • SHOW search_path;でスキーマが入っているか確認する
  • テーブル名が大文字を含むときはダブルクオートでテーブル名を括る
1
SELECT * FROM "ApplicationUsers"; # このダブルクオートが必要

SQLメモ

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT current_database();
SELECT current_user;
SELECT version();


SET password_encryption='scram-sha-256';
\password

SHOW data_directory

CREATE DATABASE <dbname>

\x # 拡張モードに切り替え

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

1
postgres=# \cd directory

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

環境変数読み込み

パラメーターを設定しない場合, PGHOSTまたはPGHOSTADDR, PGPORT, PGDATABASE PGUSERなどの環境変数を探す.

行削除

1
2
DELETE FROM order_header WHERE id<3500 OR id>5775;
DELETE FROM order_details WHERE order_id<3500 OR order_id>5775;

クリーンアップ

1
2
3
4
SHOW data_directory; # Macだと`/opt/homebrew/var/postgres`

rm -rf <dirname>
initdb -D <dirname>

コマンドメモ

コマンドラインからSQL発行

  • URL
  • dockerからの実行
1
dc exec pgsql psql -U <dbuser> <dbname> -c 'drop schema public cascade; create schema public' && efm add Initialize && efd update

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

1
postgres=# \s

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

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

1
postgres=# \! command

接続サービスファイル

システム全体は/etc/pg_service.conf, 個々のユーザーは~/.pg_service.conf.

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

1
postgres=# \conninfo

設定ファイル

  • postgresql.conf
  • pg_hba.conf: ホストベースの認証. パスワードがプレーンテキストで送られるため, パスワード認証方式を使わないこと.

データベース一覧を表示

1
2
postgres=# \l
psql -l

データベース削除

1
2
DROP DATABASE <dbname>
DROP SCHEMA public # mysqlでいうDATABASEがSCHEMA?

データベース作成

1
postgres=# create database dbname;

データベースに接続

1
2
3
4
5
postgres=# \c <dbname>

select current_database(); # データベース名確認
select current_schema();   # 現在のスキーマ確認
SELECT * FROM "ApplicationUserShops"; # 大文字を含む場合はダブルクオートで括る

データベースのダンプとインポート

1
2
3
4
5
6
7
pg_dump -h 'example.com' -p 1921 -U postgres -d my_schema -v > ./init.sql
pg_dump -U postgres -d laravel -v > ./init.sql

psql -h example.com -p 1921 -U postgres -d my_schema -f ./example.dump


pg_restore -U postgres -d laravel ./latest.dump

テーブル一覧を表示

1
postgres=# \z

テーブル削除

1
2
DROP TABLE blackusers, cars CASCADE
DROP TABLE blackusers, cars_settlements, failed_jobs, line_groups, materials, migrations, order_details, order_headers, password_resets, personal_access_tokens, products, products_materials, products_products, settlements, users, cars CASCADE

テーブルを全て削除

1
2
drop schema public cascade;
create schema public;

テーブル定義を確認

1
postgres=# \d tablename

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

パスワードファイル

  • パスワードファイルは~/.pgpassが標準. chmod 600を設定していないと無視される.

パスワード変更

1
alter role <dbuser> with password '<dbpass>';

他のデータベースに接続

1
postgres=# \c dbname

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

1
postgres=# \i filename.sql

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

役割(ロール)の管理

  • CREATE ROLE, ALTER ROLE, DROP ROLEがある.

ユーザ一覧を表示

1
postgres=# \du

ロール作成

1
create user admin SUPERUSER;

ロール一覧

1
\du

ログイン

  • URL
  • -Uでユーザーを指定しないとOSにログイン中のユーザーでログインしようとする.
  • データベースのスーパーユーザーでログインするとプロンプトが#になる.
1
2
3
4
5
6
7
psql -d <database> -U <user> -h <host>
psql <database>
psql -U admin
psql postgresql://myuser:mypasswd@myhost:5432/mydb
psql -Upostgres # ログイン
psql -h myhost -p 5432 -d mydb -U myuser
psql postgresql://myuser@myhost:5432/mydb

ログの場所

  • Debian/Ubuntuでは/var/log/postgresql.