Ubuntu20.04にPostgreSQL & PostGISのインストール

## はじめに
QGISのqwc2を使ったWeb公開を色々と試しているのだが、ローカルで作成したQGISプロジェクトをサーバーにアップロードさせる際、サーバー自体はGCP上に構築しているため、プロジェクト内で参照しているデータが存在しないという状態になる。
（QGISサーバーとローカルPCは、同じネットワーク上にないので当たり前の話だが・・・）
そこで、サーバー上にPostgreSQLを構築し、ローカルのPostgresSQLから外部参照する形で、同じテーブル構造をローカルPostgresで維持できるかを試してみたく、その基本となる内容をメモする。

## 実行環境
【PC環境-OS】
　　・Ubuntu20.04 LTS

【ソフトウェア-バージョン】
　　・PostgresSQL 11系　
　　・PostGIS 2.5

## 今回のやる事

１．Ubuntu20.04にPostgreSQLとPostGISをインストール
２．外部からの接続確認
３．ローカルのPostgreSQLに外部参照テーブルの作成


## １．Ubuntu20.04にPostgreSQLとPostGISをインストール


```shell:
$ sudo apt update
$ sudo apt -y upgrade
```

公開鍵のダウンロード

```shell:
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
```
今回はposgreSQL-11系、PostGIS-2.5 をインストールしたいのでリポジトリを追加

```shell:
$ echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql-pgdg.list > /dev/null
```

再度ベースシステムのアップデート

```shell:
$ sudo apt update
```

PostgreSQLとPostGISをインストール

```shell:
$ sudo apt install postgresql-11-postgis-2.5
```


ちゃんとインストールできているか確認

```shell:
$ dpkg -l | grep post

ii  pgdg-keyring                       2018.2                                all          keyring for apt.postgresq
l.org
ii  postgresql-11                      11.12-1.pgdg20.04+1                   amd64        The World's Most Advanced
 Open Source Relational Database
ii  postgresql-11-postgis-2.5          2.5.5+dfsg-1.pgdg20.04+2              amd64        Geographic objects suppor
t for PostgreSQL 11
ii  postgresql-11-postgis-2.5-scripts  2.5.5+dfsg-1.pgdg20.04+2              all          Geographic objects suppor
t for PostgreSQL 11 -- SQL scripts
ii  postgresql-client-11               11.12-1.pgdg20.04+1                   amd64        front-end programs for Po
stgreSQL 11
ii  postgresql-client-common           226.pgdg20.04+1                       all          manager for multiple Post
greSQL client versions
ii  postgresql-common                  226.pgdg20.04+1                       all          PostgreSQL database-clust
er manager
```



postgresアカウントで、psqlを起動

```shell:
$ sudo -u postgres psql
```

データベースの一覧取得

```:
postgres=# \l

                                List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
```

**データベースの作成**
　※一旦`Ctrl + D`で抜けた後、以下を実行。

```shell:
$ sudo -u postgres createdb test_db
```

再度データベースの一覧取得
　※ちゃんと、`test_db` が追加されている。

```:
postgres=# \l

                                List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 test_db   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
```

**作成したDBを操作するためのユーザーを追加**
　※パスワードの設定を求められる。

```shell:
$ sudo -u postgres createuser -P 【ユーザー名】
Enter password for new role: 
```

**追加したユーザーにDBへの権限を付与**
　※以下のコマンドでは新しく作成した `test_db` に権限付与

postgresアカウントで、psqlを起動

```shell:
$ sudo -u postgres psql
```

```:
postgres=# grant all privileges on database test_db to 【ユーザー名】;

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 test_db   | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =Tc/postgres         +
           |          |          |         |         | postgres=CTc/postgres+
           |          |          |         |         | 【ユーザー名】=CTc/postgres
```

**作成したDBに入ってGIS用の機能拡張**

```shell:
$ sudo -u postgres psql test_db
```

```:
test_db=# CREATE EXTENSION postgis;

CREATE EXTENSION
```


## ２．外部からの接続確認

**外部から接続できる様に設定変更**

`pg_hba.conf`の編集

```shell:
sudo nano /etc/postgresql/11/main/pg_hba.conf
```

```conf:pg_hba,conf
# ----------------------------省略--------------------------------------
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all            【接続許可するIPアドレス】           md5     # ここを追記！
# ----------------------------省略--------------------------------------
```

`postgresql.conf`の編集

```shell:
$ sudo nano /etc/postgresql/11/main/postgresql.conf
```

```conf:postgresql.conf
listen_addresses = '*'
```

**PostgresSQLの起動・停止は以下のコマンド**

```shell:
# 起動
$ sudo service postgresql start

# 停止
$ sudo service postgresql stop

# 再起動
$ sudo service postgresql restart
```

ローカルのPCから接続してみる（自分は `pgAdmin4` を使って接続しました。）
　※GCP上のファイアウォールの設定で`port:5432`を開け忘れていると、接続できないので注意。

test_dbに接続してみて、以下のクエリを実行できれば問題なし！

```sql:
-- スキーマ作成のクエリ
CREATE SCHEMA sample_schema;

-- Geometory型を含むテーブルの作成
CREATE TABLE sample_shema.sptb1 (  
  gid SERIAL PRIMARY KEY
  ,geom GEOMETRY(POINT, 4326)  
);  
```


## ３．ローカルのPostgreSQLに外部参照テーブルの作成

前提として、ローカルPCのOSやPostgresのバージョンは以下の通り。
　・Windows 10 pro
　・PostgreSQL 10系
　・PostGIS 2.5系

**GCP上のPostgreSQL(11系)に以下の様にテーブルを作成**　（参照される側のデータ準備）

```sql:
-- 参照される側のテーブルを作成
CREATE TABLE sample_schema.sptb2 (  
  link_no SERIAL PRIMARY KEY
  ,name VARCHAR(10) not null
  ,age INTEGER not null
); 

-- データも入れておく
INSERT INTO sample_schema.sptb2 VALUES (1, '鈴木花子', 20);
```

**ローカルのPostgreSQL(10系)で以下のクエリを実行**　　※DBは任意なものを選択する。

```sql:
-- DBに対して拡張機能を付けているため、初回のみでOK。　※[IF NOT EXISTS] はまだ存在していない時に実行という意味合い
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 参照先の外部サーバオブジェクトを作成。対象のDBもここで入力。
CREATE SERVER remote_serv1
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '【GCP上のサーバーの外部IP】', port '5432', dbname 'test_db');

-- ローカルのユーザと参照先ユーザ（＆ パスワード）をマッピングする設定
CREATE USER MAPPING
    FOR 【ローカルのPosgres上のユーザー】 SERVER remote_serv1
    OPTIONS (USER 'ext_user', PASSWORD '*******');

-- 外部テーブル定義の設定　　※外部テーブルのカラム定義は参照される側のテーブルと一致していなければならない。
CREATE FOREIGN TABLE 【ローカルPostgresの任意のスキーマ】.sptb2(
  link_no SERIAL
  ,name VARCHAR(10) not null
  ,age INTEGER not null
) SERVER remote_serv1;

-- 外部テーブル定義を削除するときは、以下のコマンド
-- 　※もちろん、外部DBにあるテーブル自体は削除されない。
DROP FOREIGN TABLE 【ローカルPostgresの任意のスキーマ】.sptb2;
```

外部テーブル定義はインポートでもできる。　※こちらの方が余計なミスはなくなるから良いかも。

```sql:
DROP FOREIGN TABLE IF EXISTS 【参照する側(ローカルPostgres)のスキーマ】.sptb2;
IMPORT FOREIGN SCHEMA 【参照される側の対象スキーマ】  -- 今回の場合 [sample_schema]
   LIMIT TO (sptb2) -- 対象のテーブルだけインポート
   FROM SERVER remote_serv1 INTO 【参照する側(ローカルPostgres)のスキーマ】;
```

基本的には [**こちら**](https://www.postgresql.jp/document/10/html/postgres-fdw.html) を参考にしている。


以下のコマンドをローカルPostgres上のDB内で実行して、ちゃんと表示されればOK！

```sql:
SELECT * FROM  【参照する側(ローカルPostgres)のスキーマ】.sptb2;
```


**※ちなみに参照される側の大元のテーブルでレコードが増えても問題なく参照できるが、カラムの構成が変わると、外部テーブル定義も設定し直さなければいけないため注意が必要！**


## おまけ
PostgreSQLをインストールした時点で、すでにデータベースの管理ユーザであるpostgresユーザが作られていたと思うが、このユーザのパスワードは不定のため、このままではパスワードログインができない。
最初にデータベースを作ったりデータベースのユーザを設定するためには、postgresユーザにならなければならないため、そこで、postgresユーザログインできるようにパスワードを設定しましょう。

```shell:
$ sudo passwd postgres
```