PostGIS on Ubuntu

安装

https://postgis.net/install/
Ubuntu直接看这篇：https://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS24UbuntuPGSQL10Apt

postgresql

sudo apt install postgresql

sudo service postgresql status
1月 23 15:34:29 uos-PC systemd[1]: Starting PostgreSQL RDBMS...
1月 23 15:34:29 uos-PC systemd[1]: Started PostgreSQL RDBMS.

在终端中，以 postgres 用户身份登录 psql 控制台：

sudo -u postgres psql

postgis

sudo apt install postgis

验证安装成功：https://postgis.net/documentation/getting_started/

CREATE EXTENSION postgis;
SELECT PostGIS_Full_Version();

或：

SELECT * FROM pg_available_extensions WHERE name = 'postgis';

SELECT name, default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%' or name LIKE 'address%';

麒麟V10安装问题

致命错误:  私钥文件"/etc/ssl/private/ssl-cert-snakeoil.key"具有由所在组或全局范围访问的权限

解决办法：

sudo chown postgres:postgres /etc/ssl/private/ssl-cert-snakeoil.key
sudo chmod 600 /etc/ssl/private/ssl-cert-snakeoil.key

sudo systemctl restart postgresql
sudo systemctl status postgresql

See: https://zhuanlan.zhihu.com/p/694223833

修改密码

https://www.cnblogs.com/kaituorensheng/p/4735191.html
进入psql命令行后修改：

sudo -u postgres psql

alter user postgres with password 'passwd'; 
\q

普通用户登录

psql -Upostgres
psql: 致命错误:  对用户"postgres"的对等认证失败

修改pg_hba.conf文件
sudo vi /etc/postgresql/11/main/pg_hba.conf
sudo service postgresql restart
psql -Upostgres

开放TCP

查找postgresql.conf文件

root@ubuntu:~# locate postgresql.conf
/etc/postgresql/14/main/postgresql.conf
/root/postgres-master/src/backend/utils/misc/postgresql.conf.sample
/usr/local/pgsql/data/postgresql.conf
/usr/share/postgresql/14/postgresql.conf.sample

修改：

listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)

或者使用psql：

sudo -u postgres psql
ALTER SYSTEM SET listen_addresses='*'; 
\q

开放登录

查找pg_hba.conf文件

root@ubuntu:~# locate pg_hba.conf
/etc/postgresql/14/main/pg_hba.conf
/root/postgres-master/src/backend/libpq/pg_hba.conf.sample
/usr/local/pgsql/data/pg_hba.conf
/usr/share/postgresql/14/pg_hba.conf.sample

添加规则：

# 明文认证，有的psql客户端不支持高级认证方式
host    all             all              0.0.0.0/0              password
# 与明文对立的有md5等方式
host    all             all              0.0.0.0/0              md5
# 直接信任某IP，无需验证密码
host    all             all              x.x.x.x/0              trust

重启

systemctl restart postgresql.service

see

http://www.project-open.com/en/howto-postgresql-port-secure-remote-access