分类：PostgreSQL

2021
01-12

解决postgresql无法远程访问的情况

今天刚入手这个数据库玩玩，发现无法通过IP去访问数据库，后面查询原因为，该数据库默认只能通过本地连接，也就是回环地址(127.0.0.1)解决方案：1.修改安装目录下的data\pg_hba.conf,在配置文件最后有IPV4和IPV6的配置,新增一行(这里我用的IPV4,开放所有IP)hostallall0.0.0.0/0md5说明:该配置为允许所有IP访问,下面有对应的一些配置示例提供参考32->192.168.1.1/32表示必须是来自这个IP地址的访问才合法；24->192.168.1.0... 继续阅读 >

2021
01-12

浅谈Postgresql默认端口5432你所不知道的一点

关于Postgresql端口5432的定义：5432端口，已经在IANA(TheInternetAssignedNumbersAuthority，互联网数字分配机构)注册，并把该端口唯一分配给Postgres。这意味着，一台安装了linuxOS的服务器，哪怕没有安装过postgresql数据库，也会有这个预留端口。查看这个预留端口的方法如下：new@newdb->cat/etc/services|grep5432postgres5432/tcppostgresql#POSTGRESpostgres5432/udppostgresql#POSTGRES/etc/servic... 继续阅读 >

2021
01-12

postgresql修改完端口后直接psql连接数据库报错的解决

今天修改pg的端口号port改成5435后重启完数据库的时候直接psql进库的时候进不去[postgres@node2data]$psqlpsql:couldnotconnecttoserver:NosuchfileordirectoryIstheserverrunninglocallyandacceptingconnectionsonUnixdomainsocket"/tmp/.s.PGSQL.5432这时，进数据库有两种方式1、psql后面加上端口号[postgres@node2data]$psql-p5435psql(9.6.1)Type"help"forhelp.postgres=#2、在环境变量/ho... 继续阅读 >

2021
01-12

postgresql 实现启动、状态查看、关闭

利用psql启动数据库[postgres@highgo~]$pg_ctlstart查看系统中运行的postgres进程#ps-ef|greppostgres连接postgresql数据库#psql-h127.0.0.1-dpostgres-Upostgres停止postgresql数据库实例#pg_ctlstop#ps-ef|greppostgres启动服务器最简单的方法是像下面这样：$postgres-D/usr/local/pgsql/data最好在后台启动postgres，使用下面的Unixshell语法：$postgres-D/usr/local/pgsql/data>logfile2>&1&要... 继续阅读 >

2021
01-12

postgresql连续归档及时间点恢复的操作

简介前面我们介绍了通过pgsql的流复制在生产环境中搭建高可用环境来保证服务的可持续性；我们也要对数据库进行周期备份，来防止数据的丢失，这就需要连续归档，它不仅可以用于大型数据库的增量备份和恢复，也可以用于搭建standby镜像备份。  PostgreSQL默认处于非归档模式。开启归档模式，主要涉及到三个参数：wal_level,archive_mode和archive_commandwal_level参数默认为mininal,设置此参数为archive或者之上的级别... 继续阅读 >

2021
01-12

PostgreSQL pg_archivecleanup与清理archivelog的操作

pg_archivecleanup和pg_rewind是PG中两个重要的功能，一个是为了清理过期的archivelog使用的命令，另一个是你可以理解为物理级别的wallog的搬运工。我们先说第一个pg_archivecleanup命令，这个命令主要是用于使用了archivelog功能的postgresql但在archivelog堆积如山的情况下，你怎么来根据某些规则，清理这些日志呢？这里面就要使用pg_archivecleanup这个命令了，可以定时的来运行它，将已经移动到archivecle... 继续阅读 >

2021
01-12

解决PostgreSQL日志信息占用磁盘过大的问题

当PostgreSQL启用日志时,若postgresql.conf日志的相关参数还使用默认值的话磁盘很容易被撑爆.因此在启用了logging_collector参数时,需要对其它相关的参数进行调整.系统默认参数如下#log_destination='stderr'#日志格式,值为stderr,csvlog,syslog,andeventlog之一.logging_collector=on#启用日志#log_directory='log'#日志文件存储目录#log_filename='postgresql-%Y-%m-%d_%H%M%S.log'#日志文件命名方,默认为每秒一... 继续阅读 >

2021
01-12

Postgresql 如何清理WAL日志

WAL是WriteAheadLog的简写，和oracle的redo日志类似，存放在$PGDATA/pg_xlog中，10版本以后在$PGDATA/pg_wal目录.如果开启了归档，在目录archive_status下会有一些文件，以ready结尾的，表示可以归档但还没有归档，done结尾的表示已经归档。和WAL日志数量相关的几个参数：wal_keep_segments=300#inlogfilesegments,16MBeach;0disablescheckpoint_completion_target=0.5#checkpointtargetduration,0.0-... 继续阅读 >

2021
01-12

PostgreSQL归档配置及自动清理归档日志的操作

在一般的生产环境中，数据库都需要开启归档模式，那么在pg中如何开启归档模式呢？pg中的归档配置涉及几个参数如下：#-Archiving-是否开启归档#archive_mode=off#enablesarchiving;off,on,oralways#(changerequiresrestart)归档命令，注意%p%f%%格式化的含义。%p是被归档的redo文件的路径，%f是被归档的redo文档的文件名%%是百分号#archive_command=''#command... 继续阅读 >

2021
01-12

PostgreSQL 实现列转行问题

1测试表数据SELECTrelative_label_contentFROMfrk_s.label_cor_generelative_label_content------AABBCC2列转行写法写法1：string_aggSELECTfrwybs,string_agg(relative_label_content,',')asrelative_label_contentFROMfrk_s.label_cor_geneGROUPBYfrwybsrelative_label_content------------AA,BB,CC写法2：array_to_string(ARRAY_AGG(text),',')SELECTfrwybs,array_to_string(ARRAY_AGG(DISTINCTr... 继续阅读 >

2021
01-12

PostgreSQL 实现distinct关键字给单独的几列去重

PostgreSQL去重问题一直困扰着我，distinct和groupby远不如MySQL用起来随便，但是如果掌握了规律，还是和MySQL差不多的主要介绍的是distinct关键字selectdistinctid,name,sex,agefromstudent假如有一张student表，字段如上图，我查询student表中所有信息用distinct去重(上面的SQL语句)，pgsql就会根据所有的字段通过算法取得重复行的第一行，但是很明显，ID这个字段我在设计的时候不会让它重复，所以相当于没有去重我想只根据... 继续阅读 >

2021
01-10

浅谈PostgreSQL中大小写不敏感问题

本文主要讨论PostgreSQL中大小写不敏感存在的问题。默认情况下，PostgreSQL会将列名和表名全部转换为小写状态。图1Person与person如图1所示，我们创建表person，其中包含name列。然后插入一条记录。执行SELECT查询时，使用列名Name和表名Person而不是name和person，发现仍然可以正常获取刚刚插入表person中的记录。图2创建表Person？此时如果我们再想创建表Person，会得到一个错误，因为此时PostgreSQL实际上把表名从Person转换... 继续阅读 >

2021
01-10

PostgreSQL LIKE 大小写实例

PostgreSQL数据库函数upper（“字符串”）：转成大写字符串WHEREUPPER("User_Name")LIKEupper(username)此句查询“User_Name”中值大小写不区分。SELECT"User_Id","User_Image","User_Name","User_Birthday","User_Sex","User_OnlineLat","User_OnlineLon","User_State",(SELECTCOUNT(*)FROM"tbUsers"WHEREUPPER("User_Name")LIKEupper(username))AS"user_count"FROM"tbUsers"WHEREUPPER("User_Name")LIKEupp... 继续阅读 >

2021
01-09

Postgresql中LIKE和ILIKE操作符的用法详解

LIKE和ILIKE操作符可以模糊匹配字符串，LIKE是一般用法，ILIKE匹配时则不区分字符串的大小写。它们需要结合通配符使用，下面介绍两种常用的通配符。%：百分号用于匹配字符串序列，可匹配任意组合；_：下划线用于匹配任何单一字符。举例来说明LIKE和ILIKE操作符的区别。先创建一张数据表table1,包含两列：id列和name列，代码如下：createtabletable1(idint,namevarchar);insertintotable1values(1,'HuaweiMateRS'),(2,'Hu... 继续阅读 >

2021
01-09

PostgreSQL中的COMMENT用法说明

PostgreSQL附带了一个命令-COMMENT。如果想要记录数据库中的内容，这个命令很有用。本文将介绍如何使用此命令。随着数据库的不断发展和数据关系变得越来越复杂，跟踪数据库中添加的所有内容会变得非常困难。要记录数据的组织方式以及可能随时间添加或更改的组件，有必要添加某种文档。例如，文档可以写在外部文件中，但这会产生一种问题，他们很快就会变为过时的文件。PostgreSQL有一个解决这个问题的方法：COMMENT命令。使用它... 继续阅读 >

2021
01-09

使用PostgreSQL为表或视图创建备注的操作

1为表和列创建备注droptableifexiststest;createtabletest(objectidserialnotnull,numintegernotnull,constraintpk_test_objectidprimarykey(objectid),constraintck_test_numcheck(num<123),);commentontabletestis'我是表';commentoncolumntest.objectidis'我是唯一主键';commentoncolumntest.numis'数量字段';commentonconstraintpk_test_objectidontestis'我是约束,唯一主... 继续阅读 >