Zenn
🐘

PostgreSQL チートシート

2021/02/15に公開
PostgreSQL
tech

凡例

この記事ではコマンドラインツールでコマンドを実行する場合、コマンドの前に次のプロンプトをつける。

一般ユーザの場合

=> COMMAND;

スーパーユーザ（postgres）の場合

=# COMMAND;

コマンドラインツールの操作

基本操作

  • \l …… データベースの一覧
  • \c データベース …… データベースを切り替える
  • \d …… テーブル、ビュー、シーケンスの一覧
  • \dt …… テーブル一覧
  • \ds …… シーケンス一覧
  • \dv …… ビュー一覧
  • \di …… インデックス一覧
  • \d 名前 …… 指定したテーブル、ビュー、シーケンス、インデックスの定義
  • \i ファイル …… ファイルに記述された SQL を実行する
  • \q …… コマンドラインツールを終了する
  • \? …… ヘルプを表示

\d コマンドは次のようにパターンにマッチしたものを表示することもできる。

=> \dt *place*

入出力エンコーディングを確認・設定する

Windows では標準のシェルが UTF-8 で動いていないのでたまにやらかす。そんなときは入出力エンコーディングを設定してやる。

入出力エンコーディングを確認する

=> show client_encoding;
 client_encoding
-----------------
 SJIS
(1 row)

入出力エンコーディングを設定する（クライアントを終了するまで）

=> \encoding 'UTF8'
=> show client_encoding;
 client_encoding
-----------------
 UTF8
(1 row)

デフォルトの入出力エンコーディングを設定するには、次の環境変数を設定する。

PGCLIENTENCODING=UTF8

ユーザ・データベースの作成・変更

既存のデータベースをコピーする

=# create database 新データベース with template = コピー元データベース;

PostgreSQL ではデータベースの作成は実際にはデータベース template1 のコピーとして行われる。そのテンプレートに既存のデータベースを指定することでそのデータベースをコピーできる。

テーブルの所有者を一括変更する

=# \c データベース
=# reassign owned by 旧ロール to 新ロール;

選択中のデータベースのテーブルやビュー、シーケンスなどの所有者を一括で変更できる。間違ったユーザでリストアしたときなどに便利。ただし postgres ユーザからの変更はできないらしい。

データベースのダンプとリストア

pg_dump の基本的な使い方

pg_dump -U ユーザ データベース > 出力.sql

特定のテーブルのみを出力するには -t オプションを使用する

pg_dump -U ユーザ -t テーブル1 [-t テーブル2 ...] データベース > 出力.sql

次のオプションを使用できる。

  • -O …… テーブルの所有権に関する情報を出力しない
  • -a …… テーブル定義を省略し、データのみを出力する
  • --column-inserts …… INSERT コマンドとして出力する。PostgreSQL 以外のシステムにデータを移行する際に有用

リストア

リストアは pg_dump で出力したファイルを SQL として実行させるだけでよい。

cat 出力.sql | psql -U ユーザ データベース

またはコマンドラインツールで次のようにする。

=# \i 出力.sql

Discussion