随笔分类 -  PostgreSQL Q&A

Cannot enlarge string buffer containing XX bytes by XX more bytes
摘要:在ELK的数据库报警系统中,发现有台机器报出了下面的错误: 当看到是发生了OOM时,以为是整个数据库实例存在了问题,线上检查发现数据库正常,后查阅资料了解到,pg对于一次执行的查询语句长度是有限制的,如果长度超过了1G,则会报出上面的错误。 上面日志中的1342177281 bytes是查询的长度。 阅读全文

posted @ 2018-12-07 23:21 Still water run deep 阅读(2128) 评论(0) 推荐(0)

pg_upgrade升级报错:Only the install user can be defined in the new cluster
摘要:前两天pg11刚出来,打算测试一下,想将测试库升级到pg11,之前测试库的版本是pg9.6,后面我将它升到了pg10,打算在pg10的版本基础上升级到pg11。 但执行时,多次报出: 所以就搜了一下代码,在src/bin/pg_upgrade/check.c中找到该报错的原因 pg_upgrade升 阅读全文

posted @ 2018-10-23 16:59 Still water run deep 阅读(677) 评论(0) 推荐(0)

WARNING: pgstat wait timeout
摘要:在ELK的邮件报警中,发现了一个 WARNING: pgstat wait timeout 的报错信息,看字面意思是pgstat有关操作等待超时。 通过google查询,发现在pg的邮件列表中提到多数为io不足引起的,通过sar命令,查看当时的io情况,发现io的使用率在80%-100%之间。暂时以 阅读全文

posted @ 2018-09-13 17:05 Still water run deep 阅读(724) 评论(0) 推荐(0)

kill -9 a postgres process
摘要:在postgresql中,不推荐使用Kill -9直接杀掉异常连接,因为直接使用kill -9会引起整个数据库核心进程的重启,同时其他正常程序连接也会被杀掉。 现开启两个psql连接,然后使用Kill -9杀掉其中一个: 观察上面的日志信息,可以看到session2是被杀掉的会话,但同时sessio 阅读全文

posted @ 2018-01-08 19:34 Still water run deep 阅读(825) 评论(0) 推荐(0)

pg_basebackup: invalid tar block header size
摘要:问题: 在使用pg_basebackup搭建备节点时,由于pg_basebackup本身使用的是int整型来保存传输的数据大小,当传输的数据大于4G的话,整数就会溢出,进而报出:pg_basebackup: invalid tar block header size之类的错误. 解决: 在9.4版本 阅读全文

posted @ 2017-08-29 17:58 Still water run deep 阅读(322) 评论(0) 推荐(0)

Postgresql FATAL: could not create semaphores: No space left on device
摘要:昨天安装完成pg 9.5后,启动报错: FATAL: could not create semaphores: No space left on device DETAIL: Failed system call was semget(xxxxxxxxxx). HINT: This error do 阅读全文

posted @ 2016-04-27 15:59 Still water run deep 阅读(4940) 评论(0) 推荐(0)

配置recovery_min_apply_delay后重启standby节点报错:psql: FATAL: the database system is starting up
摘要:环境: pg版本:PostgreSQL 9.4.4 on x86_64 系统版本:CentOS release 6.6 linux内核版本:2.6.32-504.8.1.el6.x86_64 今天测试standby延时复制时,在standby节点配置了recovery_min_apply_delay 阅读全文

posted @ 2016-04-08 16:15 Still water run deep 阅读(5208) 评论(0) 推荐(0)

Deleting backup_label on restore will corrupt your database!
摘要:The quick summary of this issue is that the backup_label file is an integral part of your database cluster binary backup, and removing it to allow the 阅读全文

posted @ 2016-03-22 22:19 Still water run deep 阅读(1641) 评论(0) 推荐(0)

why does txid_current() assign new transaction-id?
摘要:Naoya:Hi,hackers!I have a question about txid_current().it is "Why does txid_current() assign new transaction-id?".When we executes txid_current() out... 阅读全文

posted @ 2015-08-13 23:04 Still water run deep 阅读(588) 评论(0) 推荐(0)

PostgreSQL: Query for location of global tablespace?
摘要:Q:I have been trying to make our database clients pro-active about not filling up the partition on which the database they are using resides.As all ou... 阅读全文

posted @ 2015-08-05 00:08 Still water run deep 阅读(516) 评论(0) 推荐(0)

Is it possible to configure PostgreSQL to automatically close idle connections?
摘要:1、use pgbouncerAs new connections/transactions/statements arrive, the pool will increase in size up to thedefined user maximums. Those connections wil... 阅读全文

posted @ 2014-11-02 11:29 Still water run deep 阅读(560) 评论(0) 推荐(0)

How to drop a PostgreSQL database if there are active connections to it?
摘要:1、PostgreSQL 9.1 and below:SELECT pg_terminate_backend(pg_stat_activity.procpid)FROM pg_stat_activityWHERE pg_stat_activity.datname = 'TARGET_DB' AND... 阅读全文

posted @ 2014-11-01 19:30 Still water run deep 阅读(330) 评论(0) 推荐(0)

导航