1、由于版本问题，导致安装麻烦，卸载-安装-卸载-删除原来不同文件夹同样的文件---
有效参考
postgreSQL15发布，难掩激动快速尝鲜_11672417的技术博客_51CTO博客
Community DL Page (enterprisedb.com)
postgresql数据库报“connections on Unix domain socket "/tmp/.s.PGSQL.5432"?”/temp/.s.pgswl.5432临风赏月的博客-CSDN博客
麒麟信安V3.4 安装PG15的过程_11519070的技术博客_51CTO博客
安装postgres
1、python 错误导致yum不能使用：export LD_LIBRARY_PATH="/usr/lib64:/usr/lib:${LD_LIBRARY_PATH}"
1.1 echo "export LD_LIBRARY_PATH="/usr/lib64:/usr/lib:${LD_LIBRARY_PATH}" >> /etc/profile
1.2 source /etc/profile
2、更新centos源 yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
3、yum update
4、安装pgseql yum install -y postgresql15 postgresql15-server postgresql15-libs postgresql15-contrib
5、安装插件 yum install postgresql-contrib15.1
6、安装net-tools
7、查看端口：netstat -anp | grep 5432
# 导入osm
1、yum install osm2pgsql
2、下载osm数据
3、切换账户 su - postgres
4、创建pgsql的osm database
5、卸载：yum remove postgresql-9.2.24-8.el7_9.x86_64
6、卸载：yum remove postgresql-libs-9.2.24-8.el7_9.x86_64
7、export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib
8、osm2pgsql --slim -d osm --hstore --multi-geometry --number-processes 8 --tag-transform-script /home/osm/openstreetmap-carto-4.20.0/openstreetmap-carto.lua --style /home/osm/openstreetmap-carto-4.20.0/openstreetmap-carto.style /home/osm/china-latest.osm.pbf
sudo passwd postgres
su – postgres
psql -c "ALTER USER postgres WITH PASSWORD ‘postgres';"
systemctl stop postgresql-15
systemctl status postgresql-15
systemctl start postgresql-15
systemctl enable postgresql-15
/usr/local/pgsql-15.1/bin
pg_ctl register -N PostgreSQL -D "/var/lib/pgsql/15/data"
export HGDB_HOME=/usr/local/pgsql-15.1
export PATH=$PATH:$HGDB_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql-15.1/lib
插件安装
Docker安装
ubuntu20.04
# 按照官网安装，直接安装15即可
sudo apt update
sudo apt install postgresql postgresql-contrib
# 查看版本
sudo -u postgres psql -c "SELECT version();"
#
查看安装状态
ps aux | grep postgres
netstat -npl | grep postgres
ss -nlt | grep 5432
# 重启服务
sudo service postgresql restart
su - postgres -c "/usr/lib/postgresql/15/bin --username=postgres --encoding=UTF8 --lc-collate=C --lc-ctype=en_US.utf8 --data-checksums -D /home/lzg/data"
用户需要输入密码：
su - postgres 如果报错：su: Authentication failure
则：sudo passwd postgres #sudo passwd -d postgres
#两次输入密码。。 1234
#再次使用该用户 1234
#再次进入即可：su - postgres
卸载
sudo find / -name "postgres*"|xargs rm -f
初始化数据库
sudo rm -rf /var/lib/postgresql/15/main
sudo mkdir /var/lib/postgresql/15/main
sudo chmod 775 /var/lib/postgresql/15/main
sudo chown postgres /var/lib/postgresql/15/main
cd /usr/lib/postgresql/15/bin/
./initdb --locale $LANG -E UTF8 -D /var/lib/postgresql/15/main
./pg_ctl -D /var/lib/postgresql/15/main -l logfile start
# "local" is for Unix domain socket connections only
local all all peer
#docker 安装
sudo apt update
sudo apt install apt-transport-https ca-certificates curl gnupg-agent software-properties-common
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt update
sudo apt install docker-ce docker-ce-cli containerd.io
sudo apt update
apt list -a docker-ce
sudo systemctl status docker
sudo usermod -aG docker $USER
使用
1、数据卷重用
sudo docker volume create pgdata
sudo docker volume inspect pgdata
2、启动docker
docker run --name postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -v pgdata:/var/lib/postgresql/data -d postgres
sudo docker run -e POSTGRES_PASSWORD=postgres -p 5432:5432 -v pgdata:/var/lib/postgresql/data ubuntu/postgres
sudo docker exec -it eda7995239c0 /bin/bash
run，创建并运行一个容器；
--name，指定创建的容器的名字；
-e POSTGRES_PASSWORD=password，设置环境变量，指定数据库的登录口令为password；
-p 54321:5432，端口映射将容器的5432端口映射到外部机器的54321端口
进入容器
apt-get update && apt-get install lsb-core
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
3、安装插件
apt install postgresql-14-pgrouting
apt install postgresql-14-postgis-3
4、docker 备份
sudo docker ps 获得正在运行的id:80d4159c23ad
sudo docker commit -a "gg <xxx@tsari.tsinghua.edu.cn>" -m "pg14,add posgis pgrouting" 80d4159c23ad postgres:v2
5、启动
sudo docker run --name postgres -e POSTGRES_PASSWORD=postgres -p 5432:5432 -v pgdata:/var/lib/postgresql/data postgres:v2
后面
sudo docker start postgres
6、测试插件：
CREATE EXTENSION PostGIS
CREATE EXTENSION pgRouting
hstore安装
需要安装contrib，使用apt-install安装，postgresql15-contrib
如果安装失败，则
- wget postgresql15.1源码
- 解压缩
- ./configure --prefix=$PWD/install --with-pgport=5432 --with-python --with-contrib --without-readline
- cd contrib
- make all$make install
- 生成的文件拷贝到安装目录/usr/local/pgsql-15.1/ （里面的bin，lib,share)
- create extension hstore;测试成功
