データベース緊急メンテナンスの実例（PostgreSQL編）

とある業者が納品した勤怠管理のWebアプリケーションで、ある従業員の、ある日付以降、勤怠のシフト情報が登録されていない事態が発覚した。
その後の調査で、データベースを直接修正するしかないとなったので、自分がどのように対処したか解説を交えながら紹介する。

## 本番サーバからデータベースのダンプを取得
本番サーバ（**CentOS 7.4**／**PostgreSQL 9.2**）から、データベースの全量をダンプする。

```
su - postgres
pg_dump DB0072 > /tmp/DB0072.txt
```

## Windows環境にリストア
このWebアプリケーションには開発サーバが無いので、WindowsマシンでPostgreSQLサービスを起動してリストアする。
なお、データベース名に英大文字が含まれている場合、ダブルクォートで囲まないと認識されないらしい。これには若干ハマった。

```
PS C:\> psql -U postgres
ユーザ postgres のパスワード:
psql (9.6.10)
"help" でヘルプを表示します.

postgres=# drop database "DB0072";
DROP DATABASE

postgres=# create database "DB0072";
CREATE DATABASE

PS C:\> psql -U postgres -d DB0072 -f DB0072.txt
```

## テーブルの解析
古参のフリーソフト [A5:SQL Mk-2](https://a5m2.mmatsubara.com/) を活用する。
今でも精力的にバージョンアップを継続されており、作者さんには敬服するばかりだ。

### データベースに接続
Windowsマシン（localhost）にリストアしたデータベースに接続する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/4f80df34-4ccf-dafe-3759-00dd0bf29344.png)

### テーブル定義書を作成
本データベースでは**ビュー**を多用している。ビューとは複数のテーブルを結合し、あたかも一つのテーブルであるかのように見せる**仮想的なテーブル**のことで`CREATE VIEW`で作成できる。
勤怠シフト情報を管理している**JavaBeans**からビュー名は特定できていたので、テーブル定義書を作成し、ビューから実テーブルを探るときの助けとする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/1d4caae8-7490-3d97-eb7c-a3813c3be85e.png)
Excelシートにテーブル定義書を出力する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/77e8043e-84e0-eeb5-50c9-329bd50e30c1.png)
従業員コードと勤怠シフト日付の**複合主キー**になっていた。

### CSV出力
勤怠シフト情報の実テーブルが特定できたので、CSVにエクスポートして開く。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/236222/f20d2445-28c5-895b-ff8c-cbb69cae2083.png)
該当する従業員について、ある日付以降のレコードが無いことを確認。

## レコードをテーブルに１件追加してみる
該当する従業員の勤怠シフト情報は前月と同じであることが分かっている。
なので、8月21日の勤怠シフト情報レコードを元に、9月21日～10月20日までのレコードを新規に追加すれば良さそうだ。（21日を基点としているのは、この会社の勤怠が20日締めだから）

この、**既に登録されているレコードの一部を変えて複製するSQL**というのは実際さまざまな場面で使われる。
やり方は色々あると思うが、今回は開発サーバが無いことを考慮し、`TEMPORARY TABLE`（**一時テーブル**）を介してステップを踏んで確認しながら確実に実行していくことにした。

では、コマンドラインからSQLを発行するためデータベースに接続しよう。

```
psql -U postgres
\c DB0072
```

### 複製
勤怠シフトの実テーブルから一時テーブルを作成する。
一時テーブルとはセッションの間にしか生存できないテーブルで、他のセッションからはアクセスできないという特徴を持つ。

```sql
CREATE TEMP TABLE temp_d1000t AS SELECT * FROM d1000t WHERE syaincd=72528 and shiftymd='20190821';
```

複合主キーをすべて検索条件に指定しているので、選択される１件のレコードを格納した一時テーブルが作成されることになる。

### 更新
一時テーブルの勤怠シフト日付を更新する。
登録日時と更新日時の項目（いずれも`TIMESTAMP`型）があったので、あわせて現在日時に修正しておいた。

```sql
UPDATE temp_d1000t set shiftymd='20190921', torokudt=now(), koushindt=now();
```

### 追加
一時テーブルの全レコード（今回は１件だが）を勤怠シフトの実テーブルに追加する。

```sql
INSERT INTO d1000t SELECT * FROM temp_d1000t;
```

これで、8月21日付の勤怠シフトレコードを9月21日付としてコピーしたことになる。
セッションからログアウトし（この時点で一時テーブルは削除される）、あらためて確認しておこう。

## 残レコードをテーブルにすべて追加
9月21日付のレコードは追加できたが、9月22日～10月20日までのレコードも追加しなければならない。
このような繰り返し処理は**PL/pgSQL**で書いた方がスマートではあるが、一度しか使わないのでシェルスクリプトでSQL文を生成し、流し込むことにした。

```bash:make_sql.sh
#!/bin/bash
echo "CREATE TEMP TABLE temp_d1000t AS SELECT * FROM d1000t WHERE syaincd=72528 and shiftymd='20190821';"
FROM_DATE='20190922'
TO_DATE='20191020'
for (( DATE=$FROM_DATE; $DATE <= $TO_DATE; DATE=`date -d "$DATE 1 day" '+%Y%m%d'` )); do
  echo "UPDATE temp_d1000t set shiftymd='$DATE', torokudt=now(), koushindt=now();"
  echo "INSERT INTO d1000t SELECT * FROM temp_d1000t;"
done
```

### SQL文の適用方法

```
./make_sql.sh | psql -U postgres -d DB0072
```

あとは、業務画面から勤怠シフト情報を確認し、解消されていればクローズ。