Chapter 3. PostGIS管理

Table of Contents

3.1. パフォーマンスチューニング

PostGISの調整はPostgreSQLの作業量の調整と非常に似ています。ジオメトリとラスタは重く、メモリ関連の最適化は他のPostgreSQLクエリと比べて影響が大きい点だけは留意して下さい。

PostgreSQLの最適化に関する一般的な詳細は、 Tuning your PostgreSQL Serverをご覧ください。

PostgreSQL 9.4以上では、ALTER SYSTEMを使うことで、postgresql.confpostgresql.auto.confを触ることなくサーバレベルで設定できます。

ALTER SYSTEM SET work_mem = '256MB';
-- this forces non-startup configs to take effect for new connections
SELECT pg_reload_conf();
-- show current setting value
-- use SHOW ALL to see all settings
SHOW work_mem;

PostgreSQLの設定に加えて、PostGISには Section 7.23, “Grand Unified Custom変数 (GUC)”で挙げる独自設定があります。

3.1.1. 起動時

次に示す設定はpostgresql.confにあります:

constraint_exclusion

  • デフォルト: partition

  • 一般的にテーブルのパーティショニングに使われます。デフォルトとして"partition"に設定されています。継承階層内にあり、プランナにペナルティ以外を払わないなら、クエリプランナにテーブルの制約条件の解析だけを行わせるので、PostgreSQL 8.4以上ではこれが理想的です。

shared_buffers

  • デフォルト: PostgreSQL 9.6では128MB以下

  • 利用可能なRAMの25%から40%を設定します。Windowsでは高く設定することができないかも知れません。

max_worker_processes これは、PostgreSQL 9.4以上で有効です。PostgreSQL 9.6以上では、パラレルクエリ処理に使うプロセス数の最大値の制御で、さらに重要なものとなっています。

  • デフォルト: 8

  • システムが対応できるバックグラウンドプロセスの最大値を設定します。このパラメータはサーバ起動時のみ設定できます。

3.1.2. 実行時

work_mem - 並べ替えや複雑なクエリに使われるメモリのサイズの設定

  • デフォルト: 1-4MB

  • 大きなデータベースの場合や、複雑なクエリの場合、RAMが多い場合は値を大きくするように調整します。

  • 同時接続ユーザ数が多い場合や、RAMが少ない場合には値を小さくするように調整します。

  • たくさんのRAMを持ち、少数の開発者しかいない場合は次のようにします。

    SET work_mem TO '256MB';

maintenance_work_mem - VACUUM, CREATE INDEX等で使われるメモリのサイズ

  • デフォルト: 16-64MB

  • 一般的には低すぎます - メモリスワップの間、入出力が拘束され、オブジェクトがロックされます

  • たくさんのRAMを持つ本番サーバでは32MBから1GBが推奨ですが、同時接続ユーザ数に依存します。たくさんのRAMを持ち、少数の開発者しかいない場合は次のようにします:

    SET maintenance_work_mem TO '1GB';

max_parallel_workers_per_gather

この設定はPostgreSQL 9.6以上で使用でき、並列クエリに対応しているPostGIS 2.3以上に影響は限られます。0より大きい値に設定すると、ST_Intersectsといった関係関数を含むクエリで、複数プロセッサが使われるようにできます。その時、2倍を超える速度が出る可能性があります。予備のプロセッサが多数ある場合には、この値をプロセッサ数に変更するべきです。また、max_worker_processesをこの値と同じにするようにします。

  • デフォルト: 0

  • 単一のGatherノードが開始できるワーカの最大数を設定します。並列ワーカは、max_worker_processesで確立されたプロセスのプールから取得されます。要求したワーカ数は、実際には実行可能になっていない場合があることに注意して下さい。これが発生する場合には、想定より少ないワーカでプランが実行され、非効率になります。これの値を0 (デフォルト値)にすると、パラレルクエリ実行が無効になります。

3.2. ラスタ機能の設定

ラスタ機能を有効にしたら、下に示す確実な設定方法を読んだ方がいいです。

PostGIS 2.1.3以降では、データベース外ラスタと全てのラスタドライバは、デフォルトでは無効になっています。これらを有効にするには、サーバ上で、環境変数 POSTGIS_GDAL_ENABLED_DRIVERSPOSTGIS_ENABLE_OUTDB_RASTERSを設定します。PostGIS 2.2では、Section 7.23, “Grand Unified Custom変数 (GUC)”に従って設定する、クロスプラットフォームな手法があります。

データベース外ラスタを有効にするには次のようにします:

POSTGIS_ENABLE_OUTDB_RASTERS=1

他の値を入れたり、値を入れない場合には、データベース外ラスタは無効になります。

インストールしたGDALのドライバを有効にするには、次の環境変数を設定します:

POSTGIS_GDAL_ENABLED_DRIVERS=ENABLE_ALL

一部のドライバのみ有効にしたい場合には、環境変数を次のように設定します:

POSTGIS_GDAL_ENABLED_DRIVERS="GTiff PNG JPEG GIF XYZ"
[Note]

Windows上の場合は、ドライバリストに引用符をつけないで下さい

環境変数の設定はOSによって異なります。UbuntuまたはDebian上でapt-postgresqlを経由したPostgreSQLのインストールについては、/etc/postgresql/10/main/environmentを編集するのが好ましい方法です。 ここで、10はPostgreSQLのバージョンを指し、mainはクラスタを指します。

Windowsでサービスとして実行している場合には、システム変数で設定します。Windows 7では、コンピュータを右クリックしてプロパティをクリックするか、エクスプローラの検索バーにコントロール パネル\すべてのコントロール パネル項目\システムを指定します。 それから、システムの詳細設定 -> 詳細設定 -> 環境変数 を順にクリックして、新しいシステム環境変数を追加します。

環境変数を設定した後は、設定を反映させるために、PostgreSQLサービスの再起動が必要です。

3.3. 空間データベースの作成

3.3.1. エクステンションを使って空間データベースを有効にする

PostgreSQL 9.1以上を使っていて、エクステンションのPostGISモジュールをコンパイル、インストールしている場合には、エクステンションというメカニズムを使用して、データベースを空間データベースに切り替えることができます。

中核となるPostGISエクステンションには、ジオメトリ、ジオグラフィ、spatial_ref_sysおよび全ての関数とコメントが含まれています。ラスタとトポロジは別のエクステンションになっています。

空間データベースにしたいデータベース上で次のSQLを実行します:

CREATE EXTENSION IF NOT EXISTS plpgsql;
      CREATE EXTENSION postgis;
      CREATE EXTENSION postgis_raster; -- OPTIONAL
      CREATE EXTENSION postgis_topology; -- OPTIONAL

3.3.2. エクステンションを使わずに空間データベースを有効にする (お勧めできません)

[Note]

これは、通常はPostgreSQLのエクステンションのディレクトリ内にPostGISをインストールできないか、したくない場合 (たとえばテスト中や開発中、または制限のある環境内)にのみ必要となります。

ビルドの際に指定した[prefix]/share/contrib内にある様々なSQLファイルをロードしてPostGISオブジェクトと関数の定義をデータベースに追加します。

中核のPostGISオブジェクト (ジオメトリ型とジオグラフィ型、これらに対応する関数)はpostgis.sqlスクリプトにあります。ラスタオブジェクトはrtpostgis.sqlスクリプトにあります。トポロジオブジェクトはtopology.sqlスクリプトにあります。

完全なEPSG座標系定義IDセットについては、spatial_ref_sys.sql定義ファイルをロードしてspatial_ref_sysテーブルを生成して下さい。これによりジオメトリ関数ST_Transform()が実行できるようになります。

PostGIS関数にコメントを追加したい場合には、postgis_comments.sqlスクリプト内のコメントが見つかると思います。コメントはpsqlのターミナルウィンドウから単に\dd [関数名]と打ち込むだけで見ることができます。

ターミナルで次のシェルコマンドを実行します:

DB=[yourdatabase]
    SCRIPTSDIR=`pg_config --sharedir`/contrib/postgis-3.4/

    # Core objects
    psql -d ${DB} -f ${SCRIPTSDIR}/postgis.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/spatial_ref_sys.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/postgis_comments.sql # OPTIONAL

    # Raster support (OPTIONAL)
    psql -d ${DB} -f ${SCRIPTSDIR}/rtpostgis.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/raster_comments.sql # OPTIONAL

    # Topology support (OPTIONAL)
    psql -d ${DB} -f ${SCRIPTSDIR}/topology.sql
    psql -d ${DB} -f ${SCRIPTSDIR}/topology_comments.sql # OPTIONAL

3.4. 空間データベースのアップグレード

既存の空間データベースのアップグレードは、新しいPostGISオブジェクト定義の置き換えや導入を必要とするとき、慎重を要することがあります。

不幸なことに、定義の全てが実行中のデータベース内で簡単には置き換えられるわけではないので、ダンプ/リロードが最善策となることがあります。

PostGISには、マイナーバージョンアップやバグフィクスリリースの場合に使うソフトアップグレードと、メジャーアップグレードで使うハードアップグレードが用意されています。

PostGISをアップグレードしようとする前にデータのバックアップを取ることは、常に価値のあるものです。pg_dumpで -Fc フラグを使うと、ハードアップグレードによってダンプを常にリストアすることができます。

3.4.1. ソフトアップグレード

エクステンションを使ってデータベースをインストールした場合には、エクステンションモデルでアップグレードしなければなりません。 古いSQLスクリプトを使ってインストールした場合には、SQLスクリプトは既にサポートされていませんので、エクステンションに切り替えるべきです。

3.4.1.1. 9.1以上でエクステンションを使ったソフトアップグレード

エクステンションを使ってPostGISをインストールした場合には、エクステンションを使ってアップグレードする必要があります。エクステンションを使ったマイナーアップグレードはかなり楽です。

PostGIS 3以上を実行している場合には、PostGIS_Extensions_Upgrade関数を使ってインストールしているもののうち最新の版にアップグレードすべきです。

SELECT postgis_extensions_upgrade();

PostGIS 2.5以前を実行している場合には、次のようにします:

ALTER EXTENSION postgis UPDATE;
SELECT postgis_extensions_upgrade();
-- This second call is needed to rebundle postgis_raster extension
SELECT postgis_extensions_upgrade();

インストールされたPostGISに複数のバージョンがあり、最新版にアップグレードしたくない場合には、明示的なバージョンの指定ができます。次のようにします:

ALTER EXTENSION postgis UPDATE TO "3.5.0dev";
ALTER EXTENSION postgis_topology UPDATE TO "3.5.0dev";

次のようなエラー通知が表示されることがあります。

No migration path defined for … to 3.5.0dev

この場合は、データベースをバックアップして、 Section 3.3.1, “エクステンションを使って空間データベースを有効にする” に記述されているように新しいデータベースを生成し、バックアップを新しいデータベースにリストアしなければなりません。

次のようなメッセージを得ることがあります:

Version "3.5.0dev" of extension "postgis" is already installed

この場合は、全てアップデートされていて、安全に無視できます。SVN版から次版 (新しい版番号を得ていないもの)にアップグレードしようとしない限り、"next"を版文字列に追加できますが、次回に"next"を削除する必要があります:

ALTER EXTENSION postgis UPDATE TO "3.5.0devnext";
ALTER EXTENSION postgis_topology UPDATE TO "3.5.0devnext";
[Note]

PostGISをバージョン指定なしにインストールした場合には、 しばしば再格納の前のPostGIS EXTENSIONの再インストールをとばすことができます。 バックアップはCREATE EXTENSION postgisだけで、リストアの間に最新版になります。

[Note]

PostGISエクステンションを3.0.0より前からアップグレードする場合には、ラスタ機能が不要なら、安全に削除できる新しい postgis_rasterエクステンションを持つことになります。次のようにします:

DROP EXTENSION postgis_raster;

3.4.1.2. 9.1より前またはエクステンションを使わないソフトアップグレード

PostGISをエクステンションを使わずにインストールした人向けです。エクステンションを使っていてこの方法を使うと、次のようなメッセージが現れます:

can't drop … because postgis extension depends on it

ご注意: PostGIS 1.*またはr7429以前のPostGIS 2.*へ移行する場合には、この手続きを使うことができませんが、ハードアップグレードを実行する必要があります。

コンパイルとインストール (make install)の実行後に、インストール先フォルダ内にある*_upgrade.sqlのファイルの集合を見つけておくべきです。次のコマンドで一覧を得られます:

ls `pg_config --sharedir`/contrib/postgis-3.5.0dev/*_upgrade.sql

postgis_upgrade.sqlから順番に全てをロードします。

psql -f postgis_upgrade.sql -d your_spatial_database

同じ手続きをラスタ、トポロジ、SFCGALエクステンションに適用します。それぞれのファイル名はrtpostgis_upgrade.sql, topology_upgrade.sql, sfcgal_upgrade.sqlになります。必要な場合は次のように実行します:

psql -f rtpostgis_upgrade.sql -d your_spatial_database
psql -f topology_upgrade.sql -d your_spatial_database
psql -f sfcgal_upgrade.sql -d your_spatial_database

エクステンションによるインストールに変更した方が良いです。次のようにします:

psql -c "SELECT postgis_extensions_upgrade();"
[Note]

求める版へのアップグレードに使う特定のpostgis_upgrade.sqlが発見できない場合には、ソフトアップグレードを実行するにはあまりに前の版を使っています。ハードアップグレードが必要です。

PostGIS_Full_Version関数の"procs need upgrade"というメッセージで、この種のアップグレードを実行する必要性についての情報が得られます。

3.4.2. ハードアップグレード

ハードアップグレードとは、PostGISで利用可能なデータの完全なダンプ/リロードを意味します。PostGISオブジェクトの内部格納状態が変更される場合や、ソフトアップグレードができない場合に、ハードアップグレードが必要です。付録のリリースノートに、版ごとについて、ダンプ/リロード (ハードアップグレード)の要否を記載しています。

ダンプ/リロード作業はpostgis_restoreスクリプトが補助します。このスクリプトは、PostGIS (古いものを含む)に属する定義を全て飛ばします。また、重複シンボルエラーや非推奨オブジェクトを持越すことなく、スキーマとデータをPostGISをインストールしたデータベースにリストアできます 。

Windows用に関する追加情報は Windows Hard upgradeにあります。

手続きは次の通りです:

  1. アップグレードしたデータベース (olddbと呼ぶことにしましょう)の「カスタム書式」のダンプを、バイナリBLOBデータを含めたダンプを指定して (-b)、verboseモード (-v)で生成します。ユーザはデータベースのオーナーになることができ、PostgreSQLのスーパーユーザである必要はありません。

    pg_dump -h localhost -p 5432 -U postgres -Fc -b -v -f "/somepath/olddb.backup" olddb
  2. 新しいデータベースにPostGISを、PostGISが無い状態からインストールします。このデータベースをnewdbと呼ぶことにします。この作業に関する説明についてはSection 3.3.2, “エクステンションを使わずに空間データベースを有効にする (お勧めできません)”Section 3.3.1, “エクステンションを使って空間データベースを有効にする”とを参照して下さい。

    ダンプにあるspatial_ref_sysは、リストアされますが、既にあるspatial_ref_sysを上書きしません。リストア対象のデータベースに公式データセットの訂正が確実に伝わるようにするためです。標準のエントリを上書きしたい場合は、newdbを生成する際にspaltial_ref_sys.sqlファイルをロードしないだけです。

    データベースが本当に古く、ビューや関数に、長く非推奨になっている関数があるような場合には、関数やビューを使えるようにするlegacy.sqlをロードする必要があるでしょう。ただし、本当に必要な場合に限ります。可能なら、ビューや関数をダンプせずにアップグレードすることを検討して下さい。非推奨関数は、uninstall_legacy.sqlで後から削除することができます。

  3. バックアップを新しいnewdbデータベースにリストアするには、postgis_restoreを使います。psqlが予期せぬエラーを標準エラー出力に出すことがあります。これらのログを保存しておいて下さい。

    postgis_restore "/somepath/olddb.backup" | psql -h localhost -p 5432 -U postgres newdb 2> errors.txt

エラーは次の場合に起こりえます:

  1. ビューまたは関数の中に非推奨のPostGISオブジェクトを使っているものがある場合。これの訂正には、リストア前にlegacy.sqlスクリプトのロードを試してみることができます。非推奨オブジェクトをまだ持っている版のPostGISにリストアして、コードを作り替えた後に再び移動させることもできます。legacy.sqlを利用する場合は、非推奨関数を使うのをやめたコードに訂正して、uninstall_legacy.sqlをロードするのを忘れないでください。

  2. ダンプファイル内のspatial_ref_sysにあるカスタムレコードが不正なSRIDになっていることがあります。妥当なSRID値は0より大きく999000より小さくなります。999000から999999の間は内部利用のための予約領域ですが、SRID値 > 999999 の場合は、その値は一切使用できません。全ての不正なSRIDを持つ独自レコードは、SRID値 > 999999 の場合は予約領域に移動して、保持されます。しかし、spatial_ref_sysテーブルから、値が保持されるように設定されているチェック制約が外れます。場合によっては (複数の不正なSRIDが同じ予約領域のSRID値に変換されるとき)、主キーも外れます。

    これを修正するために、独自のSRSを妥当な値 (910000..910999の範囲)のSRIDに複写し、全てのテーブルを新しいSRIDに変更 (UpdateGeometrySRID)し、spatial_ref_sysから不正なエントリを削除します。そして、次のようにチェック制約を再構築します:

    ALTER TABLE spatial_ref_sys ADD CONSTRAINT spatial_ref_sys_srid_check check (srid 
    > 0 AND srid < 999000 );
    
    ALTER TABLE spatial_ref_sys ADD PRIMARY KEY(srid));
        

    フランスIGN地図を含む古いデータベースをアップグレードする場合には、おそらくSRIDが範囲外になり、データベースのインポート時に次のような問題に遭遇します:

    WARNING: SRID 310642222 converted to 999175 (in reserved zone)

    この場合には、次のステップを試すことができます。最初にpostgis_restoreから出たIGNをSQLから完全に破棄します。そのために次のコマンドを実行します:

    postgis_restore "/somepath/olddb.backup" > olddb.sql

    次のコマンドを実行します:

    grep -v IGNF olddb.sql > olddb-without-IGN.sql

    その後、新しいデータベースを生成し、必要なPostGISエクステンションを有効化して、このスクリプトで確実にフランスIGNの系を挿入します。これらの処理の後、次のようにデータのインポートを行います:

    psql -h localhost -p 5432 -U postgres -d newdb -f olddb-without-IGN.sql  2> errors.txt