分类：PostgreSQL

2021
01-03

postgresql 实现查询某时间区间的所有日期案例

postgresql 实现查询某时间区间的所有日期案例 核心sql如下：selectdaytime::datefromgenerate_series(('2017-06-01'),--查询开始日期（可根据需求调整）(selectnow()::date),--查询结束日期（可根据需求调整）'1day'--间隔（可根据需求调整）)s(daytime)以上sql，得到结果为从6月1号到今天这个时间区间内的每天的日期，如下：举例说明：查询tableA所有time_period区间内的日期，time_period的数据格式为：20170101-20170120；selectdaytime,periodsfrom(selectday... 继续阅读 >
2021
01-03

解决postgreSql远程连接数据库超时的问题

解决postgreSql远程连接数据库超时的问题 首先在cmd中ping这个ip如果发现可以ping通就可以考虑是远程数据库开启了防火墙、或者数据库设置该ip不能访问。防火墙问题：可以考虑直接关闭防火墙，或者设置防火墙开放5432端口然后到postgresql安装目录下data中修改pg_hba.conf文件，配置用户的访问权限，拉到底部hostallall127.0.0.1/32trusthostallall192.168.1.0/24md5#表示允许网段192.168.1.0上的所有主机使用所有合法的数据库用户名#访问数据库，并提供加密的密... 继续阅读 >
2021
01-03

PostgreSQL 设置允许访问IP的操作

PostgreSQL安装后默认只能localhost:5432访问检验方法：curllocalhost:5432#访问成功提示curl:(52)Emptyreplyfromservercurl127.0.0.1:5432#访问不成功提示curl:(7)Failedtoconnectto172.17.201.227port5432:Connectionrefused修改pg_hba.confpg_hba.conf和postgresql.conf的存放目录都在(9.5版本)/etc/postgresql/9.5/mainhostallall192.168.1.0/24trust表示允许网段192.168.1.0上的所有主机使用所有合... 继续阅读 >
2021
01-03

PostgreSQL 允许远程访问设置的操作

PostgreSQL 允许远程访问设置的操作 postgres远程连接方式配置配置pg_hba.conf文件目录C:\ProgramFiles\PostgreSQL\9.5\data(QXY)主机[postgres@qxydata]$pwd/spark/pgsql/data[postgres@qxydata]$catpg_hba.conf#TYPEDATABASEUSERADDRESSMETHOD#“local”isforUnixdomainsocketconnectionsonlylocalallalltrust#IPv4localconnections:hostallall127.0.0.1/32trust#IPv6localconnections:hostallall::1/128trust在#IPv4local... 继续阅读 >
2021
01-03

postgresql流复制原理以及流复制和逻辑复制的区别说明

流复制的原理：物理复制也叫流复制，流复制的原理是主库把WAL发送给备库，备库接收WAL后，进行重放。逻辑复制的原理：逻辑复制也是基于WAL文件，在逻辑复制中把主库称为源端库，备库称为目标端数据库，源端数据库根据预先指定好的逻辑解析规则对WAL文件进行解析，把DML操作解析成一定的逻辑变化信息（标准SQL语句），源端数据库把标准SQL语句发给目标端数据库，目标端数据库接收到之后进行应用，从而实现数据同步。流复制和逻辑复... 继续阅读 >
2021
01-03

PostgreSQL 流复制异步转同步的操作

非常重要的synchronous_commit参数流复制的同步方式，有主库配置文件postgresql.conf，中的synchronous_commit控制着。所以理解该参数的配置十分重要。单实例环境参数值说明优点缺点on或local当事务提交时，WAL先写入WALbuffer再写到WAL文件（落盘）中。设置为on表示提交事务时需要等待本地WAL最终落盘后，才向客户端返回成功。非常安全数据库性能有损耗... 继续阅读 >
2020
12-30

Postgresql 检查数据库主从复制进度的操作

如何查看主从复制的状态，且备库应用落后了多少字节这些信息要在主库中查询查看流复制的信息可以使用主库上的视图selectpid,state,client_addr,sync_priority,sync_statefrompg_stat_replication;pg_stat_replication中几个字断记录了发送wal的位置及备库接收到的wal的位置、sent_location--发送wal的位置write_location--备库接收到的wal的位置flush_location—备库写wal日志到磁盘的位置replay_location—备库应用日志的位置... 继续阅读 >
2020
12-30

PostgreSQL流复制参数max_wal_senders的用法说明

环境：PostgreSQL9.2.4主机:192.25.10.76从机:192.25.10.71做postgresql的流复制主从时，会遇到调整max_wal_sengers这个参数，官方文档对这个参数做了一个简要的说明(9.2.4比早先版本多了几句话并做了一些微调)，但没有实际的例子。1.参数说明：Specifiesthemaximumnumberofconcurrentconnectionsfromstandbyserversorstreamingbasebackupclients(i.e.,themaximumnumberofsimultaneouslyrunningWALsender... 继续阅读 >
2020
12-30

在postgresql数据库中创建只读用户的操作

在pg数据库中创建只读用户可以采用如下方法。大体实现就是将特定schema的相关权限赋予只读用户。--创建用户CREATEUSERreadonlyWITHENCRYPTEDPASSWORD'123456';--设置用户默认开启只读事务ALTERUSERreadonlySETdefault_transaction_read_only=ON;--将schema中usage权限赋予给readonly用户,访问所有已存在的表GRANTusageONSCHEMAxyhTOreadonly;--将schema中表的查询权限赋予给readonly用户,访问所有已存在的表GRAN... 继续阅读 >
2020
12-30

postgresql中wal_level的三个参数用法说明

wal_level中有三个主要的参数：minimal、archive和hot_standby1、minimal是默认的值，它仅写入崩溃或者突发关机时所需要的信息（不建议使用）。2、archive是增加wal归档所需的日志（最常用）。3、hot_standby是在备用服务器上增加了运行只读查询所需的信息，一般实在流复制的时候使用到。补充：postgresqlWAL相关参数配置文件#-Settings-wal_level=minimal#minimal,replica,orlogical#(changerequiresres... 继续阅读 >
2020
12-30

在postgresql中结束掉正在执行的SQL语句操作

结束进程两种方式：SELECTpg_cancel_backend(PID)取消后台操作，回滚未提交事物(select);SELECTpg_terminate_backend(PID)中断session，回滚未提交事物(select、update、delete、drop);SELECT*FROMpg_stat_activity;根据datid=10841SELECTpg_terminate_backend(10841);补充：PostgreSQL无法在PL/pgSQL中开始/结束事务我正在寻求澄清如何确保plpgsql函数中的原子事务,以及为数据库进行此特定更改设置了隔离级别.在下面显... 继续阅读 >
2020
12-30

postgresql减少wal日志生成量的操作

postgresql减少wal日志生成量的操作 1、在繁忙的系统中，如果需要降低checkpoint发生的频率，减少WAL日志的生成量，减轻对系统IO的压力，可以通过以下两种方法。1)调整WALsegment大小，最高可以调整到64MB，不过只能通过编译来调整。对于已有系统不太方便；2)增大checkpoint_segments设置，使得checkpoint不会过于频繁地被触发；2、在9.5中，checkpoint_segments被废弃，可以通过新增参数max_wal_size来调整，该参数缺省为1GB，已经是9.4的2倍。但如果9.4中手工设... 继续阅读 >
2020
12-30

postgresql synchronous_commit参数的用法介绍

synchronous_commit指定在命令返回”success”指示给客户端之前，一个事务是否需要等待WAL记录被写入磁盘。合法的值是{local,remote_write,remote_apply,on,off}默认的并且安全的设置是on。不同于fsync，将这个参数设置为off不会产生数据库不一致性的风险：一个操作系统或数据库崩溃可能会造成一些最近据说已提交的事务丢失，但数据库状态是一致的，就像这些事务已经被干净地中止。因此，当性能比完全确保事务的持久性更重要时，... 继续阅读 >
2020
12-30

Postgresql - 查看锁表信息的实现

查看表锁信息，是DBA常用的脚本之一。实验环境：CentOS7PG10.4先通过A窗口执行mytest=#begin;BEGINmytest=#updatet1setcol1='a'whereid=1;UPDATE1mytest=#打开B窗口执行mytest=#begin;BEGINmytest=#updatet1setcol1='b'whereid=2;UPDATE1mytest=#updatet1setcol1='b'whereid=1;等待了说明只锁住了行，对于更新其他行没有影响。再打开一个窗口查看信息SELECTa.datname,locktype,virtualtransacti... 继续阅读 >
2020
12-30

基于postgresql数据库锁表问题的解决

基于postgresql数据库锁表问题的解决 查询是否锁表了selectoidfrompg_classwhererelname='可能锁表了的表'selectpidfrompg_lockswhererelation='上面查出的oid'如果查询到了结果，表示该表被锁则需要释放锁定selectpg_cancel_backend(上面查到的pid)补充：PostgreSQL解决锁表、死锁问题1.--查询ACTIVITY的状态等信息SELECTT.PID,T.STATE,T.QUERY,T.WAIT_EVENT_TYPE,T.WAIT_EVENT,T.QUERY_STARTFROMPG_STAT_ACTIVITYTWHERET.DATNAME='数据... 继续阅读 >