PostgreSQL¶

インストール¶

Mac¶

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コマンドメモ¶

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

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

URL

psqlの終了¶

1	`postgres=# \q`

`relation does not exist`が出てきたとき¶

SHOW search_path;でスキーマが入っているか確認する
テーブル名が大文字を含むときはダブルクオートでテーブル名を括る

1	`SELECT * FROM "ApplicationUsers"; # このダブルクオートが必要`

SQLメモ¶

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などの環境変数を探す.

行削除¶

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

クリーンアップ¶

URL

SHOW data_directory; # Macだと`/opt/homebrew/var/postgres`

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

コマンドメモ¶

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

URL
dockerからの実行

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: ホストベースの認証. パスワードがプレーンテキストで送られるため, パスワード認証方式を使わないこと.

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

postgres=# \l
psql -l

データベース削除¶

URL

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

データベース作成¶

1	`postgres=# create database dbname;`

データベースに接続¶

postgres=# \c <dbname>

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

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

URL
Heroku

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`

テーブル削除¶

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

テーブルを全て削除¶

URL

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;`

ロール一覧¶

\du

ログイン¶

URL
-Uでユーザーを指定しないとOSにログイン中のユーザーでログインしようとする.
データベースのスーパーユーザーでログインするとプロンプトが#になる.

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.