pg、mysql_fdw、postgis安装

pg

wget https://ftp.postgresql.org/pub/source/v14.4/postgresql-14.4.tar.gz  --no-check-certificate
yum install -y gcc xml2 readlinedev* zlib perldev* pythondev* bison flexs
yum install readline-devel
tar xf postgresql-14.4.tar.gz
./configure --prefix=/usr/local/postgresql
make  && make  install
mkdir  /usr/local/postgresql/data
adduser   postgres
chown -R postgres.postgres  /usr/local/postgresql/data/
su  postgres
/usr/local/postgresql/bin/initdb -D /usr/local/postgresql/data/
/usr/local/postgresql/bin/pg_ctl -D /usr/local/postgresql/data/ -l logfile   start
​
#修改pg_hba.conf
host  all all 0.0.0.0/0  md5
#修改postgres.conf
listen_address='*'
# pg启停
关闭数据库服务有三种模式：
​
SIGTERM，smart shutdown：阻止新连接，保持现有连接，仅当所有会话退出后才会关闭；
SIGINT，fast shutdown：阻止新连接，中断事务，断掉会话；
SIGQUIT，immediate shutdown：断电；
可使用命令pg_ctl stop -m smart|fast|immediate关闭数据库服务。
​

pg systemd

[Unit]
Description=postgreSQL Server [Service]
User=postgres
Group=postgres
Type=forking
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/postgresql/bin/pg_ctl -D /usr/local/postgresql/data/    start
LimitNOFILE = 65535
Restart=on-failure
RestartSec=3
RestartPreventExitStatus=1
PrivateTmp=false
[Install]
WantedBy=multi-user.target 

mysql_fdw安装与使用

##安装
wget https://github.com/EnterpriseDB/mysql_fdw/archive/refs/tags/REL-2_8_0.tar.gz
tar xf REL-2_8_0.tar.gz
cd mysql_fdw-REL-2_8_0/
export PATH=/usr/local/postgres/bin/:$PATH
export PATH=/usr/local/mysql/bin/:$PATH
make USE_PGXS=1
make USE_PGXS=1 install 
##使用 
create  extension mysql_fdw;
CREATE SERVER server_name FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '192.168.1.31', port '4000');
create  user mapping for postgres server  server_name options(username 'test',password '123456');
grant usage on foreign server server_name to user_name;
CREATE FOREIGN   TABLE aa (
  id int,
  score decimal(4,2) DEFAULT NULL
) server server_name  options (dbname 'yangjx',table_name 'aa');

postgis-3.2.1安装

## 安装
wget http://ftp.gnu.org/pub/gnu/libiconv/libiconv-1.11.tar.gz
tar xf libiconv-1.11.tar.gz
cd libiconv-1.11
./configure --prefix=/usr/local/libiconv
make && make install
yum -y install libxml2
yum -y install libxml2-devel
wget http://download.osgeo.org/geos/geos-3.7.3.tar.bz2
yum install bzip2 -y
bzip2 -d geos-3.7.3.tar.bz2
tar xf geos-3.7.3.tar
./configure  --prefix=/usr/local/geos
make && make install
wget https://download.osgeo.org/proj/proj-5.2.0.tar.gz  --no-check-certificate
tar xf proj-5.2.0.tar.gz
./configure  --prefix=/usr/local/proj
make && make install
yum install postgresql-libs.x86_64
wget https://download.osgeo.org/gdal/2.4.4/gdal-2.4.4.tar.gz  --no-check-certificate
tar xf gdal-2.4.4.tar.gz
./configure  --prefix=/usr/local/gdal
make && make  install
wget  https://download.osgeo.org/postgis/source/postgis-3.2.1.tar.gz  --no-check-certificate
./configure --with-libiconv=/usr/local/libiconv  --without-protobuf  --with-geosconfig=/usr/local/geos/bin/geos-config    --with-projdir=/usr/local/proj/  --with-gdalconfig=/usr/local/gdal/bin/gdal-config
 make && make install
 
vi /etc/ld.so.conf
/usr/local/mysql/lib
/usr/local/postgresql/lib/
/usr/local/gdal/lib
/usr/local/proj/lib
/usr/local/geos/lib
/usr/local/lib
ldconfig