分类：PostgreSQL

2021
01-13

postgresql 如何查看pg_wal目录下xlog文件总大小

当然如果你登录服务器所在主机，直接在$PGDAT/pg_wal下执行：du-h--max-depth=1./可以得到。#du-h--max-depth=1./4.0K./archive_status193M./如果通过客户端怎么做呢？答案：pg_ls_waldir()函数。pg_ls_waldir()是pg10.0引入的函数，可以输出数据库WAL目录的所有文件。postgres=#selectsum(size)frompg_ls_waldir();sum-----------201326592(1row)单位是byte，所以当前pg_wal的xlog日志总大小为201326592... 继续阅读 >
2021
01-13

postgresql之使用lsn 获取 wal文件名的实例

10.0及以后版本：pg_walfile_name()用法：postgres=#selectpg_current_wal_lsn(),pg_walfile_name(pg_current_wal_lsn()),pg_walfile_name_offset(pg_current_wal_lsn());pg_current_wal_lsn|pg_walfile_name|pg_walfile_name_offset--------------------+--------------------------+---------------------------------2/C000840|00000001000000020000000C|(00000001000000020000000C,... 继续阅读 >
2021
01-13

修改postgresql存储目录的操作方式

修改postgresql存储目录：sudorsync-av/var/opt/gitlab/postgresql/data/gitlab/修改/etc/gitlab/gitlab.rbpostgresql[‘data_dir']=“/data/gitlab/postgresql/data”postgresql[‘dir']=“/data/gitlab/postgresql”postgresql[‘home']=“/data/gitlab/postgresql”重新加载配置，并重启gitlabgitlab-ctlreconfiguregitlab-ctlrestart注意：保留原位置目录/var/opt/gitlab/postgresql（会创建s.PGSQL.5432文件... 继续阅读 >
2021
01-13

postgresql运维之远程迁移操作

postgresql运维之远程迁移操作 背景：高可用架构版本。主备分别部署在机器A和B上，现在要将其分别迁移到机器C和D上。思路：1、首先根据源实例的备份（云盘上可用snapshot），创建一个mirror实例，mirror包含两个节点，分别部署在C和D上。2、在源实例主节点hba.conf中增加mirror主节点的ip的设置，允许源实例主节点接受来自mirror主节点的连接。3、mirror实例主节点，创建recovery.conf文件，设置primary_conninfo指向源主节点。启动mirror主节点，建立源实例主到... 继续阅读 >
2021
01-13

postgreSQL自动生成随机数值的实例

1、随机生成身份证新建一个函数，用来生成身份证号码，需要输入两个日期参数createorreplacefunctiongen_id(adate,bdate)returnstextas$$selectlpad((random()*99)::int::text,2,'0')||lpad((random()*99)::int::text,2,'0')||lpad((random()*99)::int::text,2,'0')||to_char(a+(random()*(b-a))::int,'yyyymmdd')||lpad((random()*99)::int::text,2,'0')||... 继续阅读 >
2021
01-13

使用postgresql 模拟批量数据插入的案例

使用postgresql 模拟批量数据插入的案例 创建表：CREATETABLEt_test(IDINTPRIMARYKEYNOTNULL,NAMETEXTNOTNULL,AGEINTNOTNULL,ADDRESSCHAR(50),SALARYREAL);模拟批量插入：insertintot_testSELECTgenerate_series(1,5000000)askey,repeat(chr(int4(random()*26)+65),4),(random()*(6^2))::integer,null,(random()*(10^4))::integer;解释：其中：1、generate_series（1,5000000），表示生成一个数值序列，从1到5000... 继续阅读 >
2021
01-13

postgreSQL数据库默认用户postgres常用命令分享

1、修改用户postgres的密码#alteruserpostgreswithpassword‘xxxx';(其中xxxx是修改的密码)。2、查看下当前schema的所有者：//查看当前schema的所有者，相当于\du元命令SELECTn.nspnameAS"Name",pg_catalog.pg_get_userbyid(n.nspowner)AS"Owner"FROMpg_catalog.pg_namespacenWHEREn.nspname!~'^pg_'ANDn.nspname<>'information_schema'ORDERBY1;3、查询结果如图所示，模式“abc”的所有者为postgresql用... 继续阅读 >
2021
01-13

PostgreSQL的中文拼音排序案例

前一段时间开发人员咨询，说postgresql里面想根据一个字段做中文的拼音排序，但是不得其解环境：OS：CentOS6.3DB：PostgreSQL9.2.4TABLE:tbl_kenyon场景：postgres=#\dtbl_kenyonTable"public.tbl_kenyon"Column|Type|Modifiers--------+------+---------------vname|text|--使用排序后的结果，不是很理想postgres=#selectvnamefromtbl_kenyonorderbyvname;vname-------上海北京杭州浙江(4rows... 继续阅读 >
2021
01-13

关于PostgreSQL 行排序的实例解析

关于PostgreSQL 行排序的实例解析 在查询生成输出表之后，也就是在处理完选择列表之后，你还可以对输出表进行排序。如果没有排序，那么行将以不可预测的顺序返回(实际顺序将取决于扫描和连接规划类型和在磁盘上的顺序，但是肯定不能依赖这些东西)。确定的顺序只能在明确地使用了排序步骤之后才能保证。ORDERBY子句用于声明排序顺序:SELECT_select_list_FROM_table_expression_ORDERBY_sort_expression1_[ASC|DESC][NULLS{FIRST|LAST}][,`... 继续阅读 >
2021
01-13

PostgreSQL中的template0和template1库使用实战

postgresql中默认会有三个数据库：postgres、template0、template1。postgres=#\lListofdatabasesName|Owner|Encoding|Collate|Ctype|Accessprivileges-----------+----------+----------+-------------+-------------+-----------------------postgres|postgres|UTF8|en_US.UTF-8|en_US.UTF-8|=T/postgres+|||||postgres=CTc/postgrestemplate0|postgres|UTF8|... 继续阅读 >
2021
01-13

PostgreSQL中的collations用法详解

PostgreSQL中的collations用法详解 与Oracle相比，PostgreSQL对collation的支持依赖于操作系统。以下是基于Centos7.5的测试结果$env|grepLC$env|grepLANGLANG=en_US.UTF-8使用initdb初始化集群的时候，就会使用这些操作系统的配置。postgres=#\lListofdatabasesName|Owner|Encoding|Collate|Ctype|Accessprivileges-----------+----------+----------+-------------+-------------+-----------------------postgr... 继续阅读 >
2021
01-13

PostgreSQL中enable、disable和validate外键约束的实例

我就废话不多说了，大家还是直接看实例吧~postgres=#createtablet1(aintprimarykey,btext,cdate);CREATETABLEpostgres=#createtablet2(aintprimarykey,bintreferencest1(a),ctext);CREATETABLEpostgres=#insertintot1(a,b,c)values(1,'aa',now());INSERT01postgres=#insertintot1(a,b,c)values(2,'bb',now());INSERT01postgres=#insertintot2(a,b,c)values(1,1,'aa');INSERT01postgres=... 继续阅读 >
2021
01-13

浅谈PostgreSQL中的孤儿文件用法(orphaned data files)

浅谈PostgreSQL中的孤儿文件用法(orphaned data files) 创建一个测试表postgres=#createtablet1(aint);CREATETABLEpostgres=#selectpg_relation_filepath('t1');pg_relation_filepath----------------------base/75062/75297(1row)postgres=#在操作系统上已经可以看到该文件。$ls-la$PGDATA/base/75062/75297-rw-------1postgrespostgres0Nov911:11/data/pgdata/11/data/base/75062/75297插入一些数据：postgres=#showsegment_size;segment_size--------------... 继续阅读 >
2021
01-13

解决postgresql insert into select无法使用并行查询的问题

本文信息基于PG13.1。从PG9.6开始支持并行查询。PG11开始支持CREATETABLE…AS、SELECTINTO以及CREATEMATERIALIZEDVIEW的并行查询。先说结论：换用createtableas或者selectinto或者导入导出。首先跟踪如下查询语句的执行计划：selectcount(*)fromtestt1,test1t2wheret1.id=t2.id;postgres=#explainanalyzeselectcount(*)fromtestt1,test1t2wheret1.id=t2.id;... 继续阅读 >
2021
01-13

PostgreSQL 启动失败的解决方案

PostgreSQL 启动失败的解决方案 环境RedHatCloudForms4.x问题postgresql启动失败，并导致evmserverd崩溃。[----]I,[2016-11-29T03:12:31.816753#1201:e4f994]INFO--:MIQ(PostgresAdmin.runcmd_with_logging)Runningcommand...servicerh-postgresql94-postgresqlstart[----]E,[2016-11-29T03:12:32.886621#1201:e4f994]ERROR--:AwesomeSpawn:servicerh-postgresql94-postgresqlstartexitcode:1[----]E,[2016-11-29T03:12:32.886797... 继续阅读 >