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)
今回はここまで
