01.在linux基础上配置PG所需要环境。
[root@es1 postgresql]# ll
total 26048
-rw-r--r-- 1 root root 26669211 Jul 6 11:37 postgresql-12.2.tar.gz
[root@es1 postgresql]# pwd
/data/postgresql
2.启动一个带超级权限的基本容器。
--privileged:带超级权限
docker run -it -d --name postgresql-base -h postgresql-base --privileged 192.168.1.11:443/myharbor/centos:7.9
docker exec -it postgresql-base bash
--容器内：
vi /etc/sysctl.conf 配置
vi /etc/selinux/config配置
3.安装配置pg12.2 版本
--192.168.1.7：
--容器外部宿主机
docker exec -it postgresql-base mkdir -p /postgresql/soft
docker cp /data/postgresql/postgresql-12.2.tar.gz postgresql-base:/postgresql/soft
mount /dev/cdrom /mnt
--编译及安装PG
cd /postgresql
tar -zxvf /postgresql/soft/postgresql-12.2.tar.gz
cd postgresql-12.2
./configure
make && make install
rm -rf /postgresql/soft/*
4.初始化数据库
--环境变量准备
su - pgsql
vi ~/.bash_profile
--初始化数据库。
/postgresql/pg12/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres -X /postgresql/pgxlog5432
--备份初始化文件。
vi /postgresql/pgdata/postgresql.conf
--让所有主机都可以访问。
cat >> /postgresql/pgdata/pg_hba.conf << EOF
host all all 0.0.0.0/0 md5
EOF
exit
su - pgsql
pg_ctl start
pg_ctl stop
[pgsql@postgresql-base:/home/pgsql]$pg_ctl start
waiting for server to start....2024-07-06 11:51:00.190 CST [9386] LOG: 00000: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-07-06 11:51:00.190 CST [9386] LOCATION: PostmasterMain, postmaster.c:998
2024-07-06 11:51:00.190 CST [9386] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2024-07-06 11:51:00.190 CST [9386] LOCATION: StreamServerPort, pqcomm.c:593
2024-07-06 11:51:00.193 CST [9386] LOG: 00000: listening on Unix socket "./.s.PGSQL.5432"
2024-07-06 11:51:00.193 CST [9386] LOCATION: StreamServerPort, pqcomm.c:587
2024-07-06 11:51:00.226 CST [9386] LOG: 00000: redirecting log output to logging collector process
2024-07-06 11:51:00.226 CST [9386] HINT: Future log output will appear in directory "pg_log".
2024-07-06 11:51:00.226 CST [9386] LOCATION: SysLogger_Start, syslogger.c:675
done
server started
[pgsql@postgresql-base:/home/pgsql]$
[pgsql@postgresql-base:/home/pgsql]$
[pgsql@postgresql-base:/home/pgsql]$pg_ctl stop
waiting for server to shut down.... done
server stopped
or:
nohup /postgresql/pg12/bin/postgres -D /postgresql/pgdata >/postgresql/pg12/pglog.out 2>&1 &
[pgsql@postgresql-base:/home/pgsql]$tail -100f /postgresql/pg12/pglog.out
nohup: ignoring input
2024-07-06 11:51:19.507 CST [9396] LOG: 00000: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-07-06 11:51:19.507 CST [9396] LOCATION: PostmasterMain, postmaster.c:998
2024-07-06 11:51:19.507 CST [9396] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2024-07-06 11:51:19.507 CST [9396] LOCATION: StreamServerPort, pqcomm.c:593
2024-07-06 11:51:19.509 CST [9396] LOG: 00000: listening on Unix socket "./.s.PGSQL.5432"
2024-07-06 11:51:19.509 CST [9396] LOCATION: StreamServerPort, pqcomm.c:587
2024-07-06 11:51:19.542 CST [9396] LOG: 00000: redirecting log output to logging collector process
2024-07-06 11:51:19.542 CST [9396] HINT: Future log output will appear in directory "pg_log".
2024-07-06 11:51:19.542 CST [9396] LOCATION: SysLogger_Start, syslogger.c:675
5.修改为有密码的访问
psql
\password postgres （输入两次密码）
ALTER USER postgres WITH password 'rootroot';
pg_ctl stop
pg_ctl start
psql -h 127.0.0.1 -p 5432 -U postgres
Password for user postgres: rootroot
psql (12.2)
Type "help" for help.
postgres=#
6.提交基本的容器
--重启下容器。
docker stop postgresql-base
docker start postgresql-base
--提交容器为：images 正在运行的容器的名字。
docker commit postgresql-base postgresql-base:db12.2
docker images
[root@es1 bin]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
postgresql-base db12.2 f4fb15d58ebc 6 seconds ago 849MB
192.168.1.11:443/myharbor/centos 7.9 77596ed99450 25 hours ago 490MB
docker rm -f postgresql-base
7.配置 POSTGIS
01.启动新的images容器。
docker run -it -d --name postgresql-base -h postgresql-base -p 5432:5432 --privileged postgresql-base:db12.2
su - pgsql
pg_ctl start
[pgsql@postgresql-base:/home/pgsql]$pg_ctl start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2024-07-06 12:30:17.303 CST [122] LOG: 00000: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-07-06 12:30:17.303 CST [122] LOCATION: PostmasterMain, postmaster.c:998
2024-07-06 12:30:17.303 CST [122] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2024-07-06 12:30:17.303 CST [122] LOCATION: StreamServerPort, pqcomm.c:593
2024-07-06 12:30:17.306 CST [122] LOG: 00000: listening on Unix socket "./.s.PGSQL.5432"
2024-07-06 12:30:17.306 CST [122] LOCATION: StreamServerPort, pqcomm.c:587
2024-07-06 12:30:17.347 CST [122] LOG: 00000: redirecting log output to logging collector process
2024-07-06 12:30:17.347 CST [122] HINT: Future log output will appear in directory "pg_log".
2024-07-06 12:30:17.347 CST [122] LOCATION: SysLogger_Start, syslogger.c:675
. done
server started
exit;
--容器内。
source /home/pgsql/.bash_profile
env |grep PG
--再安装这个。
--容器内操作。
yum -y install epel-release
yum list postgis*
postgis.x86_64 2.0.7-2.el7 epel
postgis-docs.x86_64 2.0.7-2.el7 epel
postgis-utils.x86_64 2.0.7-2.el7 epel
postgis25_12.x86_64 2.5.5-4.rhel7 pgdg12
postgis25_12-client.x86_64 2.5.5-4.rhel7 pgdg12
postgis25_12-devel.x86_64 2.5.5-4.rhel7 pgdg12
postgis25_12-docs.x86_64 2.5.5-4.rhel7 pgdg12
postgis25_12-gui.x86_64 2.5.5-4.rhel7 pgdg12
postgis25_12-utils.x86_64 2.5.5-4.rhel7 pgdg12
postgis30_12.x86_64 3.0.7-1.rhel7 pgdg12
postgis30_12-client.x86_64 3.0.7-1.rhel7 pgdg12
postgis30_12-devel.x86_64 3.0.7-1.rhel7 pgdg12
postgis30_12-docs.x86_64 3.0.7-1.rhel7 pgdg12
postgis30_12-gui.x86_64 3.0.7-1.rhel7 pgdg12
postgis30_12-utils.x86_64 3.0.7-1.rhel7 pgdg12
postgis30_13.x86_64 3.0.7-1.rhel7 pgdg13
postgis30_13-client.x86_64 3.0.7-1.rhel7 pgdg13
postgis30_13-devel.x86_64 3.0.7-1.rhel7 pgdg13
postgis30_13-docs.x86_64 3.0.7-1.rhel7 pgdg13
postgis30_13-gui.x86_64 3.0.7-1.rhel7 pgdg13
postgis30_13-utils.x86_64 3.0.7-1.rhel7 pgdg13
postgis31_12.x86_64 3.1.9-1.rhel7 pgdg12
postgis31_12-client.x86_64 3.1.9-1.rhel7 pgdg12
postgis31_12-devel.x86_64 3.1.9-1.rhel7 pgdg12
postgis31_12-docs.x86_64 3.1.9-1.rhel7 pgdg12
postgis31_12-gui.x86_64 3.1.9-1.rhel7 pgdg12
postgis31_12-llvmjit.x86_64 3.1.9-1.rhel7 pgdg12
postgis31_12-utils.x86_64 3.1.9-1.rhel7 pgdg12
postgis31_13.x86_64 3.1.9-1.rhel7 pgdg13
postgis31_13-client.x86_64 3.1.9-1.rhel7 pgdg13
postgis31_13-devel.x86_64 3.1.9-1.rhel7 pgdg13
postgis31_13-docs.x86_64 3.1.9-1.rhel7 pgdg13
postgis31_13-gui.x86_64 3.1.9-1.rhel7 pgdg13
postgis31_13-llvmjit.x86_64 3.1.9-1.rhel7 pgdg13
postgis31_13-utils.x86_64 3.1.9-1.rhel7 pgdg13
postgis31_14.x86_64 3.1.9-1.rhel7 pgdg14
postgis31_14-client.x86_64 3.1.9-1.rhel7 pgdg14
postgis31_14-devel.x86_64 3.1.9-1.rhel7 pgdg14
postgis31_14-docs.x86_64 3.1.9-1.rhel7 pgdg14
postgis31_14-gui.x86_64 3.1.9-1.rhel7 pgdg14
postgis31_14-llvmjit.x86_64 3.1.9-1.rhel7 pgdg14
postgis31_14-utils.x86_64 3.1.9-1.rhel7 pgdg14
postgis32_12.x86_64 3.2.5-1.rhel7 pgdg12
postgis32_12-client.x86_64 3.2.5-1.rhel7 pgdg12
postgis32_12-devel.x86_64 3.2.5-1.rhel7 pgdg12
postgis32_12-docs.x86_64 3.2.5-1.rhel7 pgdg12
postgis32_12-gui.x86_64 3.2.5-1.rhel7 pgdg12
postgis32_12-llvmjit.x86_64 3.2.5-1.rhel7 pgdg12
postgis32_12-utils.x86_64 3.2.5-1.rhel7 pgdg12
postgis32_13.x86_64 3.2.5-1.rhel7 pgdg13
postgis32_13-client.x86_64 3.2.5-1.rhel7 pgdg13
postgis32_13-devel.x86_64 3.2.5-1.rhel7 pgdg13
postgis32_13-docs.x86_64 3.2.5-1.rhel7 pgdg13
postgis32_13-gui.x86_64 3.2.5-1.rhel7 pgdg13
postgis32_13-llvmjit.x86_64 3.2.5-1.rhel7 pgdg13
postgis32_13-utils.x86_64 3.2.5-1.rhel7 pgdg13
postgis32_14.x86_64 3.2.5-1.rhel7 pgdg14
postgis32_14-client.x86_64 3.2.5-1.rhel7 pgdg14
postgis32_14-devel.x86_64 3.2.5-1.rhel7 pgdg14
postgis32_14-docs.x86_64 3.2.5-1.rhel7 pgdg14
postgis32_14-gui.x86_64 3.2.5-1.rhel7 pgdg14
postgis32_14-llvmjit.x86_64 3.2.5-1.rhel7 pgdg14
postgis32_14-utils.x86_64 3.2.5-1.rhel7 pgdg14
postgis32_15.x86_64 3.2.5-1.rhel7 pgdg15
postgis32_15-client.x86_64 3.2.5-1.rhel7 pgdg15
postgis32_15-devel.x86_64 3.2.5-1.rhel7 pgdg15
postgis32_15-docs.x86_64 3.2.5-1.rhel7 pgdg15
postgis32_15-gui.x86_64 3.2.5-1.rhel7 pgdg15
postgis32_15-llvmjit.x86_64 3.2.5-1.rhel7 pgdg15
postgis32_15-utils.x86_64 3.2.5-1.rhel7 pgdg15
postgis33_12.x86_64 3.3.3-1.rhel7 pgdg12
postgis33_12-client.x86_64 3.3.3-1.rhel7 pgdg12
postgis33_12-devel.x86_64 3.3.3-1.rhel7 pgdg12
postgis33_12-docs.x86_64 3.3.3-1.rhel7 pgdg12
postgis33_12-gui.x86_64 3.3.3-1.rhel7 pgdg12
postgis33_12-llvmjit.x86_64 3.3.3-1.rhel7 pgdg12
postgis33_12-utils.x86_64 3.3.3-1.rhel7 pgdg12
postgis33_13.x86_64 3.3.3-1.rhel7 pgdg13
postgis33_13-client.x86_64 3.3.3-1.rhel7 pgdg13
postgis33_13-devel.x86_64 3.3.3-1.rhel7 pgdg13
postgis33_13-docs.x86_64 3.3.3-1.rhel7 pgdg13
postgis33_13-gui.x86_64 3.3.3-1.rhel7 pgdg13
postgis33_13-llvmjit.x86_64 3.3.3-1.rhel7 pgdg13
postgis33_13-utils.x86_64 3.3.3-1.rhel7 pgdg13
postgis33_14.x86_64 3.3.3-1.rhel7 pgdg14
postgis33_14-client.x86_64 3.3.3-1.rhel7 pgdg14
postgis33_14-devel.x86_64 3.3.3-1.rhel7 pgdg14
postgis33_14-docs.x86_64 3.3.3-1.rhel7 pgdg14
postgis33_14-gui.x86_64 3.3.3-1.rhel7 pgdg14
postgis33_14-llvmjit.x86_64 3.3.3-1.rhel7 pgdg14
postgis33_14-utils.x86_64 3.3.3-1.rhel7 pgdg14
postgis33_15.x86_64 3.3.3-1.rhel7 pgdg15
postgis33_15-client.x86_64 3.3.3-1.rhel7 pgdg15
postgis33_15-devel.x86_64 3.3.3-1.rhel7 pgdg15
postgis33_15-docs.x86_64 3.3.3-1.rhel7 pgdg15
postgis33_15-gui.x86_64 3.3.3-1.rhel7 pgdg15
postgis33_15-llvmjit.x86_64 3.3.3-1.rhel7 pgdg15
postgis33_15-utils.x86_64 3.3.3-1.rhel7 pgdg15
yum -y install postgis31_12* --exclude postgresql*
--安装成功后，复制以下目录，不要替换。
awk 'BEGIN {cmd="cp -ri /usr/pgsql-12/lib/* /postgresql/pg12/lib/ "; print "n" |cmd; }'
awk 'BEGIN {cmd="cp -ri /usr/pgsql-12/share/extension/* /postgresql/pg12/share/extension/ "; print "n" |cmd; }'
--卸载软件。
yum -y remove postgresql*
yum clean all
rm -rf /var/cache/yum/*
*/
su - pgsql
pg_ctl stop
pg_ctl start
[root@postgresql-base /]# su - pgsql
Last login: Sat Jul 6 12:30:16 CST 2024 on pts/1
[pgsql@postgresql-base:/home/pgsql]$pg_ctl stop
waiting for server to shut down.... done
server stopped
[pgsql@postgresql-base:/home/pgsql]$pg_ctl start
waiting for server to start....2024-07-06 12:45:29.030 CST [852] LOG: 00000: starting PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2024-07-06 12:45:29.030 CST [852] LOCATION: PostmasterMain, postmaster.c:998
2024-07-06 12:45:29.030 CST [852] LOG: 00000: listening on IPv4 address "0.0.0.0", port 5432
2024-07-06 12:45:29.030 CST [852] LOCATION: StreamServerPort, pqcomm.c:593
2024-07-06 12:45:29.033 CST [852] LOG: 00000: listening on Unix socket "./.s.PGSQL.5432"
2024-07-06 12:45:29.033 CST [852] LOCATION: StreamServerPort, pqcomm.c:587
2024-07-06 12:45:29.078 CST [852] LOG: 00000: redirecting log output to logging collector process
2024-07-06 12:45:29.078 CST [852] HINT: Future log output will appear in directory "pg_log".
2024-07-06 12:45:29.078 CST [852] LOCATION: SysLogger_Start, syslogger.c:675
done
server started
--配置POSTGIS；
psql -h 127.0.0.1 -p 5432 -U postgres
--Enabling PostGIS:
-- Enable PostGIS (as of 3.0 contains just geometry/geography)
CREATE EXTENSION postgis;
-- enable raster support (for 3+)
CREATE EXTENSION postgis_raster;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;
--------------------------------------
--升级POSTGIS;
--Upgrading PostGIS:
-- Upgrade PostGIS (includes raster) to latest version
ALTER EXTENSION postgis UPDATE;
--NOTICE: version "3.1.9" of extension "postgis" is already installed
ALTER EXTENSION postgis_topology UPDATE;
--NOTICE: version "3.1.9" of extension "postgis_topology" is already installed
-- Upgrade PostGIS (includes raster)
ALTER EXTENSION postgis UPDATE TO "3.2.1";
-- Upgrade Topology
ALTER EXTENSION postgis_topology UPDATE TO "3.2.1";
-- Upgrade US Tiger Geocoder
ALTER EXTENSION postgis_tiger_geocoder UPDATE TO "3.2.1";
--------------------------------------
8.测试POSTGIS
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
geom GEOMETRY(Point, 26910),
name VARCHAR(128)
);
-- Add a spatial index
CREATE INDEX mytable_gix ON mytable USING GIST (geom);
-- Add a point
INSERT INTO mytable (geom) VALUES (
ST_GeomFromText('POINT(0 0)', 26910)
);
-- Query for nearby points
SELECT id, name
FROM mytable
WHERE ST_DWithin(geom,ST_GeomFromText('POINT(0 0)', 26910),1000);
id | name
----+------
1 |
(1 row)
select name from pg_available_extensions;
name
------------------------------
plpgsql
address_standardizer
address_standardizer_data_us
adminpack
amcheck
autoinc
bloom
btree_gin
btree_gist
citext
cube
dblink
dict_int
dict_xsyn
earthdistance
file_fdw
fuzzystrmatch
hstore
hstore_plperl
hstore_plperlu
insert_username
intagg
intarray
isn
jsonb_plperl
jsonb_plperlu
lo
ltree
moddatetime
pageinspect
pg_buffercache
pgcrypto
pg_freespacemap
pg_prewarm
pgrowlocks
pg_stat_statements
pgstattuple
pg_trgm
pg_visibility
postgis
postgis_raster
postgis_sfcgal
postgis_tiger_geocoder
postgis_topology
postgres_fdw
refint
seg
sslinfo
tablefunc
tcn
tsm_system_rows
tsm_system_time
unaccent
uuid-ossp
xml2
(55 rows)
postgres=# SELECT PostGIS_full_version();
9.制作PG+POSTGIS组合的镜像
--重启pgsql; 然后制作新的镜像。
su - pgsql
pg_ctl stop
pg_ctl start
--1.11.9.3 创建新镜像：PG+gis;
pg_ctl stop
docker stop postgresql-base
docker start postgresql-base
--提交一个新的镜像。postgresql:12.2
[root@es1 bin]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
postgresql-base db12.2 f4fb15d58ebc 37 minutes ago 849MB
192.168.1.11:443/myharbor/centos 7.9 77596ed99450 25 hours ago 490MB
docker commit postgresql-base postgresql:12.2
docker rm -f postgresql-base
cd /data/postgresql
cat > dockerfile-postgresql.yml << EOF
FROM postgresql:12.2
MAINTAINER SSPU.net.cn,qq113257174
WORKDIR /postgresql/
VOLUME ["/postgresql/pgdata"]
EXPOSE 5432
CMD ["su","-","pgsql","-c","/postgresql/pg12/bin/postgres -D /postgresql/pgdata"]
EOF
--创建新容器
cat > docker-build-postgresql.sh << EOF
#!/bin/bash
docker build --rm -t postgresql-gis:12.2 -f dockerfile-postgresql.yml .
EOF
--数据持久化
docker volume create pgdata
ls /data/docker/data/volumes/pgdata/_data/
--容器的目录挂载到：/postgresql/pgdata;
docker run -it -d --name pgdata5432 -h pgdata5432 --mount src=pgdata,dst=/postgresql/pgdata \
-p 5432:5432 postgresql-gis:12.2
docker ps
docker exec -it pgdata5432 bash
su - pgsql
psql -h 192.168.1.7 -p 5432 -U postgres
[pgsql@pgdata5432:/home/pgsql]$psql -h 192.168.1.64 -p 5432 -U postgres
Password for user postgres:
psql (12.2)
Type "help" for help.
create user SSPUDB with password 'SSPU123' nocreatedb;
create database SSPUDB with owner=SSPUDB template=template0 encoding='UTF8' lc_collate='C' lc_ctype='C' connection limit = -1;
\q
psql -h 192.168.1.7 -p 5432 -U SSPUDB -d SSPUDB
create table SSPU (name varchar(50));
insert into SSPU values('SSPU.net.cn');
insert into SSPU values('wx');
insert into SSPU values('SSPUDB-com');
insert into SSPU values('wx-gzh');
insert into SSPU values('SSPUDB_com');
insert into SSPU values('oracle');
insert into SSPU values('mysql');
commit;
select * from SSPU;
sspudb=> WARNING: there is no transaction in progress
COMMIT
sspudb=> name
--------------
SSPU.net.cn
wx
SSPUDB-com
wx-gzh
SSPUDB_com
oracle
mysql
nosql
docker rm -f pgdata5432
上传镜像到镜像服务器，以后其他业务使用。
docker tag postgresql-gis:12.2 192.168.1.11:443/myharbor/postgresql-gis:12.2
docker push 192.168.1.11:443/myharbor/postgresql-gis:12.2
[root@es1 bin]# docker push 192.168.1.11:443/myharbor/postgresql-gis:12.2
The push refers to repository [192.168.1.11:443/myharbor/postgresql-gis]
5c337eddc7ad: Pushed
629d9b95bcaa: Pushed
53c61a520c85: Mounted from myharbor/esdb
88765da43086: Mounted from myharbor/esdb
2ca40431ab7d: Mounted from myharbor/esdb
caceb42f09c2: Mounted from myharbor/esdb
5b1d42135d46: Mounted from myharbor/esdb
174f56854903: Mounted from myharbor/esdb
12.2: digest: sha256:cd6658cf2f13044efd2785e78d3ac953885810c4766180bc8d6b21e9474fa1de size: 1997
--备份镜像。倒入后就可以直接使用。
docker save 192.168.1.11:443/myharbor/postgresql-gis:12.2 > postgresq-gis.tar
gzip postgresq-gis.tar
[root@es1 data]# docker save 192.168.1.11:443/myharbor/postgresql-gis:12.2 > postgresq-gis.tar
[root@es1 data]# gzip postgresq-gis.tar
[root@es1 data]# ll postgresq-gis.tar.gz
-rw-r--r-- 1 root root 389315096 Jul 6 14:54 postgresq-gis.tar.gz
扫一扫
2293
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
