PostgreSQL操作コマンド一覧

Ubuntu上でのPostgreSQLの設定

この記事のまとめ：

Ubuntu上でPostgreSQLサーバーを操作する際に多用するコマンドをまとめています。

はじめに：本記事での表記について

コマンドについて説明する機会が多々ありますが、それぞれどのターミナル上で実行しているかを区別するために文頭の記号ごとにそれぞれ下記を意味しています。

$  → Linuxターミナル上でのコマンド (一般ユーザーでのログイン中)
=# → PostgreSQL上でのコマンド (スーパーユーザーでログイン中)
=> → PostgreSQL上でのコマンド (一般ユーザーでのログイン中)

データベースへの接続

リモート接続コマンド

下記のコマンドでデータベースにリモートが接続できます。

$ psql -h [ホスト名 or IPアドレス] -U [ユーザー名] -d [DB名]

データベース一覧表示

下記のコマンドでデータベースの一覧を表示できます。

$ psql -l

もしくは、

=> \l

データベースから出る

下記のコマンドでログイン中のデータベースからログオフできます。

=> \q

ユーザー (ロール)操作

ユーザーの追加

スーパーユーザー権限のあるユーザーで接続したのちに下記のコマンドで新しい新規ユーザーを作成できます。

=# CREATE role [ユーザー名] with login password '[パスワード]'

ユーザーのパスワード変更

スーパーユーザー権限のあるユーザーでログインをして、下記のコマンドでパスワードの設定ができます。

=# ALTER role [ユーザー名] with password '[パスワード]';

ユーザーの一覧を表示

下記のコマンドで現在ログインしているデータベースのユーザーの一覧を表示できます。

=> \du

データベース操作

データベースの新規作成

データベースの作成には、スーパーユーザー権限を持ったユーザーでデータベースに接続している必要があります。権限のあるユーザーで接続したのちに下記のコマンドで新しいデータベースを作成できます。

=# CREATE DATABASE [データベース名] OWNER [ユーザー名];

データベースのオーナー変更

下記のコマンドでデータベースのオーナーを変更ができます。

=# ALTER DATABASE [データベース名] OWNER TO [ユーザー名];

データベース名の変更

下記のコマンドで作成したデータベース名を変更ができます。

=# ALTER DATABASE [現在のデータベース名] RENAME to [新しいデータベース名]

テーブル操作

テーブルの新規作成

下記のコマンドで現在接続しているデータベースに新しいテーブルを作成することができます。

=> CREATE TABLE [テーブル名]
   ([カラム名] [データ型], [カラム名] [データ型]);

接続しているデータベースとは異なるデータベースにテーブルを新規作成することもできます。その際は下記のようにテーブル名の前にデータベース名を記載します。

=> CREATE TABLE [データベース名].[テーブル名]
   ([カラム名] [データ型], [カラム名] [データ型]);

参考

データ型 (https://www.postgresql.jp/document/9.6/html/dataTYPE.html)
日付/時刻型データ型 (https://www.postgresql.jp/document/9.4/html/dataTYPE-datetime.html)

テーブルにカラム (列)の追加

すでに作成してあるテーブルに新たにカラムを追加する場合には、下記のコマンドを使います。

=> ALTER TABLE [テーブル名] ADD [カラム名] [データ型];

カラム名の変更

すでに作成してあるカラムのカラム名を変更する場合には、下記のコマンドを使います。

=> ALTER TABLE [テーブル名] RENAME COLUMN [現在のカラム名] TO [新しいカラム名];

カラムのデータ型の変更

すでに作成してあるカラムのデータ型を変更する場合には、下記のコマンドを使います。

=> ALTER TABLE [テーブル名] ALTER [カラム名] TYPE [データ型];

テーブルレコードの全削除

下記のコマンドテーブルの全レコードを削除できます。

=> TRUNCATE TABLE [テーブル名];

その他テーブル操作

テーブル一覧表示(viewやsequenceも含む)

=> \d

テーブル一覧表示

=> \dt

テーブルのスキーマの詳細を表示

=> \d <TABLE_NAME>

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

=> \z <TABLE_NAME>

データベースのコピー

データベースの情報すべてをコピー

DBのファイルからユーザー情報まですべてをコピーコピー元からダンプします。

$ pg_dumpall --username=postgres -f <ファイル名>

コピー先にダンプしたファイルをコピーし、リストアします。

$ pg_restore -v --host=localhost --username=postgres  --dbname=<DB名> <ファイル名>

特定のテーブルを別のサーバーもしくは別のデータベースにコピー

実行方法は2つあります。特に結果に違いはありませんが、やりやすい方でやってみてください。なお、どちらもあらかじめコピー先のデータベースにはコピー元と全く同じカラムのテーブルを作成しておく必要があります。

1. テーブルのレコード情報を一旦ローカルに退避する方法

$ pg_dump -h [コピー元ホスト名] -U [ユーザー名] -p [ポート番号] -d [コピー元DB] -t [テーブル名] > [退避ファイル名]
$ cat [退避ファイル名] | psql -h [コピー先ホスト名] -U [ユーザー名] -p [ポート番号] -d [コピー先DB]

catコマンドを使用するのでLinux (Unix)系が対象ですが、Windowsでも同様のコマンドを使用すれば問題ないかもしれません。

2. 退避しない方法

標準入出力から直接コピー元からコピー先にレコード情報をコピーしますので、まずそれぞれのデータベースにログインする際にパスワードを聞かれないようにします。

Windowsの場合には、%APPDATA%\postgresql\pgpass.confファイルに、Linuxの場合には、~/.pgpassファイルに次の形式でコピー元とコピー先のデータベースのログイン情報を記載します。

[ホスト名]:[ポート番号]:[DB名]:[ユーザ名]:[パスワード]

記載が終わったら、念のためどちらのデータベースにもパスワード入力なしにログインできるか確認してください。

下記のコマンドにより、テーブルのコピーがd系ます。

$ psql -h [ホスト名] -U [ユーザ名] -p [ポート番号] -d [コピー元のDB名] -c "COPY (SELECT * FROM [コピー元のテーブル名] WHERE ~ ) TO STDOUT" | PGPASSWORD=[パスワード] psql -h [ホスト] -U [ユーザ名] -p [ポート番号] -d [コピー先のDB名] -c "COPY [コピー先のテーブル名] FROM STDIN"

参考

マニュアル

日本語で最も詳しく書いてあるマニュアルはこちら。 https://www.postgresql.jp/document/9.6/html/index.html コマンド一覧はこちら。文法がある程度わかってきたらこれで探した方が早いです。 https://www.postgresql.jp/document/9.6/html/reference.html

今回は以上です。最後まで読んでいただき、ありがとうございます。

　← 気に入っていただければ応援ポチをお願いします！

Search This Blog