読者です 読者をやめる 読者になる 読者になる

notebook

都内でWEB系エンジニアやってます。

postgresqlでバックアップ、リストア

postgresql

redashをdockerで使ってみたときにpostgresをちょっと触ったのでその時のメモ

基本的な使い方からバックアップ、リストアまで

dockerの使い方とかに関しては割愛します

コンテナはpostgres9.3のイメージを使いました

FROM postgres:9.3

コンソール操作

とりあえずコンソールに入ってみる

sudo docker-compose exec postgres psql  -U postgres

docker経由じゃない場合

psql -U postgres
  • ユーザ一覧
postgres-# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
  • データベース一覧
postgres-# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)
  • テーブル一覧
postgres-# \dt
                   List of relations
 Schema |           Name            | Type  |  Owner
--------+---------------------------+-------+----------
 public | alert_subscriptions       | table | postgres
 public | alerts                    | table | postgres
 public | api_keys                  | table | postgres
 public | dashboards                | table | postgres
 public | data_source_groups        | table | postgres
 public | data_sources              | table | postgres
 public | events                    | table | postgres
 public | groups                    | table | postgres
 public | notification_destinations | table | postgres
 public | organizations             | table | postgres
 public | queries                   | table | postgres
 public | query_results             | table | postgres
 public | users                     | table | postgres
 public | visualizations            | table | postgres
 public | widgets                   | table | postgres
(15 rows)
  • テーブルの詳細を表示
postgres-# \d queries
                                         Table "public.queries"
        Column        |           Type           |                      Modifiers
----------------------+--------------------------+------------------------------------------------------
 id                   | integer                  | not null default nextval('queries_id_seq'::regclass)
 updated_at           | timestamp with time zone | not null
 created_at           | timestamp with time zone | not null
 org_id               | integer                  | not null
 data_source_id       | integer                  |
 latest_query_data_id | integer                  |
 name                 | character varying(255)   | not null
 description          | character varying(4096)  |
 query                | text                     | not null
 query_hash           | character varying(32)    | not null
 api_key              | character varying(40)    | not null
 user_id              | integer                  | not null
 last_modified_by_id  | integer                  |
 is_archived          | boolean                  | not null
 schedule             | character varying(10)    |
 options              | text                     | not null
Indexes:
    "queries_pkey" PRIMARY KEY, btree (id)
    "queries_data_source_id" btree (data_source_id)
    "queries_is_archived" btree (is_archived)
    "queries_last_modified_by_id" btree (last_modified_by_id)
    "queries_latest_query_data_id" btree (latest_query_data_id)
    "queries_org_id" btree (org_id)
    "queries_user_id" btree (user_id)
Foreign-key constraints:
    "queries_data_source_id_fkey" FOREIGN KEY (data_source_id) REFERENCES data_sources(id)
    "queries_last_modified_by_id_fkey" FOREIGN KEY (last_modified_by_id) REFERENCES users(id)
    "queries_latest_query_data_id_fkey" FOREIGN KEY (latest_query_data_id) REFERENCES query_results(id)
    "queries_org_id_fkey" FOREIGN KEY (org_id) REFERENCES organizations(id)
    "queries_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)
Referenced by:
    TABLE "alerts" CONSTRAINT "alerts_query_id_fkey" FOREIGN KEY (query_id) REFERENCES queries(id)
    TABLE "visualizations" CONSTRAINT "visualizations_query_id_fkey" FOREIGN KEY (query_id) REFERENCES queries(id)
  • 拡張テーブル形式モード

\xと打つと結果の出力形式が変わります

MySQLでセミコロンの代わりに\Gを付けた場合と同じ感じのようですね

postgres=# select * from dashboards;
 id |          updated_at           |          created_at           | org_id | slug | name | user_id |    layout     | dashboard_filters_enabled | is_archived
----+-------------------------------+-------------------------------+--------+------+------+---------+---------------+---------------------------+-------------
  1 | 2016-09-06 17:10:48.157634+00 | 2016-08-28 19:27:32.710222+00 |      1 | memo | memo |       1 | [[6, 7], [8]] | f                         | f
(1 row)

postgres=# \x

Expanded display is on.
postgres=# select * from dashboards;
-[ RECORD 1 ]-------------+------------------------------
id                        | 1
updated_at                | 2016-09-06 17:10:48.157634+00
created_at                | 2016-08-28 19:27:32.710222+00
org_id                    | 1
slug                      | memo
name                      | memo
user_id                   | 1
layout                    | [[6, 7], [8]]
dashboard_filters_enabled | f
is_archived               | f

backup

pg_dumpコマンドがMySQLでのmysqldumpと同等のようです

docker-compose exec postgres pg_dump -U postgres postgres > ./data/dump.sql

docker経由じゃない場合

pg_dump -U postgres postgres > ./data/dump.sql

postgresデータベースの内容をdumpしてます

restore

リストアはデータベース名を指定して実行する、もしくは-fオプションでファイルを指定する

psql -U postgres postgres < ./data/dump.sql
psql -U postgres -f /docker-entrypoint-initdb.d/dump.sql

コマンドラインからのSQL実行

-cオプションで実行できる

mysql -e と同様な感じですかね

psql -U postgres postgres -c "\dt"
                   List of relations
 Schema |           Name            | Type  |  Owner
--------+---------------------------+-------+----------
 public | alert_subscriptions       | table | postgres
 public | alerts                    | table | postgres
 public | api_keys                  | table | postgres
 public | dashboards                | table | postgres
 public | data_source_groups        | table | postgres
 public | data_sources              | table | postgres
 public | events                    | table | postgres
 public | groups                    | table | postgres
 public | notification_destinations | table | postgres
 public | organizations             | table | postgres
 public | queries                   | table | postgres
 public | query_results             | table | postgres
 public | users                     | table | postgres
 public | visualizations            | table | postgres
 public | widgets                   | table | postgres
(15 rows)

今回はここまで

広告を非表示にする