Postgres

管理命令

数据库备份

alias pgbackup='thisFun(){pg_dump -U postgres -F t -ovf /var/pgbak/$1_$(date '+%Y%m%d%H%M%S').tar $1};thisFun'
alias pgbak='pg_dump -h localhost -U postgres -F t -ovf /var/pgbak/njqdb.tar njqdb'
pg_dump -h localhost -U postgres -d njqwebdb > /hdc/njqdbbak/njqweb$(date '+%Y%m%d%H%M%S').bak -v

数据库恢复

alias pgrecover='thisFun(){var=${1%%_*};var=${var##*/};echo $var;pg_restore -U postgres -d ${var} -cv $1};thisFun'
alias pgrec='pg_restore -h localhost -U postgres -d njqdb -cv /var/pgbak/njqdb.tar
psql -h localhost -U postgres -d databasename <  C:\databasename.bak

Linux管理

• 登录数据库 sudo psql -u postgres
• 导出到表格

COPY (SELECT a.corp_name,a.area_code,a.entity_code,b.addr_type,b.addr_content,b.symbol_code,b.geo_addr FROM geo_entity_info as a left join geo_space_info as b on a.corp_id=b.corp_id) TO '/mnt/d/a.csv' WITH csv HEADER;

开启模块扩展

• 创建数据库

  create database geoTemplate;
  \c geoTemplate;

• 启用空间数据库模块

  CREATE EXTENSION postgis;
  CREATE EXTENSION pgrouting;
  CREATE EXTENSION postgis_topology;
  CREATE EXTENSION fuzzystrmatch;
  CREATE EXTENSION postgis_tiger_geocoder;
  CREATE EXTENSION address_standardizer;
  CREATE EXTENSION "uuid-ossp";

管理SQL

创建空间索引

• CREATE INDEX name ON table USING gist(column);
• CREATE INDEX name ON table USING gin(column);

查询所有数据库大小

select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database ORDER BY size DESC

查看全部表大小

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc

查看全部表索引大小

select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc

查看数据库连接情况

select * from pg_stat_activity

结束数据库连接

SELECT pg_terminate_backend(pid)  FROM pg_stat_activity WHERE datname='njqwebdb' AND pid<>pg_backend_pid()

查询数据库中的表

select * from pg_tables WHERE schemaname='public'

语句耗时统计

select * FROM pg_stat_statements ORDER BY total_time DESC

索引缓存命中率

SELECT
  sum(idx_blks_read) as idx_read,
  sum(idx_blks_hit)  as idx_hit,
  (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM
  pg_statio_user_indexes;

查看数据库物理大小

select pg_size_pretty(pg_database_size('postgres'));

清空带外键约束的表格

TRUNCATE njq_user_basic CASCADE

导入函数创建脚本

select proname,  pg_get_functiondef(proname::regproc) from pg_proc where proname LIKE 'njq_%';

日常应用

导入数据库

shp2pgsql -a -D -s 4326 -W utf-8 point.shp geo_point > result.sql

-s 代表指定数据的SRID为3857
-c 代表数据将新建一个表
-d 删除旧的表，重新建表并插入数据
-a 向现有表中追加数据
-p 仅创建表结构，不添加数据，这四个参数是互斥的
-W Shape文件中属性的字符集，通常Postgresql的字符集是UTF-8，有时候可能Shape数据中的字符集是其他，就可能报“Unable to convert data value to UTF-8 (iconv reports “无效或不完整的多字节字符或宽字符”). Current encoding is “UTF-8”. Try “LATIN1” (Western European)”错误，这时候指定正确的字符集即可解决方问题。

常用查询

跨表查询统计

SELECT xcz_item.code,xcz_region."name","type","count"(xcz_item.code) AS count
FROM xcz_item
LEFT JOIN xcz_region
ON xcz_item.code LIKE '330604%' AND xcz_item.code=xcz_region.code
GROUP BY xcz_item.code,"type",xcz_region."name"
ORDER BY xcz_item.code ,"type"

跨表条件统计

SELECT DISTINCT a.code,a.name,
COALESCE(count(b.*),0) AS total,
sum(CASE WHEN b.state = 4 THEN 1 ELSE 0 END) AS finished,
sum(CASE WHEN b.state <>4 OR b.state IS NULL THEN 1 ELSE 0 END) AS unfinished,
st_astext(st_centroid(a.geom)) AS position
FROM xcz_region a left join xcz_item b
ON a.code=b.code
WHERE a.need=1 AND b.type='1001'
GROUP BY a.code,a.name,a.geom
ORDER BY a.code

图形操作

• 空间查询

SELECT count(*) FROM njq_dk WHERE st_intersects(geom,st_geomfromtext('MULTIPOLYGON(((120.92 29.37,120.93 29.37,120.93 29.38,120.92 29.37,120.92 29.37)))',4326))
SELECT count(*) FROM njq_dk WHERE st_intersects(geom,st_geomfromtext('POLYGON((120.92 29.37,120.93 29.37,120.93 29.38,120.92 29.37,120.92 29.37))',4326))

• 创建空间索引

create extension pg_trgm;
update pg_opclass set opcdefault = true where opcname='gin_trgm_ops';
CREATE index on njq_dk USING gist (geom);
CREATE index on njq_dk USING gin (geom gin_trgm_ops);

• 外包多边形

select ST_AsText(ST_ConvexHull(ST_Collect(geom))) FROM xcz_region

• 外包矩形

select ST_AsText(st_Envelope(ST_Collect(geom))) FROM xcz_region

• 中心点（面质心）

select st_astext(st_centroid(geom)) FROM xcz_region

• 面上点（和面lable位置一致）

select st_astext(st_PointOnSurface(geom)) FROM xcz_region