ubuntu20.04上安装postgresql12+postgis3

ubuntu20.04上安装postgresql12+postgis3
一、安装postgresql12数据库

找到postgresql数据库版本情况

sudo apt-cache search postgresql

找到postgresql-contrib - additional facilities for PostgreSQL (supported version)

sudo apt-get update
sudo apt-get upgrade
apt install postgresql postgresql-contrib

如果报错就配置/etc/apt/sources.list
-----------------------------------------------添加行
deb http://mirrors.aliyun.com/ubuntu/ groovy main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ groovy main restricted universe multiverse

deb http://mirrors.aliyun.com/ubuntu/ groovy-security main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ groovy-security main restricted universe multiverse

deb http://mirrors.aliyun.com/ubuntu/ groovy-updates main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ groovy-updates main restricted universe multiverse

deb http://mirrors.aliyun.com/ubuntu/ groovy-backports main restricted universe multiverse
deb-src http://mirrors.aliyun.com/ubuntu/ groovy-backports main restricted universe multiverse

默认注释了源码镜像以提高 apt update 速度，如有需要可自行取消注释

deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ groovy main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ groovy main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ groovy-updates main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ groovy-updates main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ groovy-backports main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ groovy-backports main restricted universe multiverse
deb https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ groovy-security main restricted universe multiverse
# deb-src https://mirrors.tuna.tsinghua.edu.cn/ubuntu/ groovy-security main restricted universe multiverse

/etc/apt/sources.list.d/pgdg.list
-----------------------------------------------添加行

deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main

将公钥添加至服务器，即终端中输入
apt-key adv --keyserver keyserver.ubuntu.com --recv-keys ‘公钥编码’
安装终端有提示公钥编码的值是多少

apt-key adv --keyserver keyserver.ubuntu.com --recv-keys '7FCC7D46ACCC4CF8'

开始安装postgresql数据库12版本

sudo apt-get update
sudo apt-get upgrade

apt install postgresql postgresql-contrib

启动postgres12控制台并修改postgres用户的密码

sudo -u postgres psql
ALTER USER postgres WITH PASSWORD '12345678';
\q

修改其它机器访问数据库方式：
/etc/postgresql/12/main/postgresql.conf

listen_addresses = '*'	# what IP address(es) to listen on;

注意：listen_addresses = ‘*’ 最前面没有#号

/etc/postgresql/12/main/pg_hba.conf

IPv4 local connections:

host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               trust

---

删除PostgreSQL用户密码

sudo passwd -d postgres

更改密码

sudo -u postgres passwd

输入两次新密码

重启postgresql服务，执行命令

service postgresql restart
service postgresql start

查看所有进程：

ps aux

其中
/usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf

停止postgresql服务

service postgresql stop

查看启动的端口情况

lsof -i:5432
lsof -i:80
netstat -nap|grep port
查看已经连接的服务端口（ESTABLISHED）：netstat -a
查看所有的服务端口（LISTEN，ESTABLISHED）： netstat -ap

lsof -i:5432   # 正确配置后的显示的内容
COMMAND    PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
postgres 23051 postgres    3u  IPv4 213265      0t0  TCP *:postgresql (LISTEN)
postgres 23051 postgres    4u  IPv6 213266      0t0  TCP *:postgresql (LISTEN)

查看端口号对应的系统服务名称

cat /etc/services

---

SELECT datname FROM pg_database;

//查postgresql数据库版本信息

SELECT version();  

PostgreSQL 12.6 (Ubuntu 12.6-0ubuntu0.20.10.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 10.2.0-13ubuntu1) 10.2.0, 64-bit

---

防火墙允许端口5432

ufw allow 5432

二、安装postgis插件
先查找最新postgis插件版本号

sudo apt-cache search postgis

找到postgresql-12-postgis-3

安装postgis插件

sudo apt-get install postgresql-12-postgis-3

---

连接postgresql与postgis(赋予postgresql空间数据库的能力)

(注意是否是在自己想要数据库下创建）

CREATE EXTENSION postgis;

（支持拓扑）

CREATE EXTENSION postgis_topology;

//显示postgis的版本

SELECT postgis_full_version();

---

POSTGIS=“3.0.2 2fb2a18” [EXTENSION] PGSQL=“120” GEOS=“3.8.1-CAPI-1.13.3” PROJ=“7.1.0” LIBXML=“2.9.10” LIBJSON=“0.15” LIBPROTOBUF=“1.3.3” WAGYU=“0.4.3 (Internal)”

其它原postgresql9.4版本数据库安装postgis插件的方法

sudo apt-get install postgresql-9.4-postgis-2.4

—the—end—