分类：PostgreSQL

2021
01-08

PostgreSQL 逻辑复制 配置操作

####PostgreSQLLogicalReplication(逻辑复制)####LogicalReplication(逻辑复制)为PostgreSQL自己提供的一种方案,主要是通过一方发布，一方订阅，来实现彼此共同关注的一种技术。服务器|功能|10.10.56.16|发布者P1_pub表||10.10.56.17|发布者P2_pub表||10.10.56.19|订阅者P_sub表|背景一些数据根据业务拆分成一部分P1表、一部分P2表或者多个，A库操作P1表，B库操作P2表现需要可以查询整个P表，即需要一... 继续阅读 >

2021
01-08

PostgreSQL 主备数据宕机恢复测试方案

主节点宕机数据，备库数据恢复背景在从节点停止服务，然后往主库不断写入数据，然后把主机宕机掉，启动从库，把主库宕机期间的增量wal日志复制到从库的archive目录下，执行恢复脚本，则会把主机宕机后的增量数据追加到数据库。环境服务器角色10.10.56.16master10.10.56.18slave-配置16master的pg_hba,conf文件hostallall10.10.56.0/0md5hostr... 继续阅读 >

2021
01-08

PostgreSQL使用MySQL外表的步骤详解(mysql_fdw)

浅谈postgres不知不觉已经升到了版本13,记得两年前还是版本10，当然这中间一直期望着哪天能在项目中使用postgresql，现在已实现哈～；顺带说一下：使用postgresql的原因是它的生态完整，还有一个很重要的点儿是速度快这个在第10版的时这么说也许还为时过早，但是在13这一版本下一点儿也不为过,真的太快了，我简单的用500w的数据做聚合，在不建立索引(主键除外)的情况下执行一个聚合操作，postgres的速度是mysql的8倍，真的太快了... 继续阅读 >

2021
01-08

PostgreSQL LIST、RANGE 表分区的实现方案

简介PG分区：就是把逻辑上的一个大表分割成物理上的几块。分区的优点1.某些类型的查询性能得到提升2.更新的性能也可以得到提升，因为某块的索引要比在整个数据集上的索引要小。3.批量删除可以通过简单的删除某个分区来实现。4.可以将很少用的数据移动到便宜的、转速慢的存储介质上。分区实现原理10.x版本之前PG表分区的实现原理：PG中是通过表的继承来实现的，建立一个主表，里面是空的，然后每个分区去继承它。无论何时，该... 继续阅读 >

2021
01-08

浅谈PostgreSQL的客户端认证pg_hba.conf

大家都知道防火墙主要是用来过滤客户端并保护服务器不被恶意访问攻击，那在pg中同样存在一个类似于防火墙的工具用来控制客户端的访问,也就是pg_hba.conf这个东东。在initdb初始化数据文件时,默认提供pg_hba.conf.通过配置该文件，能够指定哪些ip可以访问，哪些ip不可以访问，以及访问的资源和认证方式,该文件类似于oracle中的监听中的白名单黑名单功能,且同样可以reload在线生效。记录可以是下面七种格式之一：localdatabaseuse... 继续阅读 >

2021
01-08

基于PostgreSQL pg_hba.conf 配置参数的使用说明

pg_hba.conf配置详解该文件位于初始化安装的数据库目录下编辑pg_hba.conf配置文件postgres@clw-db1:/pgdata/9.6/poc/data>vipg_hba.confTYPE参数设置TYPE表示主机类型，值可能为：若为`local`表示是unix-domain的socket连接，若为`host`是TCP/IPsocket若为`hostssl`是SSL加密的TCP/IPsocketDATABASE参数设置DATABASE表示数据库名称,值可能为：`all`,`sameuser`,`samerole`,`replication`,`数据库名称`,或者多个... 继续阅读 >

2021
01-08

PostgreSQL 数据同步到ES 搭建操作

安装python和dev开发包[root@rtm2Packages]#rpm-ivhpython-devel-2.7.5-58.el7.x86_64.rpm准备中...#################################[100%]正在升级/安装...1:python-devel-2.7.5-58.el7#################################[100%][root@rtm2Packages]#ls安装multicorn[root@rtm2multicorn-1.3.5]#makePythonversionis2.7gcc-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement... 继续阅读 >

2021
01-08

Postgresql 跨库同步表及postgres_fdw的用法说明

postgres_fdw模块PostgreSQL9.3addpostgres_fdwextensionforaccessingremotetablesPostgreSQL9.3新增了一个postgres_fdw模块,已经整合在源码包中.用于创建postgres外部表.注：db_des为目标库，developer_month_orders_data为表名。意思就是从查询库a中建立同名FOREIGN关联表，可以查询目标库中的数据。以下命令在需要建立的关联库中执行。目标库中的表必须存在，也就是先建立好，否则从a库，查询会报找不到表错误阿里... 继续阅读 >

2021
01-08

postgresql表死锁问题的排查方式

1.查询激活的执行中的sql,查看有哪些更新update的sql。select*frompg_stat_activitywherestate='active';2.查询表中存在的锁selecta.locktype,a.database,a.pid,a.mode,a.relation,b.relnamefrompg_locksajoinpg_classbona.relation=b.oidwherelower(b.relname)='h5_game';3.杀掉死锁进程selectpg_terminate_backend(pid)frompg_stat_activitywherestate='active'andpid!=pg_backend_pid()--andp... 继续阅读 >

2021
01-08

postgresql 实现获取所有表名,字段名,字段类型,注释

获取表名及注释：selectrelnameastabname,cast(obj_description(relfilenode,'pg_class')asvarchar)ascommentfrompg_classcwhererelkind='r'andrelnamenotlike'pg_%'andrelnamenotlike'sql_%'orderbyrelname过滤掉分表：加条件andrelchecks=0即可获取字段名、类型、注释、是否为空：SELECTcol_description(a.attrelid,a.attnum)ascomment,format_type(a.atttypid,a.atttypmod)astype,a.attname... 继续阅读 >

2021
01-08

在postgreSQL中运行sql脚本和pg_restore命令方式

今天踩坑了，把powerdesign生成的sql脚本文件，用pg_restore命令一直运行。。。过程惨不忍睹，一直以为是编码问题，修改了serve和client的encoding，结果。。。记录一下这个错误：postgreSQL运行sql脚本文件：psql-ddbname-Uusername-f(脚本所在位置).sqlpostgerSQL的pg_restore命令用法:pg_restore[选项]…[文件名]一般选项:-d,--dbname=名字连接数据库名字-f,--file=文件名输出文件名-F,--format=c|d|t备份文件格... 继续阅读 >

2021
01-08

PostgreSql 导入导出sql文件格式的表数据实例

PostgreSql默认导出的文件格式是.backup，而我们很多数据库导入数据库脚本时是.sql文件格式的，PostgreSql作为国内的新潮，会不会不支持导出.sql文件格式吗？答案是当然不会。下面我们借助PgAdminIII工具来导出.sql的脚本的。一.导出首先，你当然得有一张完整的数据表啦，并且保证里面是有数据的；有了表和数据之后，选中该表；选中右键-->备份我们可以看到文件名默认以.backup格式的方式进行数据备份的。首先将自定义格式--... 继续阅读 >

2021
01-08

在postgresql中运行sql文件并导出结果的操作

方法一在psql中运行在终端进入用户test下的数据库testdb：$psql-p5432-Utest-dtestdb假设要执行的.sql文件叫做d1.sql，存放路径为：/mnt/hgfs/share/database/2.18.0_rc2/dbgen/queries/d1.sql，导出的文件叫做d1.out，存放的路径为：/mnt/hgfs/share/database/2.18.0_rc2/dbgen/queries/out/d1.outtestdb=#\o/mnt/hgfs/share/database/2.18.0_rc2/dbgen/queries/out/d1.outtestdb=#\i/mnt/hgfs/share/database/2.18.0... 继续阅读 >

2021
01-08

在postgresql中通过命令行执行sql文件

通过命令行执行初始化sql脚本是比较常见的需求，命令行下执行如下操作即可：若是执行的命名只是创建用户，编辑用户，创建数据库的话可以不指定-d参数。psql-Uusername-dmyDataBase-a-finit.sql如果是远程数据库加入-h参数指定主机地址即可psql-hhost-Uusername-dmyDataBase-a-finit.sql补充：PostgreSQL操作-psql基本命令一、建立数据库连接接入PostgreSQL数据库:psql-hIP地址-p端口-U数据库名之后会要求输... 继续阅读 >

2021
01-08

postgresql 修改字段长度的操作

使用数据库postgresql的时候，有时会遇到字段长度扩展的情况，由于之前已经有数据了，所以只能修改字段长度，不能通过删除再增加的方式。可以使用如下方式进行ALTERTABLEyour_table_namealterCOLUMNyour_column_nametypecharactervarying(3000);通过上面的一句话就可以把你的表中相应的字段的长度修改为3000.补充：PostgreSQL字符类型长度变更的性能背景业务有时会遇到表中的字符型字段的长度不够用的问题，需要修改表定义... 继续阅读 >

2021
01-08

PostgreSQL upsert(插入更新)数据的操作详解

本文介绍如何使用PostgreSQLupsert特性插入或当被插入数据已存在则更新数据。1.介绍PostgreSQLupsert在关系型数据库中，upsert是一个组合词，即当往表中插入记录，如果该记录已存在则更新，否则插入新记录。为了使用该特性需要使用INSERTONCONFLICT语句：INSERTINTOtable_name(column_list)VALUES(value_list)ONCONFLICTtargetaction;该语法中target可以是下面列举内容之一：(column_name)?列名ONCONSTRAINTcon... 继续阅读 >