在Ubuntu20.04上安装PostgreSQL和PostGIS
首先
我正在尝试使用QGIS的qwc2进行Web发布，但是当我将在本地创建的QGIS项目上传到服务器时，由于服务器本身是在GCP上构建的，所以存在数据引用不存的情况。（因为QGIS服务器和本地PC不在同一网络上，这是理所当然的事情…）
因此，我在服务器上搭建了PostgreSQL，并尝试以从本地的PostgreSQL进行外部引用的方式来保持相同的表结构，我想记录下其基本内容。
运行环境
【个人电脑操作系统】
– Ubuntu20.04 LTS
【软件版本】
– PostgreSQL 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 -
我想要安装PostgreSQL-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 |
添加用于操作已创建的数据库的用户
※需要设置密码。
$ sudo -u postgres createuser -P 【ユーザー名】
Enter password for new role:
给添加的用户授予对数据库的权限
* 在下面的命令中授予新创建的 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
将开发好的数据库添加了用于GIS的功能扩展。
$ sudo -u postgres psql test_db
test_db=# CREATE EXTENSION postgis;
CREATE EXTENSION
2. 外部连接确认
更改设置以允许外部连接。
编辑pg_hba.conf文件。
sudo nano /etc/postgresql/11/main/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
listen_addresses = '*'
以下为启动和停止PostgreSQL的命令。
# 起動
$ sudo service postgresql start
# 停止
$ sudo service postgresql stop
# 再起動
$ sudo service postgresql restart
我尝试从本地的电脑连接（我使用pgAdmin4进行连接）。
※ 如果在GCP上的防火墙设置中忘记打开端口5432，则无法连接，请注意。
如果能够连接到test_db并执行以下查询，则没有问题！
-- スキーマ作成のクエリ
CREATE SCHEMA sample_schema;
-- Geometory型を含むテーブルの作成
CREATE TABLE sample_shema.sptb1 (
gid SERIAL PRIMARY KEY
,geom GEOMETRY(POINT, 4326)
);
在本地的PostgreSQL中创建外部参照表。
作为前提条件，本地计算机的操作系统和Postgres版本如下：
– Windows 10专业版
– 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に対して拡張機能を付けているため、初回のみで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数据库中运行以下命令，如果能正确显示，则表示OK！
SELECT * FROM 【参照する側(ローカルPostgres)のスキーマ】.sptb2;
顺便提一下，如果被引用的原始表增加了记录，也没有问题可以进行引用，但是如果列的结构发生了变化，外部表的定义也必须重新设置，所以需要注意！
给个小赠品
在安装PostgreSQL时，默认已经创建了一个名为postgres的数据库管理用户，但是由于该用户的密码是未知的，因此不能使用密码登录。为了首次创建数据库或设置数据库用户，必须先转换为postgres用户，因此需要设置postgres用户的密码，以便进行登录。
$ sudo passwd postgres
