PostgreSQL データ定義(データベース定義、テーブル定義)のみのバックアップ、データのみのバックアップ

カキローウェブのTwitterはこちら

PostgreSQLでデータ定義(データベース定義、テーブル定義)のみのバックアップを作成する方法と、データのみのバックアップを作成する方法について、以下に示します。

※ここで説明に使用しているPostgreSQLは、当サイトのCentOS7 64bitのyumリポジトリにPostgreSQLを追加し、最新バージョンをインストールのページを元にインストールした、バージョン9.4になります。

※全てのデータベースのバックアップとリストアについては、当サイトのPostgreSQL 全てのデータベースのバックアップとリストアのページをご覧ください。

※特定のデータベースのバックアップとリストアについては、当サイトのPostgreSQL 特定のデータベースのバックアップとリストアのページをご覧ください。

※データベース接続ユーザー(ロール)のバックアップとリストアについては、当サイトのPostgreSQL データベース接続ユーザー(ロール)のバックアップとリストアのページをご覧ください。

動作確認用のデータベース接続ユーザーの作成

ここでは、データベースのバックアップとリストアの動作確認用に、以下のようなデータベース接続ユーザーを使用します。

データベース接続ユーザー「user01」を作成

データベース接続ユーザー「user01」を以下のように作成します。

postgres=# CREATE ROLE user01 LOGIN PASSWORD 'pass01',

データベース接続ユーザー「user02」を作成

データベース接続ユーザー「user02」を以下のように作成します。

postgres=# CREATE ROLE user02 LOGIN PASSWORD 'pass02',

動作確認用のデータベース「db01」の作成

ここでは、データベースのバックアップとリストアの動作確認用に、以下のようなデータベース「db01」を使用します。

データベース「db01」を作成

データベース「db01」を以下のように作成します。

postgres=# CREATE DATABASE db01 OWNER = user01 TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C',

以降のデータベース「db01」へのテーブルの作成、データの登録は、データベース「db01」に所有者「user01」で接続した状態で行うものとします。

データベース「db01」にテーブル「tbl11」を作成

データベース「db01」にテーブル「tbl11」を以下のように作成します。

db01=>, CREATE TABLE tbl11 (id CHAR(5) NOT NULL, num11 INTEGER, txt11 VARCHAR(50), date11 TIMESTAMP WITHOUT TIME ZONE, CONSTRAINT pk_tbl11 PRIMARY KEY (id)),

テーブル「tbl11」にデータを登録

テーブル「tbl11」に以下のようなデータを登録します。

db01=>, INSERT INTO tbl11 (id, num11, txt11, date11) VALUES ('00001', 1, '①あ〜あ001', '2015-01-02 03:04:05'),

db01=>, INSERT INTO tbl11 (id, num11, txt11, date11) VALUES ('00002', 2, '②い〜い002', '2015-11-12 13:14:15'),

データベース「db01」にテーブル「tbl12」を作成

データベース「db01」にテーブル「tbl12」を以下のように作成します。

db01=>, CREATE TABLE tbl12 (num12 SERIAL NOT NULL, txt12 VARCHAR(30), date12 TIMESTAMP WITHOUT TIME ZONE, CONSTRAINT pk_tbl12 PRIMARY KEY (num12)),

テーブル「tbl12」にデータを登録

テーブル「tbl12」に以下のようなデータを登録します。

db01=>, INSERT INTO tbl12 (txt12, date12) VALUES ('aaa', '2015-01-01 00:00:00'),

db01=>, INSERT INTO tbl12 (txt12, date12) VALUES ('bbb', '2015-01-01 01:00:00'),

動作確認用のデータベース接続ユーザーに権限を付与

ここでは、データベースのバックアップとリストアの動作確認用に、以下のような権限を付与されたデータベース接続ユーザーを使用します。

データベース接続ユーザー「user02」に権限を付与

データベース接続ユーザー「user02」に以下のような権限を付与します。

データベース「db01」に所有者「user01」で接続した状態で行うものとします。

db01=>, GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user02,

データ定義(データベース定義、テーブル定義)のみのバックアップを作成

データ定義(データベース定義、テーブル定義)のみのバックアップを作成するには、以下のようなコマンドを実行します。

※上記で作成したデータベース「db01」を対象にしています。

peer認証を使用する場合

# su - postgres

$ pg_dump --create --clean --if-exists --schema-only db01 >, /xxx/postgresql/dump/db01-schema.sql

パスワード認証を使用する場合

# pg_dump -h 127.0.0.1 -U postgres --create --clean --if-exists --schema-only db01 >, /xxx/postgresql/dump/db01-schema.sql

データベースのバックアップの作成には、「pg_dump」コマンドを使用します。
バックアップを作成時の認証方式は、peer認証、パスワード認証のどちらでもバックアップの内容に変わりはありません。

peer認証を使用する場合、「su」コマンドでユーザーを「postgres」に切り替えてから、「pg_dump」コマンドを実行します。

パスワード認証が設定ファイル「pg_hba.conf」で「host」接続時に行われるように設定されている場合、「-h」オプションで接続先のホストを指定し、「-U」オプションで接続ユーザーを指定します。
「–create」オプションを指定すると、データベースのCREATE文が出力されるようになります。

「–clean」オプションと組み合わせると、データベースのCREATE文の前に、DROP文が出力されるようになります。

「–if-exists」オプションも指定することで、DROP文には「IF EXISTS」が付くようになります。
「–schema-only」オプションを指定すると、データ定義(データベース定義、テーブル定義)のバックアップのみが出力されるようになります。
コマンドの引数に、バックアップを作成するデータベースの名前を指定します。

ここではデータベース「db01」のバックアップを作成するようにしています。

尚、バックアップを作成するデータベースの名前は、コマンドの引数ではなく、「-d」オプションで指定することもできます。
リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。

ここでは、ディレクトリ「/xxx/postgresql/dump/」に、ファイル名「db01-schema.sql」で出力するようにしています。

peer認証を使用する場合、出力先のディレクトリには、ユーザー「postgres」による書き込み権限が必要となります。

※「–schema-only」オプションは、全てのデータベースのバックアップとリストアのページで使用している「pg_dumpall」コマンドでも同様に指定することができます。

作成されたバックアップの内容は、以下のようになります。

-- -- PostgreSQL database dump -- SET statement_timeout = 0, SET lock_timeout = 0, SET client_encoding = 'UTF8', SET standard_conforming_strings = on, SET check_function_bodies = false, SET client_min_messages = warning, DROP DATABASE IF EXISTS db01, -- -- Name: db01, Type: DATABASE, Schema: -, Owner: user01 -- CREATE DATABASE db01 WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'C' LC_CTYPE = 'C', ALTER DATABASE db01 OWNER TO user01, connect db01 SET statement_timeout = 0, SET lock_timeout = 0, SET client_encoding = 'UTF8', SET standard_conforming_strings = on, SET check_function_bodies = false, SET client_min_messages = warning, -- -- Name: public, Type: SCHEMA, Schema: -, Owner: postgres -- CREATE SCHEMA public, ALTER SCHEMA public OWNER TO postgres, -- -- Name: SCHEMA public, Type: COMMENT, Schema: -, Owner: postgres -- COMMENT ON SCHEMA public IS 'standard public schema', -- -- Name: plpgsql, Type: EXTENSION, Schema: -, Owner: -- CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog, -- -- Name: EXTENSION plpgsql, Type: COMMENT, Schema: -, Owner: -- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language', SET search_path = public, pg_catalog, SET default_tablespace = '', SET default_with_oids = false, -- -- Name: tbl11, Type: TABLE, Schema: public, Owner: user01, Tablespace: -- CREATE TABLE tbl11 ( id character(5) NOT NULL, num11 integer, txt11 character varying(50), date11 timestamp without time zone ), ALTER TABLE tbl11 OWNER TO user01, -- -- Name: tbl12, Type: TABLE, Schema: public, Owner: user01, Tablespace: -- CREATE TABLE tbl12 ( num12 integer NOT NULL, txt12 character varying(30), date12 timestamp without time zone ), ALTER TABLE tbl12 OWNER TO user01, -- -- Name: tbl12_num12_seq, Type: SEQUENCE, Schema: public, Owner: user01 -- CREATE SEQUENCE tbl12_num12_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1, ALTER TABLE tbl12_num12_seq OWNER TO user01, -- -- Name: tbl12_num12_seq, Type: SEQUENCE OWNED BY, Schema: public, Owner: user01 -- ALTER SEQUENCE tbl12_num12_seq OWNED BY tbl12.num12, -- -- Name: num12, Type: DEFAULT, Schema: public, Owner: user01 -- ALTER TABLE ONLY tbl12 ALTER COLUMN num12 SET DEFAULT nextval('tbl12_num12_seq'::regclass), -- -- Name: pk_tbl11, Type: CONSTRAINT, Schema: public, Owner: user01, Tablespace: -- ALTER TABLE ONLY tbl11 ADD CONSTRAINT pk_tbl11 PRIMARY KEY (id), -- -- Name: pk_tbl12, Type: CONSTRAINT, Schema: public, Owner: user01, Tablespace: -- ALTER TABLE ONLY tbl12 ADD CONSTRAINT pk_tbl12 PRIMARY KEY (num12), -- -- Name: public, Type: ACL, Schema: -, Owner: postgres -- REVOKE ALL ON SCHEMA public FROM PUBLIC, REVOKE ALL ON SCHEMA public FROM postgres, GRANT ALL ON SCHEMA public TO postgres, GRANT ALL ON SCHEMA public TO PUBLIC, -- -- Name: tbl11, Type: ACL, Schema: public, Owner: user01 -- REVOKE ALL ON TABLE tbl11 FROM PUBLIC, REVOKE ALL ON TABLE tbl11 FROM user01, GRANT ALL ON TABLE tbl11 TO user01, GRANT ALL ON TABLE tbl11 TO user02, -- -- Name: tbl12, Type: ACL, Schema: public, Owner: user01 -- REVOKE ALL ON TABLE tbl12 FROM PUBLIC, REVOKE ALL ON TABLE tbl12 FROM user01, GRANT ALL ON TABLE tbl12 TO user01, GRANT ALL ON TABLE tbl12 TO user02, -- -- PostgreSQL database dump complete --

特定のデータベースのバックアップとリストアのページで取得したデータベース「db01」のバックアップから、テーブルにデータを登録するINSERT文と、シーケンスの値を設定するsetvalの呼び出しが除外されたものとなっています。
このバックアップからのリストアのやり方は、特定のデータベースのバックアップとリストアのページに記載しているものと同様になります。

データのみのバックアップを作成

データのみのバックアップを作成するには、以下のようなコマンドを実行します。

※上記で作成したデータベース「db01」を対象にしています。

peer認証を使用する場合

# su - postgres

$ pg_dump --inserts --data-only db01 >, /xxx/postgresql/dump/db01-data.sql

パスワード認証を使用する場合

# pg_dump -h 127.0.0.1 -U postgres --inserts --data-only db01 >, /xxx/postgresql/dump/db01-data.sql

データベースのバックアップの作成には、「pg_dump」コマンドを使用します。
バックアップを作成時の認証方式は、peer認証、パスワード認証のどちらでもバックアップの内容に変わりはありません。

peer認証を使用する場合、「su」コマンドでユーザーを「postgres」に切り替えてから、「pg_dump」コマンドを実行します。

パスワード認証が設定ファイル「pg_hba.conf」で「host」接続時に行われるように設定されている場合、「-h」オプションで接続先のホストを指定し、「-U」オプションで接続ユーザーを指定します。
「–data-only」オプションを指定すると、データのバックアップのみが出力されるようになります。
コマンドの引数に、バックアップを作成するデータベースの名前を指定します。

ここではデータベース「db01」のバックアップを作成するようにしています。

尚、バックアップを作成するデータベースの名前は、コマンドの引数ではなく、「-d」オプションで指定することもできます。
リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。

ここでは、ディレクトリ「/xxx/postgresql/dump/」に、ファイル名「db01-data.sql」で出力するようにしています。

peer認証を使用する場合、出力先のディレクトリには、ユーザー「postgres」による書き込み権限が必要となります。

※「–data-only」オプションは、全てのデータベースのバックアップとリストアのページで使用している「pg_dumpall」コマンドでも同様に指定することができます。

作成されたバックアップの内容は、以下のようになります。

-- -- PostgreSQL database dump -- SET statement_timeout = 0, SET lock_timeout = 0, SET client_encoding = 'UTF8', SET standard_conforming_strings = on, SET check_function_bodies = false, SET client_min_messages = warning, SET search_path = public, pg_catalog, -- -- Data for Name: tbl11, Type: TABLE DATA, Schema: public, Owner: user01 -- INSERT INTO tbl11 VALUES ('00001', 1, '①あ〜あ001', '2015-01-02 03:04:05'), INSERT INTO tbl11 VALUES ('00002', 2, '②い〜い002', '2015-11-12 13:14:15'), -- -- Data for Name: tbl12, Type: TABLE DATA, Schema: public, Owner: user01 -- INSERT INTO tbl12 VALUES (1, 'aaa', '2015-01-01 00:00:00'), INSERT INTO tbl12 VALUES (2, 'bbb', '2015-01-01 01:00:00'), -- -- Name: tbl12_num12_seq, Type: SEQUENCE SET, Schema: public, Owner: user01 -- SELECT pg_catalog.setval('tbl12_num12_seq', 2, true), -- -- PostgreSQL database dump complete --

特定のデータベースのバックアップとリストアのページで取得したデータベース「db01」のバックアップから、テーブルにデータを登録するINSERT文と、シーケンスの値を設定するsetvalの呼び出しのみを取り出したものとなっています。
このバックアップからのリストアのやり方は、特定のデータベースのバックアップとリストアのページに記載しているものと同様ですが、バックアップの内容にデータベース「db01」へのconnectコマンドは含まれていませんので、psqlコマンドで接続するデータベースに「db01」を指定するようにします。

PostgreSQL データ定義(データベース定義、テーブル定義)のみのバックアップ、データのみのバックアップについては、以上です。