postgresql
jjxliu306
这个作者很懒,什么都没留下…
展开
-
postgresql 中常用小语法
1. PG中 类型转换 有时候在postgresql中需要对值的类型进行转换,pg中提供的方法 example : select '33':: integer example2: select case('33' as integer)2. pg中的行号 (类似于oracle中的 rownum) example : select row_number() over() , * from...2013-05-14 13:47:05 · 213 阅读 · 0 评论 -
postgresql自动增备pitr脚本
postgresql中提供了pitr作为增备的方案,在全量物理备份的基础上通过wal文件的备份来提供所有数据修改的记录,当需要还原的时候通过wal日志回放进行数据还原,还原后面在讲,本帖主要说明通过脚本和crontab定时任务来对数据库进行定期的自动全量备份和pitr增量备份,避免一个基础备份+长时间备份日志造成回滚或者还原话费大量的时间。 1、编写 base_backup.sh,提供...2017-08-22 14:15:33 · 243 阅读 · 0 评论 -
postgres常用查询集锦
查询pg中每个表的统计量信息(在非精确的数据条数统计下适用):-- 查询public的schema下所有表的统计条数(预估值)SELECT relname, reltuples FROM pg_class r JOIN pg_namespace n ON (relnamespace = n.oid) WHERE relkind = 'r' AND n.nspname = '...2017-07-11 13:14:53 · 228 阅读 · 0 评论 -
pg提取序列号
select table_catalog , table_schema , table_name , column_name , column_default , regexp_matches(column_default , '^nextval[\(]([^\)]+)::regclass[\)]$') from information_schema.columns where col...2017-07-05 17:02:47 · 1238 阅读 · 0 评论 -
postgres窗口函数实例(取出每日中前N条数据)
假设现有一批数据,字段中包含date_id,数据格式yyyyMMdd. 现在需求为需要取出每天中前N条数据,那么sql来了(用到窗口函数): create temp table dddd(id serial , date_id int , name varchar(20)); insert into dddd(date_id , name) values (20160301...2016-03-02 14:09:00 · 1710 阅读 · 0 评论 -
postgis 查看gis字段定义
postgis中有时候我们需要查看gis表中定义的字段类型 point、linestring。polygon等。可以通过 geomerty_colums 去查看,example: gistest=# select * from geometry_columns where f_table_schema = 'public' and f_table_name = 'gis_per...2015-07-06 15:20:40 · 832 阅读 · 0 评论 -
postgresql 查看tablespace命中率
SELECT a.datname, pg_size_pretty(pg_database_size(a.datid)) AS size, CAST(blks_hit / (blks_read + blks_hit + 0.000001) * 100.0 AS NUMERIC(5, 2)) AS cache, CAST(xact_commi...2015-07-02 09:41:29 · 1003 阅读 · 0 评论 -
postgis三维转二维
有时候我们拿到的数据是三维的 譬如:linestring(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6 2) ,但其实我们展示的时候只需要二维,并且数据量庞大的时候可能需要删掉三维的信息。 SELECT st_astext(st_force_2d(st_geomfromewkt('linestring(1 1 2, 2 3 2, 4 5 2, 6 7 2, 5 6 2)...2015-07-01 10:43:19 · 1046 阅读 · 0 评论 -
postgis源码安装
Postgis安装手册1、 准备A、 需要先安装好postgresql,本人目前安装的是9.3的版本,安装过程详见:http://jjxliu306.iteye.com/blog/2222106,具体版本可以去官网下载:http://www.postgresql.org/ftp/source/ 。 B、 准备postgis安装包和相关依赖,包含:gdal-1.9.2.tar.gz g...2015-06-26 13:18:36 · 336 阅读 · 0 评论 -
postgresql源码安装
Pg安装1、 创建用户, 这个用户随意,不一定非要叫postgres。Useradd pumpkin ;2、 下载pg源码安装包,目前我用的是9.3.5。3、 源码安装pg[pumpkin@hl231 ~]$ ll总用量 21436-rw-r--r--. 1 pumpkin pumpkin 21946446 7月 25 2014 postgresql-9.3.5.ta...原创 2015-06-25 18:19:15 · 127 阅读 · 0 评论 -
postgres模拟merge
群里这几天一直有人要做类似merge或者replace的操作,嚷嚷表字段多 用function进行insert or update写起来麻烦。OK,下面贴一个 触发器进行replace的demo 写个触发器 插入之前执行触发器 -- 创建一个测试表 create table test(id int primary key , name varchar(50)); -- 触发器 插入前ID如果已经存...原创 2015-04-09 14:33:23 · 343 阅读 · 0 评论 -
postgres 操作记录总数汇总mark
pg里有时候会存在数据删除操作需要查看到底有多少条数据被删除,这时候通过下面方法会比较简单 mrapp=# create temp table _tt (id int) ;CREATE TABLE时间:615.655 msmrapp=# insert into _tt select generate_series(1,100);INSERT 0 100时间:28.48...原创 2015-03-16 13:43:45 · 343 阅读 · 0 评论 -
postgresql 外部文件表
本文主要说明PG中怎么样外部文件转换为表来使用。这种文件作为表的存在时宽泛的一个约束。 1. 首先挂载库 mrapp=# create extension file_fdw;CREATE EXTENSION时间:1.638 ms 2. 创建服务mrapp=# create server file_table_server foreign data wrapper f...2013-06-05 12:46:34 · 439 阅读 · 0 评论 -
postgresql 中常用小语法2
1. PG 中测试可能需要循环插入N多数据 这时候写function等就比较麻烦 我们可以用 generate_series 来example: mrapp=# create table test_series(id int) ;CREATE TABLEmrapp=# insert into test_series(id) select generate_series(1,1000...原创 2013-06-04 12:03:40 · 83 阅读 · 0 评论 -
postgresql 数组应用
OperatorDescriptionExampleResult=equalARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]t<>not equalARRAY[1,2,3] <> ARRAY[1,2,4]t<less thanARRAY[1,2,3] < ARR...2013-05-23 17:09:33 · 269 阅读 · 0 评论 -
Postgres pg_multixact信息丢失极限拉起数据库
本测试模拟pg_multixact,pg_xlog全部信息丢失的情况下,数据库完全起不来的情况下拉起数据库。本人血淋淋的教训,建议还是做好备库和增量备份。 一、初始化一个新的实例、创建数据库等。(因为当前系统用户并非postgres,因此我加了-U 来默认数据库超管用户postgres) [lyf@centos250 pg9.6]$ initdb -D pg_db_test...2017-09-13 15:06:25 · 1341 阅读 · 0 评论