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