一本码簿

众里寻码千百度,那段却在github处。

0%

Postgres

数据库备份

1
2
3
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

数据库恢复

1
2
3
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
  • 导出到表格
    1
    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;

开启模块扩展

  • 创建数据库
    1
    2
    create database geoTemplate;
    \c geoTemplate;
  • 启用空间数据库模块
    1
    2
    3
    4
    5
    6
    7
    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";

创建空间索引

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

查询所有数据库大小

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

查看全部表大小

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

查看全部表索引大小

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

查看数据库连接情况

1
select * from pg_stat_activity

结束数据库连接

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

查询数据库中的表

1
select * from pg_tables WHERE schemaname='public'

语句耗时统计

1
select * FROM pg_stat_statements ORDER BY total_time DESC

索引缓存命中率

1
2
3
4
5
6
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;

查看数据库物理大小

1
select pg_size_pretty(pg_database_size('postgres'));

导入数据库

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

清空带外键约束的表格

TRUNCATE njq_user_basic CASCADE

导入函数创建脚本

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