Marlock Homes Diary

備忘録。忘れないように書きます。

PostgreSQLのトランザクションID周回問題を強制的に発生させる

こんにちは!
PostgreSQLトランザクションID(XID)の周回により、
以前は見ていた古いデータが見えなくなる事象(XID周回問題)を強制的に発生させたいと思います。

通常は自動Vacuumが古いXIDの整理を自動実行している、かつ
XID周回問題が発生する前に以下のようなメッセージが出力され、
XID周回問題(データが見えなくなる)は発生しません。

XID周回問題の発生まで、残り1,000万トランザクションで以下の警告メッセージが出力されます。

WARNING:  database "mydb" must be vacuumed within 177009986 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

残り100万トランザクションでエラーになります。(XID周回問題の防止)
エラーが発生した場合には、メッセージのHINTに出力されている通り、
シングルユーザモードでVACUUM(FREEZE)を実行し、復旧させる必要があます。

ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
HINT:  Stop the postmaster and vacuum that database in single-user mode.

参考にした情報
https://www.postgresql.jp/document/9.6/html/routine-vacuuming.html
https://www.slideshare.net/iakio/jpug-ezo20110809

今回はXID周回が発生した場合の動作を確認するために、XID周回を強制的に発生させました。
※ データが見えなくなりますので、壊れてよいデータベースクラスタを用意して実行しています。

0. 環境情報

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
$ psql -c "SELECT version()"
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

1. テスト用テーブル作成

$ psql
=# CREATE TABLE test(id int);
=# INSERT INTO test VALUES (1);
=# INSERT INTO test VALUES (2);
=# INSERT INTO test VALUES (3);
=# SELECT xmin,xmax,id FROM test;
 xmin | xmax | id
------+------+----
  556 |    0 |  1
  557 |    0 |  2
  558 |    0 |  3
(3 rows)

2. データベース停止

$ pg_ctl stop
waiting for server to shut down.... done
server stopped

3. xidを2の31乗進める

$ pg_resetwal -x 0x80000000 data
Write-ahead log reset

4. シングルユーザモードで起動

$ postgres --single postgres
PostgreSQL stand-alone backend 10.0

5. frozenxidを書き換える

=# UPDATE pg_database SET datfrozenxid = '2147483648' -- 2の31乗
=# UPDATE pg_class SET relfrozenxid= '2147483648' -- 2の31乗
=# SELECT datname,datfrozenxid from pg_database; -- datfrozenxidが"2147483648"
=# SELECT relname,relfrozenxid from pg_class; -- relfrozenxidが"2147483648"
Ctrl + D

6. データベース起動

$ pg_ctl start

7. XIDを消費

for i in `seq 0 545`
do
  psql -c "SELECT txid_current();"
done

7. 確認

$ psql
=# SELECT txid_current();
 txid_current
--------------
   2147484201
(1 row)


=# SELECT xmin,xmax,id FROM test;
 xmin | xmax | id
------+------+----
  556 |    0 |  1
  557 |    0 |  2
  558 |    0 |  3
(3 rows)

※ 上記の2SQLを繰り返すると、xmin=556のデータから見えなくなる
 xmin | xmax | id
------+------+----
(0 rows)

6. VACUUM FREEZEの実行

=# VACUUM FREEZE;
=# SELECT xmin,xmax,id FROM test;
ERROR:  catalog is missing 1 attribute(s) for relid 16384
LINE 1: SELECT xmin,xmax,id FROM test;
=# SELECT 
    * 
  FROM 
    pg_attribute
  WHERE 
    attrelid = ( SELECT oid FROM pg_class WHERE relname = 'test' );
(0 rows)

システムカタログpg_attributeのデータが見えなくなってしまったようです。
何らかの特殊要因(システムカタログを書き換えたなど(ないと思いますが))で、周回問題が発生しうるか否かを確認する際には、
frozenidと該当テーブルのxminの値を比較してみるとよいかもしれません。

以上です。

PostgreSQLのキャッシュ状態の確認方法

PostgreSQLのキャッシュ(shard_buffers)に乗っているオブジェクトは、
contribのpg_buffercacheを利用して確認することが可能です。(とても便利)


0. 確認した環境の情報

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
# uname -r
3.10.0-514.el7.x86_64
$ /usr/bin/python --version
Python 2.7.5
-- 後述のpgpagecacheで利用
$ psql
postgres=# select version();
                                                 version

---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
(1 row)

1. pg_buffercacheによる確認例

postgres=# CREATE EXTENSION pg_buffercache ;
CREATE EXTENSION
postgres=# SELECT c.relname, count(*) AS buffers
             FROM pg_buffercache b INNER JOIN pg_class c
             ON b.relfilenode = pg_relation_filenode(c.oid) AND
                b.reldatabase IN (0, (SELECT oid FROM pg_database
                                      WHERE datname = current_database()))
             GROUP BY c.relname
             ORDER BY 2 DESC
             LIMIT 10;
             relname             | buffers
---------------------------------+---------
 pgbench_accounts                |    1644
 pg_attribute                    |      35
 pg_proc                         |      25
 pg_depend_reference_index       |      15
 pg_class                        |      14
 pg_statistic                    |      11
 pg_depend                       |      10
 pg_attribute_relid_attnum_index |      10
 pg_operator                     |       9
 pg_proc_proname_args_nsp_index  |       8
(10 rows)

https://www.postgresql.org/docs/10/static/pgbuffercache.html

PostgreSQLは、OSのファイルシステムキャッシュを活用するアーキテクチャであるため、
shard_buffers上にキャッシュされていない場合でも、OSのファイルシステムキャッシュ(ページキャッシュ)にPostgreSQLのオブジェクト(テーブルなど)が乗っていれば、ディスクへのアクセスは不要になります。

性能関連の調査等でOSファイルシステムのキャッシュにどの程度、オブジェクトがキャッシュされているのかを、
把握する必要があるケースもあると考えおります。(あまりないと思いますが)

OSファイルシステムPostgreSQLのオブジェクトが乗っているか否かを確認するツールとして、
Pythonで実装されたpgpagecacheを見つけたので確認してみたいと思います。
pgpagecacheの利用には、pg_buffercacheが必要です。(CREATE EXTENSION pg_buffercache ;が必要)

3. pgpagecacheの入手と解凍

$ wget https://pypi.python.org/packages/d7/94/274b82c8d735e70b9ba4eb060b7fdfbc780271a6bbf8dcf2ed6fd7a090ca/pgpagecache-0.1.tar.gz#md5=54cdf058813a45249f82e32cdced2727
--2017-12-30 23:57:24--  https://pypi.python.org/packages/d7/94/274b82c8d735e70b9ba4eb060b7fdfbc780271a6bbf8dcf2ed6fd7a090ca/pgpagecache-0.1.tar.gz
$ tar xvzf pgpagecache-0.1.tar.gz

4. 依存パッケージの入手

$ /usr/bin/pip install tabulate
$ /usr/bin/pip install psycopg2

5. pgpagecacheによる確認例

$ /usr/bin/python ./pgpagecache-0.1/pgpagecache/pgpagecache.py -u postgres -p xxxxxxx -d postgres -H localhost -P 5410 -b /home/postgres10/data/base/
PageCache Usage
---------------

| DB Name   | Table                                     |   Cached Pages |   Total Pages |   Ratio (%) |   Bytes |
|-----------+-------------------------------------------+----------------+---------------+-------------+---------|
| postgres  | pg_ts_dict_dictname_index                 |              0 |             4 |      0      |       0 |
| postgres  | pg_foreign_data_wrapper_name_index        |              0 |             2 |      0      |       0 |
| postgres  | pg_toast_13052                            |              0 |             0 |      0      |       0 |
| postgres  | pg_subscription_rel_srrelid_srsubid_index |              0 |             2 |      0      |       0 |
| postgres  | pg_toast_3596_index                       |              0 |             2 |      0      |       0 |
| postgres  | pg_am_oid_index                           |              0 |             4 |      0      |       0 |
| postgres  | pg_inherits_relid_seqno_index             |              0 |             2 |      0      |       0 |
| postgres  | pg_proc_oid_index                         |              0 |            20 |      0      |       0 |
[省略]
| postgres  | pgbench_accounts_pkey                     |              0 |           552 |      0      |       0 |
[省略]
PostgreSQL BufferCache Usage
----------------------------

| DB Name   | Table                             |    Bytes |
|-----------+-----------------------------------+----------|
| postgres  | pgbench_accounts                  | 13467648 |
| postgres  | pg_operator                       |   122880 |
| postgres  | pg_depend_reference_index         |   122880 |
| postgres  | pg_statistic                      |    90112 |


※ 表示される値についてこれから調査したいと考えております。


https://pypi.python.org/pypi/pgpagecache/0.1


参考情報
Python 3系で実行した場合エラーになりました。

$ /opt/anaconda3/bin/python --version
Python 3.6.1 :: Anaconda 4.4.0 (64-bit)
$ /opt/anaconda3/bin/python ./pgpagecache-0.1/pgpagecache/pgpagecache.py -u postgres -p xxxxxxx -d postgres -H localhost -P 5410 -b /home/postgres10/data/base/
  File "./pgpagecache-0.1/pgpagecache/pgpagecache.py", line 80
    print "Failed to mmap %s (errno: %d)" % (filename, get_errno())
                                        ^
SyntaxError: invalid syntax

PostgreSQLのテーブル・インデックスのサイズを確認するSQL

PostgreSQLのテーブル・インデックスのサイズを確認する際には以下のSQLを利用する。

SELECT 
  nspname || '.' || relname AS "relation",
  relpages,
  pg_relation_size(C.oid) AS "size",
  reltuples
FROM 
  pg_class C
LEFT JOIN pg_namespace N 
ON (N.oid = C.relnamespace)
WHERE 
  nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY 
  pg_relation_size(C.oid) DESC;

上記SQLでは推定行数( reltuples)を出力しているため、レコード数とサイズの増加の関連性を確認する際にも利用できると考えております。

29.1. ディスク使用量の決定
Disk Usage/ja - PostgreSQL wiki

PostgreSQL 10 インストールメモ

2017/10/5にPostgreSQLのバージョン 10がリリースされたので、
インストールして、新機能を試したいと思います。

環境情報

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)


1.環境準備
1.1.make

# gmake -version
GNU Make 3.82
[省略]


1.2.gcc

# gcc -v
[省略]
gcc version 4.8.5 20150623 (Red Hat 4.8.5-11) (GCC)


1.3. Readline

# yum install readline
Package readline-6.2-10.el7.x86_64 already installed and latest version
Nothing to do

# yum install readline-devel
Package readline-devel-6.2-10.el7.x86_64 already installed and latest version
Nothing to do

1.4. zlib

# yum install zlib
Package zlib-1.2.7-17.el7.x86_64 already installed and latest version
Nothing to do
# yum install zlib-devel
Package zlib-devel-1.2.7-17.el7.x86_64 already installed and latest version
Nothing to do

2.PostgreSQLインストール

2.1.postgresユーザ作成

# useradd postgres10

2.2.ソースの入手と解凍

# cd /usr/local/src/
# wget https://ftp.postgresql.org/pub/source/v10.0/postgresql-10.0.tar.gz
# tar xvzf postgresql-10.0.tar.gz
# chown -R postgres10:postgres10 postgresql-10.0

2.3.コンパイル

# su - postgres10
$ cd /usr/local/src/postgresql-10.0/
$ ./configure
$ gmake world ※
(省略)
PostgreSQL, contrib, and documentation successfully made. Ready to install.

※ドキュメント(HTMLやman)や追加モジュール(contrib)を含め、構築可能なもの全てを構築したい場合、上記の様にworldを付与して実行

2.4.リグレーションテスト

$ gmake check
(省略)
=======================
 All 178 tests passed.
=======================
(省略)

2.5.インストール

$ su
# gmake install-world
(省略)
PostgreSQL, contrib, and documentation installation complete.


2.6.インストールディレクトリの所有者変更

# cd /usr/local/
# chown -R postgres10:postgres10 pgsql/

2.7.postgres用ユーザの環境変数設定

# su - postgres10
$ vi .bash_profile
(下記を設定)

# PostgreSQL
export POSTGRES_HOME=/usr/local/pgsql
export PGDATA=/home/postgres10/data
export PATH=$PATH:$POSTGRES_HOME/bin
export PGLIB=/usr/local/pgsql/lib
export MANPATH="$MANPATH":$POSTGRES_HOME/man
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

2.7.postgresユーザの環境変数設定読み込み

$ source .bash_profile

2.8.データベースクラスタの作成

$ initdb -E UTF8 --no-locale

2.9.ログ出力設定
# ログのみ出力(その他の設定は省略)

$ vi /home/postgres10/data/postgresql.conf
#logging_collector = off                # Enable capturing of stderr and csvlog
logging_collector = on

2.10.PostgreSQL起動

$ pg_ctl start
waiting for server to start....2017-10-21 22:38:12.421 JST [12521] LOG:  listening on IPv6 address "::1", port 5432
2017-10-21 22:38:12.421 JST [12521] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2017-10-21 22:38:12.622 JST [12521] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2017-10-21 22:38:13.032 JST [12521] LOG:  redirecting log output to logging collector process
2017-10-21 22:38:13.032 JST [12521] HINT:  Future log output will appear in directory "log".
. done
server started


以上

pgAdmin4 インストール手順 CentOS 7

「pgAdmin 4」がリリースされてしばらく時間がたったので、
CentOS 7上にインストールし、試したい考えています。
まずはインストールです。

環境情報は下記の通りです。

# cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)
# uname -r
3.10.0-514.2.2.el7.x86_64
# python --version
Python 2.7.5

yumコマンドを利用した手順

1.SELinuxの無効化確認(Permissive)

# getenforce
Permissive


2.PostgreSQLレポジトリRPMダウンロード

# cd /usr/local/src/
# wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
# ls -l /usr/local/src/pgdg-redhat96-9.6-3.noarch.rpm
-rw-r--r--. 1 root root 4816  9月 27 15:59 /usr/local/src/pgdg-redhat96-9.6-3.noarch.rpm

3.PostgreSQLレポジトリ追加

# rpm -ivh /usr/local/src/pgdg-redhat96-9.6-3.noarch.rpm
準備しています...              ################################# [100%]
更新中 / インストール中...
   1:pgdg-redhat96-9.6-3              ################################# [100%

4.EPELレポジトリの追加

# yum install epel-release -y
[省略]
# rpm -qa | grep epel
epel-release-7-8.noarch

5.pgAdmin4用ユーザの作成

# useradd pgadmin
# passwd pgadmin
ユーザー pgadmin のパスワードを変更。
新しいパスワード:[任意の文字列]
新しいパスワードを再入力してください:[任意の文字列]
passwd: すべての認証トークンが正しく更新できました。

6.pgAdmin4のインストール

# yum install pgadmin4-v1-web -y
[省略]
# yum list installed pgadmin4-v1-web 
pgadmin4-v1-web.noarch                    1.1-5.rhel7                    @pgdg96

7.pgAdmin4の設定変更

# vi /usr/lib/python2.7/site-packages/pgadmin4-web/config_distro.py

#SERVER_MODE = False # 修正箇所
MINIFY_HTML = False
HELP_PATH = '/usr/share/doc/pgadmin4-v1-docs/en_US/html'

8.pgAdminのセットアップ

# su - pgadmin
$ python /usr/lib/python2.7/site-packages/pgadmin4-web/setup.py
pgAdmin 4 - Application Initialisation
======================================


The configuration database - '/home/pgadmin/.pgadmin/pgadmin4.db' does not exist.
Entering initial setup mode...
NOTE: Configuring authentication for SERVER mode.


    Enter the email address and password to use for the initial pgAdmin user     account:

Email address: [管理者のメールアドレス]
Password:[管理者のパスワード]
Retype password:[管理者のパスワード]

The configuration database has been created at /home/pgadmin/.pgadmin/pgadmin4.db

9.Apache httpdの設定変更
※ 必要に応じて下記ファイル修正を実施

1. /usr/lib/python3.5/ → /usr/lib/python2.7/
2. アクセス可能なサーバの制御

# cp -p /etc/httpd/conf.d/pgadmin4-v1.conf.sample /etc/httpd/conf.d/pgadmin4-v1.conf
# vi /etc/httpd/conf.d/pgadmin4-v1.conf


10.サービスファイルの修正

# cp -p /usr/lib/systemd/system/pgadmin4-v1.service /etc/systemd/system/
# vi /etc/systemd/system/pgadmin4-v1.service
[下記箇所を修正]

#User=root
#Group=root

User=pgadmin
Group=pgadmin

# systemctl daemon-reload

11.pgAdmin4の起動

# systemctl start pgadmin4-v1.service
# systemctl status pgadmin4-v1.service
[省略]
   Active: active (running) since 日 2016-12-18 02:07:58 JST; 1s ago
[省略]


12.Apache httpdの起動

# systemctl start httpd.service
# systemctl status httpd.service
[省略]
   Active: active (running) since 日 2016-12-18 02:11:24 JST; 9s ago
[省略]

13.pgAdmin4へアクセス

下記URLにアクセスし、pgAdmin4にログインできることを確認
http://サーバのIPアドレス/pgadmin4

※ 80ポートをアクセス可能な状態にしておいてください。
fiwarewalld利用時のコマンド例

# firewall-cmd --add-port=80/tcp
success
# firewall-cmd --permanent --add-port=80/tcp
success

下記の画面が表示されますので、メールアドレスとパスワードを入力してログインします。
f:id:masanori19871105:20161218022650p:plain

ログイン後、PostgreSQLが動作するサーバに接続すると下記のような画面が表示されます。
f:id:masanori19871105:20161218032123p:plain


■ EDB社が提供するインストーラを利用
EDB社が提供するPostgreSQL 9.6のインストーラを利用し、
PostgreSQLをインストールするとpgAdmin4も同時にインストールされ利用することが可能です。下記URLよりダウンロード可能です。

www.enterprisedb.com


以上です、インストールが完了しのたでpgAdmin4の機能を試していきます。

Dockerコマンド(よく利用するもの)

Dockerを利用する際にコマンドを忘れてしまうため、
忘れないようにメモしておきます。(記載中)


環境

# cat /etc/redhat-release
CentOS Linux release 7.1.1503 (Core)
# docker -v
Docker version 1.7.1, build 786b29d


1. Docker イメージの削除
docker imageの「TAG」列がとなっている、不要なイメージを削除します。
DockerのFIXMEに下記のように記載されており、将来のバージョンでは、
下記のような正規表現ではなく、コマンドオプション等で削除できるようになる予定です。

Simple command to remove all untagged images (`docker rmi $(docker images | awk '/^/ { print $3 }')`)


github.com

# docker rmi $(docker images | awk '/^<none>/ { print $3 }')

2. DockerコンテナのIPアドレス確認方法
DockerコンテナのIPアドレスは下記のコマンド確認可能です。

# docker inspect --format '{{ .NetworkSettings.IPAddress }}' [コンテナ名もしくはコンテナID]
# docker inspect --format '{{ .NetworkSettings.IPAddress }}' postgresreplication92_slave_1
172.17.0.15
# docker inspect --format '{{ .NetworkSettings.IPAddress }}' postgresreplication92_slave_2
172.17.0.14


3. Dockerコンテナにログイン

# docker exec -ti postgresreplication92_slave_1 /bin/bash
[root@99381b5d7bb7 postgresql-9.2.14]#

追記します。

DockerをGUIから利用する(Kitematicの導入)

こんばんは!!

Docker Engine上のコンテナをGUIから操作したいと考え、
Kitematicを試してみました。

環境情報は下記の通りです。

OS : Windows 10 ※1

下記のURLに「Windows 10 is not currently supported. 」と記載されているが、
マシンが1台しかないため、試してみる。(2015/10/15時点)

https://docs.docker.com/installation/windows/


DockerToolboxのインストール

1. 下記のURLからDockerToolboxをダウンロード
https://www.docker.com/toolbox

f:id:masanori19871105:20151015231202j:plain

2. ダウンロードしたDockerToolbox(1.8.3)をダブルクリック

f:id:masanori19871105:20151015231242j:plain

3. 表示されるウィンド上で「Next >」を押下。

f:id:masanori19871105:20151015231242j:plain

4. 任意のフォルダを選択し、「Next >」を押下。

f:id:masanori19871105:20151015231317j:plain

5. インストールするコンポーネント(Full installation)を選択し、「Next >」を押下。

f:id:masanori19871105:20151015231339j:plain

6. デスクトップアイコンの作成およびdocker.exeおよびdocker-machine.exeをPATHに追加する設定で、「Next >」を押下。

f:id:masanori19871105:20151015231349j:plain

7. インストール設定を確認し、「Install」を押下。

f:id:masanori19871105:20151015231545j:plain

8. インストールが完了すると下記が表示されるため、「Finish」を押下。



これで導入は完了する。

Kitematicの利用

1. デスクトップ上の「Kitematic(Alpha)」をダブルクリック。
# Docker用の仮想マシンがOracleVM Virturalbox上に作成される。
仮想マシンの名前は、[default」

f:id:masanori19871105:20151016001408j:plain


2. ログイン画面が表示されるので、ユーザ名、パスワードを入力し、ログイン。

f:id:masanori19871105:20151016001416j:plain

3. イメージ一覧が表示される。本手順では、「postgres」を選択。(「create」を押下)

f:id:masanori19871105:20151016001439j:plain

f:id:masanori19871105:20151016001502j:plain

4. 「Setting」を押下。

f:id:masanori19871105:20151016001548j:plain

5. LANGをCに変更し、「SAVE」を押下。

f:id:masanori19871105:20151016001528j:plain

6. 「Ports」を押下。仮想マシン上の稼働ポートを確認。

f:id:masanori19871105:20151016001658j:plain

7. 「START」を押下し、「EXEC」を押下。

8. 表示される画面でPostgreSQLに接続

f:id:masanori19871105:20151016001850j:plain



・Kitematicを導入してみての所感
1. Linux(CentOS上)で利用するほうが使いやすい。(コマンドラインのほうがよい気がする。)
 2. Kitematicはα版のため、正式版が公開された後にもう一度使ってみます。

また、下記のエラーが発生したこともあり、スムーズに導入できませんでした。
https://github.com/kitematic/kitematic/issues/1008


GUIを利用しようとKitematicを導入しましたが、自身としてはLinux上のコマンドラインの方が利用しやすい結果となりました。