Linux部署Postgre12和PostGis,PGRoutting

操作技术问题

-- 添加一个自增的主键列
ALTER TABLE table_name
ADD COLUMN id BIGSERIAL PRIMARY KEY;


-- 联表更新
update t_obd_vehicle t1
 set chip_imei = t2.chip_imei
 from t_obd_info t2
where t1.obd_imei = t2.obd_imei

一、centos7下部署

1.1 下载安装postgresql12-server

  postgresql默认不用密码也不支持远程登录，

  修改 /var/lib/pgsql/12/data/pg_hba.conf 和 /var/lib/pgsql/12/data/postgresql.conf

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm && \
yum install -y postgresql12-server && \
/usr/pgsql-12/bin/postgresql-12-setup initdb && \
cp /var/lib/pgsql/12/data/postgresql.conf /var/lib/pgsql/12/data/postgresql_sample.conf && \

echo "
host all all 0.0.0.0/0 md5
" >> /var/lib/pgsql/12/data/pg_hba.conf && \

sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" /var/lib/pgsql/12/data/postgresql.conf && \

systemctl enable postgresql-12 && \
systemctl restart postgresql-12 && \
systemctl status postgresql-12

 1.2 离线安装方法

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm && \
mkdir pg12_rpm_packs && \
yum install --downloadonly --downloaddir=./pg12_rpm_packs postgresql12-server postgresql12 postgresql12-contrib
yum localinstall *.rpm && \

/usr/pgsql-12/bin/postgresql-12-setup initdb && \
cp /var/lib/pgsql/12/data/postgresql.conf /var/lib/pgsql/12/data/postgresql_sample.conf && \
 
echo "
host all all 0.0.0.0/0 md5
" >> /var/lib/pgsql/12/data/pg_hba.conf && \
 
sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" /var/lib/pgsql/12/data/postgresql.conf && \
 
systemctl enable postgresql-12 && \
systemctl restart postgresql-12 && \
systemctl status postgresql-12

1.3 设置密码

[root@longtu ~]# su - postgres
-bash-4.2$ psql
psql (12.8)
Type "help" for help.

postgres=# ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE
postgres=# \q
-bash-4.2$ exit;
logout
[root@longtu ~]#

二、安装空间地理支持，gis扩展，pgroute扩展

2.1 下载安装扩展包

# https://blog.csdn.net/qq262593421/article/details/114011921
# http://download.osgeo.org/postgis/windows/
# https://trac.osgeo.org/postgis/wiki/UsersWikiInstall

yum install -y epel-release && \
yum -y install postgis31_12.x86_64 postgis31_12-client.x86_64 && \
yum -y install pgrouting_12 && \
yum -y install ogr_fdw12


-- 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;
-- 
CREATE EXTENSION pgrouting;
CREATE EXTENSION ogr_fdw;

SELECT version();
SELECT PostGIS_full_version();

  2.2 创建GIS扩展

[root@longtu ~]# su - postgres
Last login: Thu Sep 16 12:55:41 CST 2021 on pts/1
-bash-4.2$ psql
psql (12.8)
Type "help" for help.

postgres=# create extension postgis;
CREATE EXTENSION
postgres=#

 三、定时备份还原

# ==== vi back.sh ====
# /usr/pgsql-12/bin/pg_dump --help
#!/bin/bash
echo "开始执行 PostgreSql 数据库postgres的备份！"
echo "backup ing -------------------"
nowtime=$(date +%F+%T)
export PGPASSWORD=pOstgis@2022
echo "时间：" $nowtime
set timeout 500
/usr/pgsql-12/bin/pg_dump --file "/root/postgres_back/postgres_back_"$nowtime".backup" --host "10.11.28.8" --port "5432" --username "postgres"  "mobiledata" --exclude-table=t_obd_history --verbose --role "postgres" --format=c --blobs --encoding "UTF8"
echo "数据库 postgres 备份结束！"
exit;


# ==== vi delete_back.sh ====
#!/bin/bash
echo "删除 8 天前的 数据库备份文件！"
find /root/postgres_back/ -name "postgres_back_*" -mtime +7 -exec rm -rf {} \;
set timeout 1000
echo " 8 天前的数据库备份文件删除完毕！"


# ==== crontab -e ====
30 23 * * * /root/postgres_back/delete_back.sh
0 23 * * * /root/postgres_back/back.sh


# ==== 还原 ====
# /usr/pgsql-12/bin/pg_restore --help
/usr/pgsql-12/bin/pg_restore -h 192.168.0.7 -p 5432 -U postgres -W -d zmddata -v "/root/postgres_back/postgres_back_2022-08-12+11:29:31.backup"

命令行其他参数

四、开发中技术问题

 4.1 将普通表转为分区表

-- https://www.bbsmax.com/A/nAJv0KDGdr/
-- 1、创建需要分区的主表，插入10000条模拟数据，注：分区列必须有not null约束
create table part_test(id int, info text, crt_time timestamp not null);
insert into part_test select id, md5(random()::text),clock_timestamp() + (id||' hour')::interval from generate_series(1,10000) t(id);

-- 2、创建分区，每个分区包含1个月的跨度数据
select                                             
create_range_partitions('part_test'::regclass,             -- 主表OID          
                        'crt_time',                        -- 分区列名           
                        '2016-10-25 00:00:00'::timestamp,  -- 开始值
                        interval '1 month',                -- 间隔，interval 类型，用于时间分区表
                        24,                                -- 分多少个区
                        false) ;                           -- 不迁移数据
                        
-- 3、查看表分区，由于不迁移数据，所以数据还在主表
SELECT
	nmsp_parent.nspname AS parent_schema,
	parent.relname AS parent,
	nmsp_child.nspname AS child,
	child.relname AS child_schema 
FROM
	pg_inherits
	JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
	JOIN pg_class child ON pg_inherits.inhrelid = child.oid
	JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace
	JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace 
WHERE
	parent.relname = 'part_test';

select count(*) from only part_test;

-- 4、使用非堵塞式的迁移接口，迁移结束后，主表数据已经没有了，全部在分区中
select partition_table_concurrently('part_test'::regclass, -- 主表OID
                                    10000,                 -- 一个事务批量迁移多少记录
                                    1.0);                  -- 获得行锁失败时，休眠多久再次获取，重试60次退出任务。 
select count(*) from only part_test;

-- 5、数据迁移完成后，建议禁用主表，这样执行计划就不会出现主表了
select set_enable_parent('part_test'::regclass, false);

-- 6、完成后，查看效果
explain select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;