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)”错误，这时候指定正确的字符集即可解决方问题。