2018年05月_瀚高PG实验室

原创数据库启动失败

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状使用pg_ctl启动数据库时出现报错：pg_ctl: no database directory specified and environment variable PGDATA unsetTry "pg_ctl --help" for more information.问题原因没有在环境变量中设置PGDATA变...

2018-05-31 16:23:29 1504

原创数据库启动时发生报错，致命错误: 无法创建信号量

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：Linux x86-64 Red Hat Enterprise Linux 5,Linux x86-64 Red Hat Enterprise Linux 6,Linux x86-64 Red Hat Enterprise Linux 7,中标麒麟_NeoKylin Linux Advanced Server release 6.8 (Ca...

2018-05-28 10:12:09 1231

原创 PostgreSQL之异地物化视图（基于外部表）

首先在192.168.100.200上创建extensionpostgres=# create extension postgres_fdw;CREATE EXTENSION在200上创建serverpostgres=# create server pg154 foreign data wrapper postgres_fdw options(hostaddr '192.16...

2018-05-28 09:53:35 1368

原创 PostgreSQL 之本地物化视图

创建本地表postgres=# create table tbl(id int primary key, info text, crt_time timestamp);CREATE TABLEpostgres=# insert into tbl select generate_series(1,100000),md5(random()::text),clock_timestamp()...

2018-05-28 09:18:40 2510

原创 PostgreSQL之本地分区表的用法和优化

数据分区的好处 1）分区后，单个分区表的索引和表都变小了，可以保持在内存里面，适合把热数据从大表拆分出来的场景。 2）对于大范围的查询，大表可以通过索引来避免全表扫描。但是如果分区了的话，可以使用分区的全表扫描。适合经常要做大范围扫描的场景，按照范围分区（分区后采用全表扫描），减少索引带来的随机BLOCK扫描。 3）大批量的数据导入或删除，对于大表来说，删除大量的数据使用DELETE的话会带...

2018-05-28 09:08:28 1523

原创 PostgreSQL V10 分区表特性

分区表特性是PG10新加的一个很重要的特性。之前的版本也能实现分区表功能，但基本是根据“继承表+约束+规则或触发器”实现。相对于之前的分区实现方式，PG10的分区特性有以下优势： 1）管理分区方便 2）数据插入效率高事实上，PG10的分区特性也是在内置继承表的基础上实现的，所以创建的分区实质上也是普通的表结构。目前PG10支持范围分区和列表分区，哈希分区还不支持。范围分...

2018-05-28 09:05:38 5228

原创 pg_ctl: cannot be run as root

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状使用pg_ctl管理数据库报错：[root@db1 Desktop]# pg_ctl startpg_ctl: cannot be run as rootPlease log in (using, e.g., "su") as the (unprivileged) user that willown the ser...

2018-05-25 16:39:05 2253

原创数据库意外中止，无法启动

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状使用过程中数据库突然中止，且无法启动，存在一些类似报错信息：LOG: checkpoints are occurring too frequently (7 seconds apart)HINT: Consider increasing the configuration parameter "checkpoi...

2018-05-24 16:41:31 8390 2

原创无法远程登陆数据库

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状使用客户端程序连接数据库报错：FATAL: no pg_hba.conf entry for host "192.168.100.182", user "highgo", database "highgo", SSL off问题原因HGDB默认禁用远程访问数据库。解决方案详细解决方案请登录【瀚高技术支持平台】查看...

2018-05-22 15:58:47 287

原创异常断电导致PostgreSQL无法连接

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状服务器异常断电后，使用psql无法连接数据库：psql: could not connect to server: No such file or directoryIs the server running locally and acceptingconnections on Unix domain soc...

2018-05-18 13:02:54 352

原创归档失败并在日志文件中存在报错

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状归档失败并在日志文件中存在报错:cp: writing `/arch/20171204/000000010000000000000002': No space left on deviceLOG: archive command failed with exit code 1DETAIL: The failed...

2018-05-18 09:55:21 910

原创中文无法正确被显示

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状查询表的时候出现乱码并提示表不存在：highgo=# select * from 一致;ERROR: relation "Д╦─Х┤╢" does not exist at character 15STATEMENT: select * from Д╦─Х┤╢;ERROR: relation "一致" doe...

2018-05-17 14:41:27 463

原创 PostgreSQL中bloom的介绍（二）

接下来，我们测试一下使用bloom索引之后的效果。首先，需要创建一些测试数据。所以，先新建一张表：test=# CREATE TABLE bloomtest(id serial, v1 int, v2 int, v3 int, v4 int, v5 int, v6 int);CREATE TABLE然后向表中插入一些随机值：test=# insert into bloomtest...

2018-05-15 14:29:58 655

原创 PostgreSQL中bloom的介绍（一）

Bloom 过滤器代表的是一组值。它的作用是检测一个元素是否可能属于集合，它可以允许有一些false positive，但是不允许存在false negative。也就是说，尽管某个元素不在集合中，测试也可能返回true。然而，如果元素在集合中，就不可能返回false。创建在一组列中的Bloom索引可以被用来加速在这些列的子集上用AND相连的等式的查询。例如：CREATE EXTENS...

2018-05-15 13:59:35 1512

原创 psql版本和server版本不一致的解决办法

如果版本不一致，会出现类似如下的警告：postgres@hyl:~$ psql -h 127.0.0.1psql (9.5.12, server 10.2)WARNING: psql major version 9.5, server major version 10. Some psql features might not work.Type "help" for ...

2018-05-15 13:09:42 3050

原创 auth_delay模块

auth_delay可以使服务器在报告认证失败之前短暂暂停一下，这样就能使对数据库密码的穷举攻击变得困难。但需要注意的是，它不会做任何事情来预防DoS攻击，而且甚至可能会加重这些攻击，这是因为在报告认证失败之前等待的进程仍然会消耗连接槽。如果想要使用这个模块的功能，必须通过postgresql.conf文件中的shared_preload_libraries来导入。 auth_delay.m...

2018-05-15 12:56:07 2057 1

原创 HighgoDB 之 file_fdw的使用

验证环境数据库版本：HighgoDB 4.3.2 （基于PostgreSQL9.5内核）postgresql从9.1开始增加了外部表访问的功能，这个功能就是数据库直接读取数据库以外的文件，比如csv或者text等类型的文件，暂时不支持DML。postgresql有各种插件能直连各种异构DB，如oracle_fdw,mysql_fdw,file_fdw等，对数据的迁移是很方便的，这是P...

2018-05-14 11:50:39 526

原创 PostgreSQL 之 with查询

WITH语句通常被称为通用表表达式（Common Table Expressions）或者CTEs。 WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE（Common table expression）它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DE...

2018-05-14 11:45:33 9849 1

原创 HighgoDB 之 unlogged table的使用

unlogged table是为临时数据设计的，写入性能较高，但是当postgresql进程崩溃时会丢失数据。创建一张普通表test和一张unlogged表test，测试性能情况普通表：highgo=#create table test(a int);CREATE TABLEhighgo=#\timingTiming is on.highgo=#insert into ...

2018-05-14 11:39:24 678

原创 HighgoDB 之临时表的使用

会话级或事务级的临时表，临时表在会话结束或事物结束自动删除，任何在临时表上创建的索引也会被自动删除。除非用模式修饰的名字引用，否则现有的同名永久表在临时表存在期间，在本会话或事务中是不可见的。另外临时表对其他会话也是不可见的，但是会话级的临时表也可以使用临时表所在模式修饰的名字引用。创建临时表的语法：CREATE TEMP tbl_name()ON COMMIT{PRESERV...

2018-05-14 11:33:38 638

原创 psql登录时存在警告

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：N/A版本：4.1.1症状使用psql登陆时报警告：WARNING: psql major version 3.1, server major version 10. Some psql features might not work.Type "help" for help.问题原因服务端版本和客户端版本不一致导致该警告。...

2018-05-11 14:21:19 324

原创 PostgreSQL报错错误代码（四 20-22）

20000 - &gt;未找到case 21000 - &gt;Cardinality违规 22000 - &gt;数据异常 2202E - &gt;数组下标错误 22021 - &gt;字符不在指令表中 22008 - &gt;日期时间字段溢出 22012 - &gt;除数为0 22005 - &gt;分配错误 2200B - &gt;转义字符冲突

2018-05-08 17:02:18 1194

原创 PostgreSQL常见名词解释

垃圾回收：pg的mvcc会产生多版本，多版本会有垃圾，垃圾需要处理，vacuum就可以清除这些垃圾。年龄：年龄是指当前系统事务号减去事务发生时的事务号的差值，因此在早期插入的数据行，随着事务号的分配，年龄会越来越大。膨胀：PG并没有像Oracle那样的undo来存放旧版本；而是将旧版本直接存放于relation文件中。那么带来的问题就是dead tuple过多，导致relation文件不断...

2018-05-08 16:30:08 1073

原创 PostgreSQL报错错误代码（三 0A-0Z）

0A000 - >功能不受支持 0B000 - >启动无效的事务 0F000 - >locator异常 0F001 - >无效的locator规范 0L000 - >无效的授权者 0LP01 - >无效的授权操作 0P000 - >无效的角色规范 0Z000 - >诊断异常 0Z002 - >没有活动处理程序访问的堆栈式诊断程序...

2018-05-08 16:27:10 968

原创 PostgreSQL报错错误代码（二 02-09）

02000 - >没有数据 02001 - >没有返回更多的动态结果集 03000 - > sql语句尚未完成 08000 - >连接异常 08003 - >连接不存在 08006 - >连接失败 08001 - > sql客户端无法建立sql连接 08004 - > sql服务端拒绝建立sql连接 08007 - >未知的事物...

2018-05-08 16:20:43 1445 1

原创登录报错psql: FATAL: database “highgo“ does not exist

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：Linux x86-64 Red Hat Enterprise Linux 6,Linux x86-64 Red Hat Enterprise Linux 7,Microsoft Windo

2018-05-08 10:00:14 5649

原创使用分区表的好处

我们以一个例子来进行说明。如果用户有一个很大的表，大小大约为1TB。其中的一列记录了数据的创建时间。应用程序每天向表中添加少量新数据，而且只更新最近的数据。旧的数据被保留在表中，用于做报告。这些更新会使表变膨胀，autovacuum不能有效的清理膨胀。手动vacuum更不可行，因为那将会锁定这张表更长的时间。这样经过日复一日的使用之后，查询会变得缓慢，性能会逐渐降低。然后我们看一下分区表对...

2018-05-04 13:59:35 5011

原创避免在子查询中使用有歧义的列名

如果在子查询中使用了有歧义的列名，可能会导致得到的结果和我们期望的结果之间出现偏差。我们以一个例子来进行说明。首先，创建两张表：highgo=# CREATE TABLE student(id int, name varchar(40));CREATE TABLEhighgo=# CREATE TABLE transcript(id int, student_id int, d...

2018-05-04 13:25:10 2309

原创 auto_explain模块

auto_explain模块提供了一种自动的记录慢速语句执行计划的方法，使用这种方法就可以不必手动的运行EXPLAIN语句。当我们想要跟踪比较大的应用中未优化的查询时，这种方式是很有帮助的。想要使用这个模块，只需要把它加载到服务器中。例如，可以使用这条语句，将它加载到某个个人会话中： LOAD ‘auto_explain’; 注意！！只有超级用户可以运行这条指令。 auto_explai...

2018-05-04 11:32:57 839

原创 wal_log_hints参数

wal_level控制着有多少信息被写入到WAL中。默认值是minimal，表示只写入从崩溃或者立即关机中恢复需要的信息。如果设置为archive，会增加WAL归档所需的日志。当设置为hot_standby时，表示在备用服务器上增加运行只读查询所需的信息。如果我们想要完成归档和流复制，需要把wal_level设置为 “archive” 或者 “hot_standby”。设置wal_log_...

2018-05-04 11:18:31 3865

原创同步流复制报错“psql: FATAL: the database system is starting up”

目录环境症状问题原因解决方案相关文档报错编码环境系统平台：Linux x86 Red Hat Enterprise Linux 6,Linux x86-64 Red Hat Enterprise Linux 7,Linux x86-64 Red H

2018-05-04 09:58:50 1942

原创 Postgresql 之事务内两个时间戳函数的不同表现

now()和clock_timestamp()在事务内两个时间戳函数的不同表现，具体如下：testdb=# begin;BEGINtestdb=# select now(); now ------------------------------- 2018-05-03 14:31:27.343792+08(1 row)te...

2018-05-03 15:23:00 3509

原创 Postgresql 之基于表的ddl（alter table）审计

如果你要记录用户表被DDL修改定义, 设置默认值, 等等alter table可以完成的工作时的记录, 可以使用事件触发器来达到这个目的。例子如下 : testdb=# create extension hstore;testdb=# create or replace function ef_alter() returns event_trigger as $$declarerec...

2018-05-03 15:18:40 720

原创 Postgresql 之系统表概述

系统表是关系型数据库存放结构元数据的地方，比如表和字段以及内部登记信息等。PostgreSQL 的系统表就是普通表。不应该手工修改系统表，通常总有 SQL 命令可以做这些事情。比如，CREATE DATABASE 向 pg_database 表插入一行，并且实际上在磁盘上创建该数据库。有几种特别深奥的操作例外，比如增加索引访问方法。系统表部分: 大多数系统表都是在数据库创建的过程中...

2018-05-03 15:15:21 4569

原创 Postgresql 之模式管理与psql使用的小技巧

今天同事问了个问题：postgresql模式创建了会存到哪个系统表中？一下没回答上来，一般的可以通过元命令查看模式等信息，参考命令如下：通过以下元命令可以列出所有模式\dn[S+] [PATTERN] 进而翻阅资料定位到系统表pg_namespace，参阅手册关于该系统表的描述如下：pg_namespace:该系统表存储名字空间(模式)。见如下应用示例： ...

2018-05-03 15:07:31 2123

原创查询某表格上次进行vacuum的时间

查询某表格上次进行vacuum的时间：select schemaname,relname,last_autovacuum,last_autoanalyze from pg_stat_all_tables where relname='tablename';dj_quiz_test=# select schemaname,relname,last_autovacuum,last_a...

2018-05-03 14:47:11 1324

原创 drop database 报错

highgo=# drop database test;错误: 55006: 其他用户正在使用数据库 "test"DETAIL: 那里有1个其它会话正在使用数据库.[highgo@localhost ~]$ psqlpsql (4.0.0)Type "help" for help.highgo=# \l ...

2018-05-03 14:41:53 3044

原创流复制查询备库与主库差异时间

highgo=# select pg_last_xlog_receive_location(),pg_last_xlog_replay_location(),pg_last_xact_replay_timestamp(),now(); pg_last_xlog_receive_location | pg_last_xlog_replay_location | pg_last_xact_replay

2018-05-03 14:36:38 732 1

原创 su highgo可能引起的psql报错

linux下切换操作系统用户，使用su highgo命令可能出现的问题：[root@localhost ~]# su highgo[highgo@localhost root]$ psqlbash: psql: 未找到命令[highgo@localhost root]$ which psql/usr/bin/which: no psql in (/home/highgo/perl5/bin:

2018-05-03 14:33:35 645

原创 PostgreSQL建立索引避免锁表

正常情况下Postgresql建立普通btree索引时会阻塞DML(insert,update,delete)操作，直到索引完成，期间读操作不受阻塞。oracle数据库有在线创建的选项online。Postgresql也提供了相关的功能concurrently，使用方法：create index concurrently index_name on tablename();使用co...

2018-05-03 06:48:44 4192

空空如也

空空如也