他DBMSとの比較 †
特徴 †
- PostgreSQLはJSON(=jsonb型)はNative Indexをサポートしている為、中途半端なVirtual Columnという仕組みでIndexを模してるMariaDB(MySQL)とは、JSON型で値へのアクセス速度が必要になった場合速度が断然違う(速い)
- REST APIを提供するPostgREST、GraphQL APIを提供するGraphileといったものがあり、JSON利用が簡単&性能が出る=モバイルアプリ&Single Page Application開発がより少ない負担で出来る
- PostGISによるGIS機能のサポートが充実
- MariaDBより機能が多い
- データのエラー判定がMariaDBより厳格で、想定外の値処理による不正な値処理の正常扱い通過が起きにくい
- 1テーブルが持てる索引の数にMariaDB/MySQLの様な制限(64個)がない
運用事例 †
JSON型 †
JSONB †
hstore型 †
VACUUM †
概要 †
- Vacuum処理とは、データ削除などで生じた不要領域の回収などを行う処理
- PostgreSQL 9.5で、複数のテーブルを対象にパラレル処理でVacuumを行える機能が追加された
- PostgreSQL 13では同一テーブル内で複数のインデックスを対象に並列処理が可能になった
AUTO VACUUM機能 †
- PostgreSQLには「AUTO VACUUM」機能が搭載されており、自動で随時VACUUMが実行されるため、多くの場合問題とならない
- テーブルによって追加・更新・削除の頻度、規模は様々であるため、AUTO VACUUM機能によるVACUUM実行のタイミングが適切でないケースが出てくる
VACUUMに関する注意点 †
- VACUUM FULLはテーブルに排他ロックを必要とするうえ、処理に非常に長い時間を要する。そのため、VACUUM FULLの実行がサービス障害につながることもありえる。
- VACUUM FULLが必要とならないよう、普段から適切にVACUUMが実行されるようにAUTO VACUUMのパラメータ調整や、各テーブルのVACUUM実行状況の監視が大切
ログ †
パフォーマンスチューニング †
バックアップ・リストア †
ファイル形式 †
スクリプト形式 †
- スクリプト形式の出力は、リストアに必要なSQL文の羅列が出る
- psqlコマンドでリストア
- スクリプト形式の場合はプレーンテキストなので、リストアの際にエラーが出たら、中を見れるという利点がある
アーカイブ形式 †
- バイナリの形で出力される
- リストアはpsqlコマンドでなくpg_restoreコマンドで行う
- アーカイブ形式の利点は、 ｢指定したtableのみを選択してリストアできる｣
- アーカイブ形式には以下の2種類がある
- custom形式はdefaultで圧縮されるので、ファイルサイズが小さくなる
バックアップ †
リストア †
バッチファイル †
backup.bat †
set USER=postgres
pg_dump --create --clean --if-exists --inserts -U %USER% -d %1 > %2
force_terminate_connection.sql †
select * from pg_database where datname = 'XXXX';
update pg_database set datallowconn = 'false' where datname = 'XXXX';
alter database XXXX connection limit 1;
select pg_terminate_backend(pid) from pg_stat_activity where datname = 'XXXX';
restore.bat †
set USER=postgres
psql -U %USER% -f force_terminate_connection.sql
psql -U %USER% -f %1
ODBCドライバ †
インストール †
DSN †
- システム DSN
- システム全体で使用される DSN
- プログラムが使用されているマシンでは、この DSN を作成しなければならない
- レジストリキー：HKEY_LOCAL_MACHINE\Software\Odbc\Odbc.ini\Odbc データ ソース
- ユーザー DSN
- 特定のユーザー用に作成された DSN
- DSN を作成したユーザーのみ使用することができる
- レジストリキー：HKEY_CURRENT_USER\Software\Odbc\Odbc.ini\Odbc データ ソース
- ファイル DSN
- DSN を含むテキストをテキストファイルに格納
- データソースに接続するための情報をファイルに格納するので，ODBC.INIやレジストリから情報を取得しないで接続を確立できる
ERROR [IM002] [Microsoft][ODBC Driver Manager] データ ソース名および指定された既定のドライバーが見つかりません。 †
- まずは「システム DSN」にドライバーを追加しているか確認
- プログラムが使用されているマシンでは「システム DSN」を作成しなければならない
IISとの接続トラブル †
- IISでODBC利用時のアクセス権限
- レジストリ「HKLM \ SOFTWARE \ Microsoft \ ODBC \ ODBC.INI」に、IISの実行ユーザに対するアクセス許可を与える
- 「ファイル名を指定して実行」で「regedit」を実行
- 表示されたレジストリエディタのダイアログにて、ODBC.INIを右クリックし、「アクセス許可」を選択
- 「ODBC.INIのアクセス許可」のダイアログが表示されるので、追加ボタンを押し、IISの実行ユーザを登録
インストール †
Windows †
Mac OS X †
1. インストール
$ ./configure オプション
$ make
$ sudo make install（デフォルトでは/usr/local/pgsqlにインストールされる）
2. 共有メモリ関係の設定変更
/etc/sysctl.conf がある場合は、このファイルを以下のように編集
kern.sysv.shmmax=167772160
kern.sysv.shmall=65536
kern.sysv.shmmin=1
kern.sysv.shmseg=8
kern.sysv.shmmni=32
ない場合は、/etc/rc の64行目付近を以下のように編集
sysctl -w kern.sysv.shmmax=167772160 kern.sysv.shmmin=1 kern.sysv.shmmni=32 kern.sysv.shmseg=8 kern.sysv.shmall=65536
編集後、Macを再起動し、ターミナルを起動。以下のコマンドを打って変更が反映されているか確認。
$ sysctl -a | grep kern.sysv
kern.sysv.shmmax: 167772160
kern.sysv.shmmin: 1
kern.sysv.shmmni: 32
kern.sysv.shmseg: 8
kern.sysv.shmall: 65536
:
3. postgres というユーザーを作成
4. "su - postgres" でpostgresユーザーになって以下を実行
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l postgres.log start
$ /usr/local/pgsql/bin/createdb test
$ /usr/local/pgsql/bin/psql test
5. 自動起動の設定
http://www.entropy.ch/software/macosx/postgresql/ より
pgsql-startupitem-1.2.pkg.tar.gz をダウンロードしてインストール
設定 †
パスワード変更 †
ツール †
pgAdmin4 †
Azure Database for PostgreSQL †
サポート †
リンク集 †
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
