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

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

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

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

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

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

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

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

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=> CREATE TABLE tbl12 (num12 SERIAL NOT NULL, txt12 VARCHAR(30), date12 TIMESTAMP WITHOUT TIME ZONE, CONSTRAINT pk_tbl12 PRIMARY KEY (num12));

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

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

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

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」を指定するようにします。

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

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

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

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

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

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