2017年12月_瀚高PG实验室

原创 PG遇到oom解决方案

postgres=# create table t1(id int,info text);CREATE TABLEpostgres=# insert into t1 select generate_series(1,10000000),md5(random()::text);server closed the connection unexpectedly This probably

2017-12-28 15:53:31 6347

原创 shared_buffers参数

官网介绍 Sets the amount of memory the database server uses for sha red memory buffers. The default is typically 128 megabytes (128MB), but might be less if your kernel settings will not support it (as d

2017-12-28 15:40:58 7161

原创 \o和\! 命令了解

\! [ command ] 跳到一个单独的shell或者执行shell命令command。参数不会被进一步解释，shell将会原封不动地看到参数。特别要说明的是，变量替换规则和反斜线转义在这里不适用。\o 或 \out [ filename ] \o 或 \out [ | command ] 安排把未来的查询结果保存到文件filename 中或者用管道导向到shell命令command。如果

2017-12-28 15:34:43 6428

原创 default_statistics_target参数

default_statistics_targetdefault_statistics_target为没有通过ALTER TABLE SET STATISTICS设置的列特定目标设置表列的默认统计目标。较大的值会增加执行ANALYZE所需的时间，但可能会提高规划人员估算的质量。该参数告诉 PostgreSQL 应该抽样多少数据来填充存储元数据的表。默认值为100，PostgreSQL 会读取 d

2017-12-28 15:30:45 1497

原创 checkpoint_completion_target值的设置（二）

高checkpoint_completion_target值带来的优缺点优点： • I/O操作占用资源比例逐渐趋于平缓。避免或减少了数据库运行中突然卡住不动的情况。 • 磁盘吞吐量趋于平缓，减少和减小了I/O峰值的出现。 • 等待时间减小，提高客户体验缺点： • 如果宕机，则恢复时间长 • Shared_buffer比较大，写入频繁的数据库，这个值设置的过大是个

2017-12-27 15:07:55 695

原创 checkpoint_completion_target值的设置（一）

checkpoint_completion_target是postgresql数据库中一个至关重要的参数，主要与参数checkpoint_timeout(checkpoint_timeout默认值为5min)配合使用。举个简单的例子来说：情况一：•checkpoint_completion_target=0.5•checkpoint_timeout

2017-12-27 14:50:49 3040 1

原创 Postgresql-XL简单搭建

一、安装部署1、集群规划共三个节点，GTM放在了一节点上，建议是为GTM单独放一个服务器pgxl1 192.168.102.98 coordinator1 datanode1 gtmpgxl2 192.168.102.99 coordinator2 datanode2pgxl3 192.168.1

2017-12-27 14:00:50 4574

Postgres-XL是什么？类似于”ORACLE RAC”的集群解决方案，对于Postgres-XL来说，一个实例实际上是由多个独立的PostgreSQL实例组成，它们分布在不同的物理（或虚拟）主机上协同工作，呈现给用户的是一个数据库实例的效果。每一个Coordinator都可以作为Postgres-XL系统的访问入口，负责处理客户端的连接及SQL命令、协调系统中的其他Coordinator和Da

2017-12-27 12:00:45 4723 3

原创 PostgreSQL10之pg_hba_file_rules视图

PostgreSQL10 新增 pg_hba_file_rules动态视图。该视图的功能直观体现出pg_hba.conf文件的配置信息和配置是否正确。修改hba文件reload后，该视图可以实时反馈出生效的配置信息和错误的配置信息。很实用。避免了一些情况下配置错误没有发现还找不到原因的情况。一种错误情况的示例如下： line_number | type | databas

2017-12-26 00:59:26 751

原创 PG10一些目录名称的变化

在PG10之前，安装路径存在如下名称的目录：pg_xlog pg_clogpg_log现在分别更名为：pg_wal pg_xactlog目的也是避免当成运行的操作日志而误删。By 徐云鹤

2017-12-26 00:49:59 632

原创 PG10 Vacuum监控进度

PostgreSQL通过Vacuum来清理过时数据，在之前的版本我们无法查看Vacuum进度。现在，我们可以通过动态视图来查看：pg_stat_progress_vacuum它显示了每个Vacuum worker进程扫描、回收的多少页面。可以通过其来估算进度。By 徐云鹤

2017-12-26 00:43:04 1551

原创 PostgresSQL关于大页内存

从PostgresSQL 9.4开始添加了服务器参数huge_pages。使用HugePage会导致更小的page表，并减少CPU在内存管理上花费的时间，从而提高性能。通常，虚拟页面的默认粒度是4k。对于使用1M虚拟内存的进程，即将虚拟地址映射到物理地址的256个页表项。对于使用1G字节的虚拟内存进行处理，即256000个虚拟页表条目。尽管现代cpus具有tlb来加速虚拟/物理内存映射，但对

2017-12-25 16:21:58 1438

原创 Tpch-300G数据量-Oracle对应sql

使用tpch进行 oracle与postgresql的查询性能测试时，德哥的tpch工具可以生成适用于postgres的sql，但是适用于oracle的sql，却需要手工修改。以下是我从tpch相关报告里提取出的300G数据量测试sql ,并稍作修改，用以显示查询时间。set timing onspool queries.logbegin dbms_stats.gather_schema

2017-12-22 18:14:27 1471

原创 drop database 会立即释放磁盘空间么？

drop database 会立即释放磁盘空间么？ DROP DATABASE drops a database. It removes the catalog entries for the database and deletes the directory containing the data. It can only be executed by the database owner

2017-12-22 18:08:14 2744

原创使用pg_archivecleanup清理归档

官网用法：https://www.postgresql.org/docs/9.5/static/pgarchivecleanup.html用法简介：-rw-------. 1 postgres postgres 16777216 Dec 20 06:59 000000050000011200000045-rw-------. 1 postgres postgres 167 Dec 13

2017-12-22 18:06:23 8768

原创 Postgres小版本升级

小版本升级的手册解释： Minor releases never change the internal storage format and are always compatible with earlier and later minor releases of the same major version number, e.g., 8.4.2 is compatible with 8.

2017-12-22 17:59:37 827 1

原创 PostgreSQL角色和权限

PostgreSQL是通过角色来管理数据库访问权限的，我们可以将一个角色看成是一个数据库用户，或者一组数据库用户。角色可以拥有数据库对象，如表、索引，也可以把这些对象上的权限赋予其它角色，以控制哪些用户对哪些对象拥有哪些权限。一、数据库角色： 1. 创建角色： CREATE ROLE role_name;

2017-12-21 22:36:39 1090

原创 PostgreSQL的约束

约束类型：检查约束、非空约束、唯一约束、主键、外键1.检查约束设置某个字段里的数值必须满足约束表达式的条件。例：限制人的年龄在0~120之间，语句如下：create table person(name varchar(40),age int check (age >=0 and ageinsert intoperson values('name1',120);ins

2017-12-21 22:36:10 4881

原创 Postgresql临时表

PostgreSQL支持两类临时表，会话级和事务级临时表。在会话级别的临时表中，在整个会话的生命周期中，数据一直保存。事务级临时表，数据只存在于这个事务的生命周期中。不指定临时表的属性，PostgreSQL中，不管是事务级还是会话级临时表，当会话结束时，临时表就会消失。这与oracle数据库不同，在oracle数据库中，只是临时表中的数据消失，而临时表还存在。示例：创建临时表temp

2017-12-21 22:35:37 7964 1

原创 \pset 、\x命令

\pset命令用于设置输出的格式\psetborder 0:表示输出内容无边框。示例如下：\psetborder 1:表示边框只在内部。示例如下：\psetborder 2:表示内外都有边框。示例如下：\x命令，可以把表中每一行的每列数据都拆分为单行展示。执行\x命令前，如下：执行\x命令后，如下：

2017-12-21 22:34:44 1746

原创 psql的环境设置(文件.mylocalenv)

实验环境硬件配置：1核 2G软件版本：vmware workstation 10.0.4 build-2249910RHEL7.4HGDB V4.1.1HGDB安装完成后使用psql直接登录，默认以highgo用户访问highgo数据库，我们可以通过创建一个文件.mylocalenv，来改变这种默认登录，具体如下：su - highgo$ vi .mylocalenvexport P

2017-12-20 14:28:48 693

原创与search_path有关的创建表异常问题(HGDB)

HGDB创建数据库对象与变量search_path的值有关，当创建对象时，如果未指定模式，这些对象将会在默认的模式下被创建，这个模式叫pubulic。一个例外情况是另一个模式首先出现在search_path。以下问题是在search_path默认值发生变化是抛出的，请参考如下：

2017-12-20 14:23:25 928

原创 HGDB数据库对象与模式的关系

关系型数据库都有模式的定义，模式的存在是为了更便于管理众多数据库对象，有为数据库对象分组的意思，HGDB数据库中的模式也是如此，一个数据库可以存在多个模式，创建的数据库对象都会隶属于某个模式，同一个数据库不同的模式下面的对象可以重名，默认的模式是public，下面通过一个实验来理解模式在HGDB中的表现。

2017-12-20 14:08:21 550

原创 HGDB查看数据库与表空间对应的物理文件

1.创建一个表空间:su - highgomkdir -p /tmp/data/user_tCREATE TABLESPACE tbls_t OWNER user_t LOCATION '/tmp/data/user_t';在此之前应该确保目录/tmp/data/user_t有权限访问，并且目录为空。 2.创建一个数据库，指明该数据库的表空间是刚刚创建的user_t：CREATE DATABAS

2017-12-20 14:02:37 362

原创 pg_dump 迁移表到其他模式

2017-12-18 15:03:53 1580 1

原创修改表（数据库）的表空间

1、将表mytable迁移到新建表空间test2下（表空间test2的路径为/home/highgo/tbs2）查询表内容：highgo=# select * from mytable ; id ---- 1 2 3(3 rows)创建表空间test2并迁移mytable到test2下：highgo=# create tablespace test2 locatio

2017-12-18 15:02:44 2682

原创 Postgresql 日期和时间类型

timestamp with[out] time zone 日期时间(时区) time with[out] time zone 只用于一日内的时间(时区) 可以精确到毫秒。MySQL只能到秒。默认都不会显示时区，需要显示的指定with time zone。highgo=# select time '120900'; time ----------

2017-12-18 15:01:21 17370

原创 checkpoint执行太频繁导致数据库日志报错

数据库系统日志报错如下所示：checkpoints are occurring too frequently (3 seconds apart),Consider increasing the configuration parameter "max_wal_size"解决方案：首先看一下参数checkpoint_warning的介绍：设置checkpoint_warning 参数

2017-12-18 14:59:02 5295

原创操作系统原理（1）

OS:本身就是一层虚拟机。OS将操作系统底层硬件抽象成了（virtual machine）计算机基本体系结构核心五大部件:CPU :运算器，控制器，寄存器三大核心部件。运算器：运算功能，逻辑运算控制器：控制指令与数据的存取过程 PS：程序=指令+数据寄存器：比如存放中间运算结果，会经常被刷新。RAM ：一个字节是一个存储单元，叫cell。 CPU必须有寻址的能力

2017-12-15 17:53:36 224

原创 Postgresql 获取某张表的oid

两种方式得到某张表的oid，这里以表 foo 为例：第一种：传统方式postgres=# select oid from pg_class where relname='foo'; oid ------- 49542(1 row)第二种：利用pg oid别名特性postgres=# select 'foo'::regclass::oid; oid ------- 4954

2017-12-15 17:51:23 7345

原创 PostgreSQL事务可以包含DML、DDL、DCL

PostgreSQL一个事务中可以包含DML, DDL, DCL.除了以下语句:create tablespacecreate database使用concurrently并行创建索引其他未尽情况略(Oracle执行DDL前自动将前面的未提交的事务提交,所以Oracle不支持在事务中执行DDL语句)这种情况和Oracle不同，oracle在执行ddl语句（如 drop table）会自动

2017-12-15 17:48:23 2009

原创 Postgresql 随机生成测试数据sql

100w条随机测试数据。create table abc (a integer,b integer,c integer ,d integer);postgres=# insert into abc select (random()*(10^5))::integer,(random()*(10^5))::integer,(random()*(10^5))::integer from generate

2017-12-15 17:42:47 3706

原创 postgresql数据库用户名密码验证失败

问题分析（1）检查环境变量中是否指定了相关参数，比如：#export PGUSER=highgo#export PGDATABASE=highgo#export PGHOST=localhost#export PGPORT=5866#export PGPASSWORD=12345（2）pg_hba.conf文件是否设置正确（3）是否添加密码文件，填写是否正确解决方案（1）如果在

2017-12-14 17:51:26 15599

原创 Windows下DATA目录的迁移

1、查看当前数据（或插入新数据）作为标记。highgo=# create table t1(id int,name text);CREATE TABL id | name----+---------------------------------- 1 | cf9920dd1f8a7be7e56a85f8a3e018f6 2 | cf9920dd1f8a

2017-12-14 17:44:42 1649 3

原创 WAL日志的命名规则深度解析（二）

上一次说到WAL日志的命名规则，但是在实际的数据库环境中，我们还会经常遇到WAL日志的另一种命名格式：highgo=# select pg_current_xlog_location(); pg_current_xlog_location -------------------------- 1/A301EEE0(1 行记录)这里表示的同为WAL日志文件的位置LSN（Log Sequence

2017-12-14 14:26:00 854

原创 WAL日志的命名规则深度解析（一）

此处说的WAL日志的命名规则指的就是$PGDATA/pg_xlog目录下的文件命名规则，或者说是归档日志的命名规则。文件名字以十六进制表示且分为三个部分，第一部分是时间线标识（timelineid），一般保持该部分值不变。第二部分是文件标识ID（fileid,seg）第三部分是区段ID（seg）且值不能超过255，即0xFF。 00000001 00000000 000000BC -

2017-12-14 12:08:51 1775

原创 postgresql的单用户模式

启动postgresql存在一种单用户模式，它的使用场景如下：1.当多用户模式无法工作的时候，可以使用单用户模式连接进数据库。比如当事物年龄极大的时候。2.initdb的阶段3.修复系统表时4.其他需求命令如下：[highgo@rhel711g ~]$ postgres --single -D $HG_HOME/dataPostgreSQL stand-alone bac

2017-12-13 15:21:32 1852

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

PG运行过程中出现归档失败的情况，并在日志文件中存在报错。报错如下：cp: writing `/arch/20171204/000000010000000000000002': No space left on deviceLOG: archive command failed with exit code 1DETAIL: The failed archive command w

2017-12-13 15:14:00 2501

原创 postgresql意外中止，无法启动

PG数据库在使用过程中数据库突然中止，且无法启动，存在一些类似报错信息：LOG: checkpoints are occurring too frequently (7 seconds apart)HINT: Consider increasing the configuration parameter "checkpoint_segments".PANIC: could not w

2017-12-13 15:08:44 8483 1

原创简单介绍postgresql的关闭模式

postgresql（PG）有三种关闭方式可选：smart：智能关闭模式。但凡有外部连接未结束，就需要等待这些连接结束后才会开始关闭数据库。fast：快速关闭模式。通过向所有活动会话发送SIGTERM信号来结束这些会话。immediate：直接关闭模式。向所有子进程发送SIGQUIT信号。通过这种方式关闭数据库，完整性是不可靠的。再次启动数据库时将会重放WAL日志进行恢复。三种模式

2017-12-13 14:54:52 3642

空空如也

空空如也