PostgreSQLでデータベースの論理バックアップ取得
目次
概要
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$