RAKUS Developers Blog | ラクス エンジニアブログ

株式会社ラクスのITエンジニアによる技術ブログです。

トップ > PostgreSQL > 【PostgreSQL】テーブル・DBを閲覧・コピーするときに知っていると便利なテクニック

【PostgreSQL】テーブル・DBを閲覧・コピーするときに知っていると便利なテクニック

PostgreSQL

はじめに

こんにちは、yk_itgです。

これまでいくつかPostgreSQLの記事を作成しましたが、今回は知っていると便利だと思うテーブル・DBの閲覧・コピーに関するtipsをまとめてみました。
私はテストを実施する時に結果を確認したり、データを用意する際によく使います。
PostgreSQLユーザなら必須のテクニックを紹介していきますので、どうぞお役立てください！

テーブルを閲覧する

テーブルの情報を確認したい：\d

\d {テーブル名} をpsql上で実行するとテーブルの情報を表示することができます。
インデックスやシーケンスなどテーブル以外のリレーションを表示することも可能です。

postgres=# \d test
                 テーブル "public.test"
  列  |   型    | 照合順序 | Null 値を許容 | デフォルト
------+---------+----------+---------------+------------
 id   | integer |          | not null      |
 hoge | text    |          |               | 'a'::text
インデックス:
    "test_pkey" PRIMARY KEY, btree (id)
    "test_hoge_idx" btree (hoge)
postgres=# \d test_pkey
インデックス "public.test_pkey"
 列 |   型    | キー? | 定義
----+---------+-------+------
 id | integer | はい  | id
プライマリキー, btree, テーブル "public.test" 用

テーブルの一覧を確認したい：\dt

\dtをpsql上で実行するとテーブルの一覧を表示することができます。
テーブル以外にも\di、\ds、\dvを使えば、それぞれインデックス、シーケンス、ビューの一覧を表示することができます。

postgres=# \dt
           リレーション一覧
 スキーマ | 名前 |    型    |  所有者
----------+------+----------+----------
 public   | test | テーブル | postgres
(1 行)

実行結果を見やすくしたい：\x

\xをpsql上で実行するとSQLやメタコマンド等の実行結果を拡張表示（縦に表示）することができます。
例えば、カラムが多すぎてターミナルで折り返して表示されてしまう場合に便利です。
逆に見づらい場合には、もう一度\xを実行すると元の表示に戻ります。

postgres=# SELECT * FROM test;
 id |   hoge1    | hoge2 | hoge3 | hoge4 | hoge5 | hoge6 | hoge7 | hoge8 | hoge9 | hoge10 | hoge11 | hoge12 | hoge13 | hoge14 | hoge15 | hoge16 | hoge17 | hoge18 | hoge19 | hoge20
----+------------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------
  1 | ABCDEFGHIJ |       |       |       |       |       |       |       |       |        |        |        |        |        |        |        |        |        |        |
(1 行)
postgres=# \x
拡張表示は on です。
postgres=# SELECT * FROM test;
-[ RECORD 1 ]------
id     | 6
hoge1  | ABCDEFGHIJ
hoge2  |
hoge3  |
hoge4  |
hoge5  |
hoge6  |
hoge7  |
hoge8  |
hoge9  |
hoge10 |
hoge11 |
hoge12 |
hoge13 |
hoge14 |
hoge15 |
hoge16 |
hoge17 |
hoge18 |
hoge19 |
hoge20 |

実行結果をファイルに出力して確認したい：\o

\o {ファイルパス}をpsql上で実行すると実行結果を指定したファイルに出力することができます。
ターミナル以外で実行結果を確認したい場合や保存したい場合に便利です。
SQL実行後に\oを実行すると出力先を標準出力に戻すことができます。

postgres=# \o result.txt
postgres=# SELECT * FROM test;
postgres=# \o
postgres=# SELECT * FROM test;
 id |    hoge
----+------------
  8 | ABCDEFGHIJ
(1 行)

result.txt

 id |    hoge    
----+------------
  8 | ABCDEFGHIJ
(1 行)

DBを閲覧する

DBの一覧を確認したい：\l

\l をpsql*1上で実行するとDBの一覧を表示することができます。
または psql -lでターミナルを起動せずに表示することもできます。

postgres=# \l
                                                    データベース一覧
               名前                |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |     アクセス権限
-----------------------------------+----------+------------------+----------+-------------------+-----------------------
 postgres                          | postgres | UTF8             | C        | C                 |
 template0                         | postgres | UTF8             | C        | C                 | =c/postgres          +
                                   |          |                  |          |                   | postgres=CTc/postgres
 template1                         | postgres | UTF8             | C        | C                 | =c/postgres          +
                                   |          |                  |          |                   | postgres=CTc/postgres
(3 行)

テーブルをコピーする

続いてはコピー作業のテクニック、まずはテーブル編の紹介です。
なお、これからご紹介する内容は、本番環境での利用は想定していません。
誤って重要なデータの書き替えが発生してしまう可能性もありますので、ご利用にはくれぐれもご注意ください。

テーブルの構造をコピーしたい：CREATE TABLE (LIKE)

CREATE TABLE {コピー先} (LIKE {コピー元} )*2のSQLを実行するとコピー元と同じ構造のテーブルを作成することができます。
LIKEの中でINCLUDING ALLを指定すると構造だけでなく、制約やデフォルト値、付与されているインデックス等もコピーすることができます。

postgres=# CREATE TABLE test2 (LIKE test);
CREATE TABLE
postgres=# \d test2
                テーブル "public.test2"
  列  |   型    | 照合順序 | Null 値を許容 | デフォルト
------+---------+----------+---------------+------------
 id   | integer |          | not null      |
 hoge | text    |          |               |
postgres=# CREATE TABLE test3 (LIKE test INCLUDING ALL);
CREATE TABLE
postgres=# \d test3
                テーブル "public.test3"
  列  |   型    | 照合順序 | Null 値を許容 | デフォルト
------+---------+----------+---------------+------------
 id   | integer |          | not null      |
 hoge | text    |          |               | 'a'::text
インデックス:
    "test3_pkey" PRIMARY KEY, btree (id)
    "test3_hoge_idx" btree (hoge)

テーブルのレコードをコピーしたい：INSERT INTO SELECT

INSERT INTO {テーブル名} SELECT * FROM {テーブル名}のSQLを実行するとSELECTの結果をそのままコピーすることができます。
そのままコピーすると主キー制約に引っかかる場合には、CREATE TABLE {コピー先} (LIKE {コピー元} )を使ってtempテーブルを作成し、変更したいカラムのみをUPDATEしてからINSERTすると主キーのカラム以外を考えずにコピーすることができます。

postgres=# SELECT * FROM test2;
 id |    hoge
----+------------
 10 | ABCDEFGHIJ
(1 行)
postgres=# INSERT INTO test2 SELECT * FROM test2;
INSERT 0 1
postgres=# SELECT * FROM test2;
 id |    hoge
----+------------
 10 | ABCDEFGHIJ
 10 | ABCDEFGHIJ
(2 行)
postgres=# SELECT * FROM test;
 id |    hoge
----+------------
  1 | ABCDEFGHIJ
(1 行)
postgres=# CREATE TEMPORARY TABLE temp_test (LIKE test);
CREATE TABLE
postgres=# INSERT INTO temp_test SELECT * FROM test WHERE id = 1;
INSERT 0 1
postgres=# UPDATE temp_test SET id = 2;
UPDATE 1
postgres=# INSERT INTO test SELECT * FROM temp_test;
INSERT 0 1
postgres=# SELECT * FROM test;
 id |    hoge
----+------------
  1 | ABCDEFGHIJ
  2 | ABCDEFGHIJ
(2 行)

DBをコピーする

テーブルの次はDB編のご紹介です。
こちらもテーブル編と同様、本番環境での利用は想定したものではありません。
環境へ重大な影響を及ぼす内容となりますため、ご注意の上での利用をお願いします。

DBをコピーしたい：createdb -T

createdb -T {コピー元} {コピー先}*3のコマンドを実行するとコピー元DBの内容でコピー先DBを作成することができます。
後述のpg_dumpと比較すると実行速度が速い印象です。

>psql -U postgres -d postgres
postgres=# \dt
            リレーション一覧
 スキーマ | 名前  |    型    |  所有者
----------+-------+----------+----------
 public   | test  | テーブル | postgres
 public   | test2 | テーブル | postgres
 public   | test3 | テーブル | postgres
(3 行)
>createdb -T postgres -U postgres postgres2

>psql -U postgres -d postgres2
postgres2=# \dt
            リレーション一覧
 スキーマ | 名前  |    型    |  所有者
----------+-------+----------+----------
 public   | test  | テーブル | postgres
 public   | test2 | テーブル | postgres
 public   | test3 | テーブル | postgres
(3 行)

DBをコピーしたい：pg_dump

pg_dump {コピー元DB} > {dumpファイル}*4のコマンドを利用するとデータベースをバックアップするdumpファイルを作成することができます。
作成したdumpファイルをpsql {コピー先DB} < {dumpファイル}でリストアすることによってコピー先のDBにコピー元のDBをデータを再現することができます。
前述のcreatedb -Tと比較すると、dumpファイルがあれば同じサーバにコピー元のDBがなくてもコピーできる点で汎用的です。
また、-nや-tのオプションを指定すると一致するスキーマやテーブル単位でコピーすることもできます。

> pg_dump -U postgres postgres > postgres_dump

> ls -1 | grep postgres_dump
postgres_dump

> createdb -U postgres -T template0 postgres3

> psql -U postgres -d postgres3 < postgres_dump

> psql -U postgres -d postgres3
postgres3=# \dt
            リレーション一覧
 スキーマ | 名前  |    型    |  所有者
----------+-------+----------+----------
 public   | test  | テーブル | postgres
 public   | test2 | テーブル | postgres
 public   | test3 | テーブル | postgres
(3 行)

最後に

PostgreSQLでのテーブル・DBの閲覧・コピーに関してご紹介してみましたが、いかがでしたでしょうか。
効率的な作業のお役に立てば幸いです。

関連記事

◆TECH PLAY
techplay.jp

◆connpass
rakus.connpass.com

*1:https://www.postgresql.jp/document/9.3/html/app-psql.html

*2:https://www.postgresql.jp/document/9.3/html/sql-createtable.html

*3:https://www.postgresql.jp/document/9.2/html/app-createdb.html

*4:https://www.postgresql.jp/document/9.2/html/app-pgdump.html

ラクスの技術 / デザイン情報サイト
Copyright © RAKUS Co., Ltd. All rights reserved.