カテゴリー: PostgreSQL

本番DBを開発用にマスキング

本番データを、開発環境に落とした後に個人情報部分を書き換えるクエリ。あくまでも開発環境用なので、データマスキングツールを使うまでもないので、簡単なクエリで書き換えるというだけ。以下はPostgreSQLでのサンプル。

１．固定名＋連番に書き換え

名前（name）を固定値「試験 太郎」＋キー番号にする場合。数値型のキーのcustomer_idを６文字以内（先頭から）の文字列として後ろにつけている。

UPDATE customer SET
　name = '試験 太郎' || CAST(customer_id AS character varying(6))
 WHERE name <> '';

「試験　太郎1」・・・「試験　太郎999999」のような感じ。

２．文字列の一部を書き換え

名前（name）の１文字目、３文字目、５文字目を「〇」に置き換える場合。

UPDATE customer SET
 name =  '〇' || SUBSTR(name, 2, 1) || '〇' || SUBSTR(name, 4, 1) || '〇' || SUBSTR(name, 6)
 WHERE  name <> '';

「山本文左衛門」が「〇本〇左〇門」となる。短い「孫文」のような場合、「〇文〇〇」となるので、まあテストデータなので特に気にしない。

３．電話番号

ハイフンで連結されている電話番号（tel）の市外局番だけはそのままで、残りは「9999-9999」として、「03-9999-9999」というような感じにする。

UPDATE customer SET
 tel =  CAST(SPLIT_PART(tel ,'-', 1) AS character varying(5)) || '-9999-9999'
 WHERE tel <> '';

ハイフンの無い携帯番号（mobile_no）の先頭６文字はそのままで、後ろ５文字を「99999」とする例で、「08012399999」という感じになる。

UPDATE customer SET
 mobile_no = SUBSTR(mobile_no , 1, 6)  || '99999'
 WHERE mobile_no <> '';

４．メールアドレス

ドメインはそのままで、アカウント部分を固定値「test」＋キー番号とする場合。

UPDATE customer SET
 mail =  'test' || CAST(id_customer AS character varying(10)) || '@' || CAST(SPLIT_PART(mail ,'@', 2) AS character varying(64))
 WHERE mail <> '';

「yamada_tarou@hogehoge.com」がid_customer=869の場合、「test869@hogehoge.com」となる。

PostgreSQL更新後にポートがデフォルトに

CentOS７にyumで入れていたPostgreSQLについて、更新したところ、ポートがデフォルトに戻ってしまった。

サービス起動ファイルが更新されてしまったため、再度、修正した。

# systemctl stop postgresql
# systemctl disable postgresql 
# vi /usr/lib/systemd/system/postgresql.service  ※Environment=PGPORT=5432 を変更して、サービス再登録
# systemctl enable postgresql 
# systemctl start postgresql 
# systemctl status postgresql  ※ポート変更されていることを確認

しかし、そもそも起動ファイルが、何で定義ファイルを無視するようになってるんだ・・・。

CentOS7に最新のPostgreSQL

テスト環境のCentOS７に入れてあるPostgreSQLが古いので、バージョンアップして最新に。その時の、作業メモ。

１．インストール

yumのままだとPostgreSQL9.2になっているので、最新をRPMから入れる。

(PostgreSQL公式)PostgreSQL: Linux downloads (Red Hat family)

このページで、１．バージョン、２．プラットフォーム、３．アーキテクチャーを選べば、４に必要なコマンドが出てくるので、それを実行。

$ yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ yum install -y postgresql13-server
$ /usr/pgsql-13/bin/postgresql-13-setup initdb
$ systemctl enable postgresql-13
$ systemctl start postgresql-13

事前に、前のバージョンは、停止して、自動起動も止める。

$ systemctl stop postgresql
$ systemctl disable postgresql

環境設定等は、以下が参考になる。

(Qiita)PostgreSQL スーパーユーザの環境変数設定

２．DB復元

バックアップファイルから、DBを復元する。

１）postgresユーザーになって、psqlコマンドを起動し、データベースとロールを作成。

$ su - postgres
$ psql
psql (13.1)
Type "help" for help.
postgres=# CREATE DATABASE xxxxxx;
CREATE DATABASE
postgres=# CREATE ROLE xxxxxx SUPERUSER LOGIN PASSWORD 'xxxxx';
CREATE ROLE
postgres=# \q

２）DBの復元

$ pg_restore -d xxxxx バックアップファイル

３．接続設定

新しいバージョンの定義ファイルを修正して、従来と同じ方法で接続できるようにする。

（定義ファイルの場所）

/var/lib/pgsql/13/data/

開発環境からの接続を許可するように、以下ファイルを修正。

$ diff pg_hba.conf.org pg_hba.conf
86a87
host all all 111.111.111.111/32 scram-sha-256    ※111.111.111.111：開発環境のIPアドレス

$ diff postgresql.conf.org postgresql.conf
62a63
listen_addresses = '*'

ファイアウォールでPostgreSQLのポートが閉じられているなら、開く。

４．ポート変更する場合

セキュリティー上、あるいは複数バージョンを同時に立ち上げる等でポート変更する場合。

１）設定ファイルのポートを変更して再起動

# diff postgresql.conf.org postgresql.conf
64c64
< #port = 5432                          # (change requires restart)
---
> port = 9999                           # (change requires restart) 

# systemctl restart postgresql-13

２）ファイアウォールのPostgreSQLのポートを変更、または追加

両方のバージョンを並行運用することも考えて、１３は別サービスとして登録。

サービス定義ファイルの場所に移動して、サービス定義ファイルをコピーして、ポートを修正する。

# cd /usr/lib/firewalld/services
# cp postgresql.xml postgresql-13.xml
# vi postgresql-13.xml   ※portの設定を変更
# firewall-cmd --permanent --add-service=postgresql-13
# firewall-cmd --reload
# firewall-cmd --list-all ※確認

※ちなみに、yumで入れたPostgreSQL9.2もポート番号を変えたい場合、サービス起動ファイルに、ポートが書かれているのでこちらを変更する必要がある。

# vi /usr/lib/systemd/system/postgresql.service
    ※Environment=PGPORT=5432 を変更して、サービス再登録
# systemctl stop postgresql
# systemctl disable postgresql
# systemctl enable postgresql
# systemctl start postgresql
# systemctl status postgresql  ※ポート変更されていることを確認