OSS Fan ~OSSでLinuxサーバ構築~

このエントリーをはてなブックマークに追加

PostgreSQLでデータベースの論理バックアップ取得

カテゴリ:OSSセットアップ | ソフトウェア:PostgreSQL | タグ:
最終更新日:2020/12/31 | 公開日:2013/11/22

目次

概要

 PostgreSQLでデータベースの論理バックアップを取得します。 データベースクラスタ全体のバックアップ(pg_dumpallコマンド使用)と同様に データベース単位のバックアップの場合もPostgreSQLインスタンスが起動した状態で PostgreSQLのコマンドを使用してバックアップを実行します。 注意点としては、ユーザ(ロール)など、データベースクラスタで保持されているデータは データベース単位でのバックアップでは取得されません。また、リカバリするときは データベース自体は作成されませんので、先に空のデータベースを作成した上で データベースのリカバリを行ってください。

構成

サーバ構成

OSバージョン

Red Hat Enterprise Linux 5.9 x86_64

パッケージ一覧

postgresql90-9.0.8-1PGDG.rhel6.x86_64.rpm
postgresql90-libs-9.0.8-1PGDG.rhel6.x86_64.rpm
postgresql90-server-9.0.8-1PGDG.rhel6.x86_64.rpm

手順

データベースの論理バックアップ

平文(SQL文)形式でのバックアップ方法

 平文形式でのバックアップで取得される情報はpg_dumpallの場合と同じSQL文の羅列です。 異なるバージョンのPostgreSQLへデータベースを移行する場合や、異なるDBMSに データベースを移行する場合に利用できます。

 PostgreSQLインスタンスが起動している状態で一般ユーザ(user1)で実行します。 バックアップファイル名は /bkup/20131117_pg_dump_plain_testdb1.sql とします。

 pg_hba.conf の設定によっては、途中でパスワードの入力を求められます。

-bash-3.2$ cd /bkup/
-bash-3.2$ pg_dump -U user1 -Fp -f 20131117_pg_dump_plain_testdb1.sql testdb1
パスワード: ←user1ユーザのパスワードを入力
-bash-3.2$ echo $?
0
-bash-3.2$ ls -l
合計 5764
-rw-r--r-- 1 postgres postgres    1347 11月 17 23:27 20131117_pg_dump_plain_testdb1.sql
-bash-3.2$ file 20131117_pg_dump_plain_testdb1.sql
20131117_pg_dump_plain_testdb1.sql: ASCII text
-bash-3.2$ cat 20131117_pg_dump_Fp_testdb1.sql
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
(中略)
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

-bash-3.2$

カスタム形式(PostgreSQL独自形式)でのバックアップ取得

 カスタム形式でバックアップします。 出力されるファイルの形式はPostgreSQL独自の形式なので、他のDBMS等にはリストアできません。 内容はテキストが中心なのでエディタ等で大部分を確認できますが、一部文字化けしました。

 PostgreSQLインスタンスが起動している状態で一般ユーザ(user1)で実行します。 バックアップファイル名は /bkup/20131117_pg_dump_plain_testdb1.dmp とします。

-bash-3.2$ pg_dump -U user1 -Fc -f 20131117_pg_dump_custom_testdb1.dmp testdb1
パスワード: ←user1ユーザのパスワードを入力
-bash-3.2$ echo $?
0
-bash-3.2$ ls -l
合計 5768
-rw-r--r-- 1 postgres postgres    2218 11月 17 23:37 20131117_pg_dump_custom_testdb1.dmp
-rw-r--r-- 1 postgres postgres    1347 11月 17 23:27 20131117_pg_dump_plain_testdb1.sql
-bash-3.2$ file 20131117_pg_dump_custom_testdb1.dmp
20131117_pg_dump_custom_testdb1.dmp: data
-bash-3.2$ cat 20131117_pg_dump_custom_testdb1.dmp
PGDMP
    1%
qtestdb19.0.139.0.13
・ENCODINENCODINGSET client_encoding = 'UTF8';
false・0
(中略)
CONSTRAINTEALTER TABLE ONLY tbl1
    ADD CONSTRAINT tbl1_pkey PRIMARY KEY (id);
8ALTER TABLE ONLY public.tbl1 DROP CONSTRAINT tbl1_pkey;
xublicuser1false142142・
 ム聹簀ゥ-bash-3.2$

tar形式でのバックアップ方法

 tar形式でバックアップします。 出力されるファイルはtar形式で、tarファイルを展開するとSQLファイル等が入っています。

 PostgreSQLインスタンスが起動している状態で一般ユーザ(user1)で実行します。 バックアップファイル名は /bkup/20131117_pg_dump_tar_testdb1.tar とします。

-bash-3.2$ pg_dump -U user1 -Ft -f 20131117_pg_dump_tar_testdb1.tar testdb1
パスワード: ←user1ユーザのパスワードを入力
-bash-3.2$ echo $?
0
-bash-3.2$ ls -l
合計 5776
-rw-r--r-- 1 postgres postgres    2218 11月 17 23:37 20131117_pg_dump_custom_testdb1.dmp
-rw-r--r-- 1 postgres postgres    1347 11月 17 23:27 20131117_pg_dump_plain_testdb1.sql
-rw-r--r-- 1 postgres postgres    7680 11月 17 23:46 20131117_pg_dump_tar_testdb1.tar
-bash-3.2$ file 20131117_pg_dump_tar_testdb1.tar
20131117_pg_dump_tar_testdb1.tar: tar archive
-bash-3.2$ tar tvf 20131117_pg_dump_tar_testdb1.tar
-rw------- 2048/1024      2157 2013-11-17 23:46:38 toc.dat
-rw------- 2048/1024         5 2013-11-17 23:46:38 1781.dat
-rw------- 2048/1024      2001 2013-11-17 23:46:38 restore.sql
-bash-3.2$

データベースの論理リストア

平文(SQL文)形式のバックアップからのリストア方法

 pg_dump コマンドで平文形式でバックアップしたデータを使ってリストアします。 pg_dump コマンドの平文形式でバックアップしたデータはデータベースを構成するためのSQL文なので、 psql でSQLを実行することでリストアします。 バックアップの時と同様にPostgreSQLインスタンスが起動した状態で実行します。 スーパーユーザの postgres ユーザで実行してください。 ※一般ユーザで実行すると途中でエラーになります。

 以下の手順では、既存のデータベースを一度dropした上で、 空のデータベースを再作成し、そこにリストアしています。

-bash-3.2$ cd /bkup/
-bash-3.2$ ls -l
合計 5776
-rw-r--r-- 1 postgres postgres    2218 11月 17 23:37 20131117_pg_dump_custom_testdb1.dmp
-rw-r--r-- 1 postgres postgres    1347 11月 17 23:27 20131117_pg_dump_plain_testdb1.sql
-rw-r--r-- 1 postgres postgres    7680 11月 17 23:46 20131117_pg_dump_tar_testdb1.tar
-bash-3.2$ dropdb testdb1
パスワード: ←postgresユーザのパスワードを入力
-bash-3.2$ echo $?
0
-bash-3.2$ createdb -O user1 testdb1
パスワード: ←postgresユーザのパスワードを入力
-bash-3.2$ psql -l
パスワード:  ←postgresユーザのパスワードを入力
                                        データベース一覧
   名前    |  所有者  | エンコーディング | 照合順序 | Ctype(変換演算子) |      アクセス権       
-----------+----------+------------------+----------+-------------------+-----------------------
 postgres  | postgres | UTF8             | C        | C                 | 
 template0 | postgres | UTF8             | C        | C                 | =c/postgres          +
           |          |                  |          |                   | postgres=CTc/postgres
 template1 | postgres | UTF8             | C        | C                 | postgres=CTc/postgres+
           |          |                  |          |                   | =c/postgres
 testdb1   | user1    | UTF8             | C        | C                 | 
(4 行)

-bash-3.2$ psql -f 20131117_pg_dump_plain_testdb1.sql testdb1
パスワード: ←postgresユーザのパスワードを入力
SET
SET
SET
SET
SET
SET
CREATE LANGUAGE
ALTER LANGUAGE
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
-bash-3.2$

カスタム形式(PostgreSQL独自形式)のバックアップからのリストア方法

 pg_dump コマンドでカスタム形式でバックアップしたデータを使ってリストアします。 カスタム形式でバックアップしたデータはほぼテキストですが、 SQL文の羅列ではないため psql で実行することはできません。 PostgreSQL専用のリストアコマンドである pg_restore を使用してリストアします。

 リストアのコマンドが違うだけで、リストアの流れは平文(SQL文)形式の時と同じです。

-bash-3.2$ dropdb testdb1
パスワード: ←postgresユーザのパスワードを入力
-bash-3.2$ createdb -O user1 testdb1
パスワード: ←postgresユーザのパスワードを入力
-bash-3.2$ pg_restore -d testdb1 20131117_pg_dump_custom_testdb1.dmp
パスワード: ←postgresユーザのパスワードを入力
-bash-3.2$ echo $?
0
-bash-3.2$

tar形式のバックアップからのリストア方法

 pg_dump コマンドでtar形式でバックアップしたデータを使ってリストアします。 カスタム形式のバックアップからリストアする時と同様に、 PostgreSQL専用のリストアコマンドである pg_restore を使用してリストアします。 リストアする際にtarファイルを展開する必要はありません。 tarファイルのまま使用します。

-bash-3.2$ dropdb testdb1
パスワード: ←postgresユーザのパスワードを入力
-bash-3.2$ createdb -O user1 testdb1
パスワード: ←postgresユーザのパスワードを入力
-bash-3.2$ pg_restore -d testdb1 20131117_pg_dump_tar_testdb1.tar
パスワード: ←postgresユーザのパスワードを入力
-bash-3.2$ echo $?
0
-bash-3.2$