配置-Postgresql+Postgis安装+Django连接测试

VM虚拟机

安装centos7 min版本
1.配置网卡
cd /etc/sysconfig/network-scripts
vi ifcfg-ens33
onboot yes
2.安装网络工具
yum install net-tools
3.配置SCI基地服务
vi /etc/ssh/sshd_config
38行
端口22
root登陆yes
4.配置清华yum源
sudo sed -e 's|^mirrorlist=|#mirrorlist=|g' \
         -e 's|^#baseurl=http://mirror.centos.org|baseurl=https://mirrors.tuna.tsinghua.edu.cn|g' \
         -i.bak \
         /etc/yum.repos.d/CentOS-*.repo
sudo yum makecache
5.VM虚拟机瘦身
vmx文件
mainMem.useNamedFile = "FALSE"
虚拟机设置
整理碎片/压缩

安装基础环境

• 安装CMAKE
  sudo yum install -y gcc make subversion gcc-c++ sqlite-devel libxml2 libxml2-devel python-devel numpy swig expat-devel libcurl-devel
• 安装GDAL
  问题描述
  django.core.exceptions.ImproperlyConfigured: Could not find the GDAL library (tried “gdal”, “GDAL”,gdal3.0.0", “gdal2.4.0”, “gdal2.3.0”, “gdal2.2.0”, “gdal2.1.0”, “gdal2.0.0”). Is GDAL installed? IfRARY_PATH in your settings.
  软件包下载
  https://blog.csdn.net/CallmeAdo/article/details/106544465
• GDAL问题
  cp /usr/local/lib/libgdal.so.20* /usr/lib64/

安装Postgresql

1.参考官网

2.国内PG的源配置

3.查看官网安装步骤

https://www.postgresql.org/docs/14/install-binaries.html
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

配置PG

centos7.9起开桌面root登录
vi /etc/gdm/custom.conf
[daemon]
AutomaticLoginEnable=True
AutomaticLogin=root
----------------------------------------------------------------------------------
centos7 yum切换清华源
sudo sed -e 's|^mirrorlist=|#mirrorlist=|g' \
         -e 's|^#baseurl=http://mirror.centos.org|baseurl=https://mirrors.tuna.tsinghua.edu.cn|g' \
         -i.bak \
         /etc/yum.repos.d/CentOS-*.repo
sudo yum makecache
----------------------------------------------------------------------------------
查看防火墙状态
firewall-cmd --state
停止firewall
systemctl stop firewalld.service
禁止firewall开机启动
systemctl disable firewalld.service
----------------------------------------------------------------------------------
pip源
豆瓣：http://pypi.douban.com/simple/
阿里云：http://mirrors.aliyun.com/pypi/simple/
清华：https://pypi.tuna.tsinghua.edu.cn/simple
中国科技大学 https://pypi.mirrors.ustc.edu.cn/simple/
华中理工大学：http://pypi.hustunique.com/
山东理工大学：http://pypi.sdutlinux.org/

mkdir -p ~/.pip
~/.pip/pip.conf
[global]
index-url = https://pypi.doubanio.com/simple/
trusted-host=pypi.doubanio.com
[global]
index-url = https://pypi.tuna.tsinghua.edu.cn/simple/
清华conda源
conda config --set show_channel_urls yes
vim ~/.condarc
channels:
  - defaults
show_channel_urls: true
default_channels:
  - https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/main
  - https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/r
  - https://mirrors.tuna.tsinghua.edu.cn/anaconda/pkgs/msys2
custom_channels:
  conda-forge: https://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud
  msys2: https://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud
  bioconda: https://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud
  menpo: https://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud
  pytorch: https://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud
  pytorch-lts: https://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud
  simpleitk: https://mirrors.tuna.tsinghua.edu.cn/anaconda/cloud
ssl_verify: false
----------------------------------------------------------------------------------
安装
https://www.postgresql.org/download/linux/redhat/
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo yum install -y postgresql14

sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

查看安装路径
find / -name '*postg*'
----------------------------------------------------------------------------------
初始化服务
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb -D /var/lib/pgsql/14/data -U postgres -W -E UTF8 --locale=zh_CN.UTF-8 --debug --data-checksums
----------------------------------------------------------------------------------
启动
systemctl restart postgresql-14.service
systemctl start  postgresql-14.service
systemctl stop  postgresql-14.service
systemctl status postgresql-14.service
----------------------------------------------------------------------------------
登录
登录方式1：
sudo su postgres
psql postgres
登录方式2：
客户端psql --help
psql -h192.168.1.181 -Uroot
----------------------------------------------------------------------------------
基本用法
\du 用户
\l 数据库列表
\c 路径
\d 表
\h create user
----------------------------------------------------------------------------------
修改默认密码
sudo su postgres
psql postgres
sudo -u postgres psql postgres
\password postgres
----------------------------------------------------------------------------------
远程访问
vim /var/lib/pgsql/14/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               md5


vim /var/lib/pgsql/14/data/postgresql.conf
listen_addresses = '0.0.0.0' 
psql -h 192.168.1.181 -U postgres
----------------------------------------------------------------------------------

安装Postgis

1.安装postgis

yum  install wget net-tools epel-release -y
yum search postgis
postgis32_14.x86_64 : Geographic Information Systems Extensions to PostgreSQL
yum install postgis32_14.x86_64
 
postgres=# CREATE EXTENSION postgis;
CREATE EXTENSION
postgres=# select postgis_lib_version();
 postgis_lib_version 
---------------------
 3.2.1
(1 row)

配置Django

1.创建项目

https://docs.djangoproject.com/zh-hans/4.0/intro/tutorial01/
django-admin startproject mysite
python manage.py startapp polls
python manage.py runserver 8080

2.配置Django

ALLOWED_HOSTS = ['127.0.0.1', '0.0.0.0', '192.168.232.138']
INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    'space'
]
DATABASES = {
    'default': {
        'ENGINE': 'django.contrib.gis.db.backends.postgis',
        'NAME': 'postgres',
        'USER': 'postgres',
        'PASSWORD': '123456',
        'HOST': '192.168.232.138',
        'PORT': 5432,
    }
}

3.迁移

python manage.py makemigrations
python manage.py migrate

测试读写数据

1.测试

en = GeoEntity(name='django', point=str(Point(1.0, -1.0)))
en.save()

2.算子测试

select st_distance(
               (select st_astext(point) as a from space_geoentity limit 1),
               (select st_astext(point) as b from space_geoentity limit 1));