LoginSignup
5
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@smiler5617

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

Last updated at Posted at 2021-07-21

はじめに

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をインストール

$ sudo apt update
$ sudo apt -y upgrade

公開鍵のダウンロード

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

今回はposgreSQL-11系、PostGIS-2.5 をインストールしたいのでリポジトリを追加

$ 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

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

$ sudo apt update

PostgreSQLとPostGISをインストール

$ sudo apt install postgresql-11-postgis-2.5

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

$ 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を起動

$ 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で抜けた後、以下を実行。

$ 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を操作するためのユーザーを追加
　※パスワードの設定を求められる。

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

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

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

$ 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用の機能拡張

$ sudo -u postgres psql test_db

test_db=# CREATE EXTENSION postgis;

CREATE EXTENSION

２．外部からの接続確認

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

pg_hba.confの編集

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

postgresql.confの編集

$ sudo nano /etc/postgresql/11/main/postgresql.conf
postgresql.conf
listen_addresses = '*'

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

# 起動
$ sudo service postgresql start

# 停止
$ sudo service postgresql stop

# 再起動
$ sudo service postgresql restart

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

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

-- スキーマ作成のクエリ
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系)に以下の様にテーブルを作成　（参照される側のデータ準備）

-- 参照される側のテーブルを作成
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は任意なものを選択する。

-- 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;

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

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

基本的には こちら を参考にしている。

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

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

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

おまけ

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

$ sudo passwd postgres
5
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?