kakiro-webカキローウェブ

システム開発情報とコンピューター書籍の紹介サイト

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

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」を対象にしています。

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
--

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

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。