【PSQL (v.12.4)】PostgreSQLで論理バックアップからリストアまで(shell command使います)

はじめに

今回毎朝8時にデータベースのバックアップを作成する、shellコマンドを作成していきます。
バックアップの拡張子は .sql として 保存時には .tar.gz に圧縮します。

・バックアップの保存先は /var/backup とします
・shellコマンドは var/lib/pgsql/backup_sh/ に置きます

《環境》
Linux
PostgreSQL 12.4

《事前準備》
・バックアップする対象のDBがある（リストア先はこのnote内で作ります）
・そのデータベースにデータが入っている状態

最終的な構造はこうなります（僕のやり方の場合）

/var/backup
|-- hoge-20201010-080001.sql
|-- hoge-20201011-080001.sql
`-- hoge-20201012-080001.sql

//ここはcrontabで時間をしてして実行させる
/var/lib/pgsql/backup_sh
`-- pg_backup.sh

やり方

まずディレクトリの作成（既にある人はここをとばしてください）

// ここにバックアップファイルが蓄積されていきます
mkdir /var/backup

次にpostgresユーザーでpostgresに入ります。

// postgresに入る
su postgres

// ディレクトリ作成
cd /var/lib/pgsql
mkdir backup_sh

// shellコマンドを作成
vi pg_backup.sh

pg_backup.sh内

#!/bin/bash

# 日付
DATE=`date '+%Y%m%d-%H%M%S'`

# ファイルを保存するPATH
SAVEPATH='/var/backup/'

# 保存するファイルに名前を付ける
PREFIX='hoge-'

# 拡張子の指定
EXT='.sql'

# アクセスに必要な情報
USER='hogehoge'
DBNAME='test'

# パスワード入力が必要な際はこれを記載
export PGPASSWORD=hogehoge

# バックアップ実行コマンド
cd /var/lib/pgsql/backup
/usr/bin/pg_dump -h ホスト名 -U $USER -p 5432 $DBNAME > $PREFIX$DATE$EXT

# tarに圧縮
tar --remove-files -P -zcvf $SAVEPATH$PREFIX$DATE$EXT.tar.gz $PREFIX$DATE$EXT

上記の場合だと実行コマンドは

/usr/bin/pg_dump -h ホスト名 -U hogehoge -p 5432 test > hoge-202010010801.sql

になります。

シェルスクリプトが実行できるように実行権限付与

このままだと単体で実行できないので

chmod u+x /var/lib/pgsql/backup_sh/pg_backup.sh

次は定期的に実行されるように設定していきます。

crontab -e

crontab内

// 毎朝8時に実行する場合
00  8  *  *  * /var/backup_sh/pg_backup.sh

// 5分おきに実行する場合
*/5 *  *  *  * /var/backup_sh/pg_backup.sh

これでバックアップの準備は完了です。

DBを作成して、リストア

// PostgreSQLに入る
psql -U postgres

// DB作成
CREATE DATABASE db名 OWNER role名;

// 出る
\q

リストアをしていく

先ほど作成したDB名を dbtest とした場合

cd /var/backup

ls -al
postgres postgres  690862 Sep 16 08:00  hoge-20201016-080001.sql.tar.gz
// こんな感じでファイルサイズもあり、バックアップが無事に取れていることを確認。


// tar.gz 解凍
tar -zxvf file名

ls-al
postgres postgres 1041479 Oct 16 08:00 hoge-20201016-080001.sql
// ファイルサイズが大きくなっているのを確認。無事に解凍できている

// リストア
psql リストア先のdb名 < file名

// リストア（今回の場合）
psql dbtest < hoge-20201016-080001.sql

無事に実行されれば完了。