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;

特定のデータベースのバックアップを作成

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

peer認証を使用する場合

# su - postgres
$ pg_dump --create --clean --if-exists --inserts db01 > /xxx/postgresql/dump/db01.sql

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

# pg_dump -h 127.0.0.1 -U postgres --create --clean --if-exists --inserts db01 > /xxx/postgresql/dump/db01.sql
  • 特定のデータベースのバックアップの作成には、「pg_dump」コマンドを使用します。

  • 全てのデータベースのバックアップとリストアのページで示したように、「pg_dumpall」コマンドでバックアップを作成する際にパスワード認証を使用すると、パスワードの入力が最初の接続時と、バックアップ対象の各データベースへの接続時に必要となりますが、「pg_dump」コマンドで特定のデータベースのバックアップを作成する際は、パスワードの入力は最初の接続時のみになります。

    そのためバックアップを作成時の認証方式は、peer認証、パスワード認証のどちらでも変わりはありません。

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

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

  • 「--create」オプションを指定すると、データベースのCREATE文が出力されるようになります。

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

    「--if-exists」オプションも指定することで、DROP文には「IF EXISTS」が付くようになります。

  • 「--inserts」オプションを指定すると、データのバックアップはCOPY文ではなく、INSERT文を使用して行われるようになります。

  • コマンドの引数に、バックアップを作成するデータベースの名前を指定します。

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

    尚、バックアップを作成するデータベースの名前は、コマンドの引数ではなく、「-d」オプションで指定することもできます。

  • リダイレクトを使用して、バックアップの内容をファイルに出力するようにします。

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

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

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

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


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


--
-- 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」への接続後にスキーマ「public」のCREATE文が出力されていますが、全てのデータベースのバックアップとリストアのページで示した、「pg_dumpall」でのバックアップの作成時には出力されないものになっています。

特定のデータベースのバックアップからのリストア

上記で取得した特定のデータベースのバックアップ「db01.sql」からリストアを行うには、以下のようなコマンドを実行します。

※バックアップにはデータベース接続ユーザーのCREATE文は含まれていませんので、リストア前に上記の動作確認用のデータベース接続ユーザーに示した「user01」、「user02」が作成されている必要があります。

peer認証を使用する場合

# su - postgres
$ psql -f /xxx/postgresql/dump/db01.sql postgres

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

# psql -h 127.0.0.1 -U postgres -f /xxx/postgresql/dump/db01.sql postgres

実行結果は、以下のようになります。

SET
SET
SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
データベース "db01" にユーザ"postgres"として接続しました。
SET
SET
SET
SET
SET
SET
psql:/xxx/postgresql/dump/db01.sql:35: ERROR:  スキーマ"public"はすでに存在します
ALTER SCHEMA
COMMENT
CREATE EXTENSION
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 setval
--------
      2
(1 行)

ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
REVOKE
REVOKE
GRANT
GRANT
  • 平文のSQLで出力されたバックアップからのリストアには、「psql」コマンドを使用します。

  • 全てのデータベースのバックアップからのリストアでは、バックアップにデータベース接続ユーザー「postgres」が含まれるため、リストア時にパスワード認証を使用すると、パスワードが異なる場合に影響がありましたが、特定のデータベースのバックアップにはデータベース接続ユーザーは含まれないため、バックアップからのリストア時の認証方式は、peer認証、パスワード認証のどちらでも変わりはありません。

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

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

  • 「-f」オプションで、リストアを行うバックアップファイルを指定します。

    ここでは、ディレクトリ「/xxx/postgresql/dump/」にある、ファイル「db01.sql」を指定しています。

  • 最後に「postgres」と指定し、リストア時に接続するデータベースを「postgres」にしています。

    「postgres」は、PostgreSQLでデフォルトで用意されているデータベースになります。

    尚、リストア時に接続するデータベースの名前は、コマンドの引数ではなく、「-d」オプションで指定することもできます。

  • 「psql:/xxx/postgresql/dump/db01.sql:35: ERROR: スキーマ"public"はすでに存在します」と表示されているエラーは、データベース「db01」を作成した時点でスキーマ「public」もデフォルトで作成されるのですが、バックアップの内容にスキーマ「public」のCREATE文が含まれているため発生しているものになります。

PostgreSQL 特定のデータベースのバックアップとリストアについては、以上です。

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

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

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

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

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

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