株式会社ラクスの技術ブログ

全982件

2020/08/18

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

はじめにこんにちは、yk_itgです。これまでいくつか PostgreSQL の記事を作成しましたが、今回は知っていると便利だと思うテーブル・DBの閲覧・コピーに関するtipsをまとめてみました。私はテストを実施する時に結果を確認したり、データを用意する際によく使います。 PostgreSQL ユーザなら必須のテクニックを紹介していきますので、どうぞお役立てください！はじめにテーブルを閲覧するテーブルの情報を確認したい：\d テーブルの一覧を確認したい：\dt 実行結果を見やすくしたい：\x 実行結果をファイルに出力して確認したい：\o DBを閲覧する DBの一覧を確認したい：\l テーブルをコピーするテーブルの構造をコピーしたい：CREATE TABLE (LIKE) テーブルのレコードをコピーしたい：INSERT INTO SELECT DBをコピーする DBをコピーしたい：createdb -T DBをコピーしたい：pg_dump 最後に関連記事テーブルを閲覧するテーブルの情報を確認したい： \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-blog.rakus.co.jp tech-blog.rakus.co.jp tech-blog.rakus.co.jp エンジニア中途採用サイトラクスでは、エンジニア・デザイナーの中途採用を積極的に行っております！ご興味ありましたら是非ご確認をお願いします。 https://career-recruit.rakus.co.jp/career_engineer/ カジュアル面談お申込みフォームどの職種に応募すれば良いかわからないという方は、カジュアル面談も随時行っております。以下フォームよりお申込みください。 rakus.hubspotpagebuilder.com ラクスDevelopers登録フォーム https://career-recruit.rakus.co.jp/career_engineer/form_rakusdev/ イベント情報会社の雰囲気を知りたい方は、毎週開催しているイベントにご参加ください！ ◆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

株式会社ラクス の技術ブログ

コンテンツ

株式会社ラクスの技術ブログ