标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,702)
- DB2 (22)
- MySQL (74)
- Oracle (1,563)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (68)
- Oracle Bug (8)
- Oracle RAC (53)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (571)
- Oracle安装升级 (94)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (81)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- Oracle各种类型坏块说明和处理
- fio测试io,导致磁盘文件系统损坏故障恢复
- ORA-742 写丢失常见bug记录
- Oracle 19c 202501补丁(RUs+OJVM)
- 避免 19c 数据库性能问题需要考虑的事项 (Doc ID 3050476.1)
- Bug 21915719 Database hang or may fail to OPEN in 12c IBM AIX or HPUX Itanium – ORA-742, DEADLOCK or ORA-600 [kcrfrgv_nextlwn_scn] ORA-600 [krr_process_read_error_2]
- ORA-600 ktuPopDictI_1恢复
- impdp导入数据丢失sys授权问题分析
- impdp 创建index提示ORA-00942: table or view does not exist
- 数据泵导出 (expdp) 和导入 (impdp)工具性能降低分析参考
- 19c非归档数据库断电导致ORA-00742故障恢复
- Oracle 19c – 手动升级到 Non-CDB Oracle Database 19c 的完整核对清单
- sqlite数据库简单操作
- Oracle 暂定和恢复功能
- .pzpq扩展名勒索恢复
- Oracle read only用户—23ai新特性:只读用户
- 迁移awr快照数据到自定义表空间
- .hmallox加密mariadb/mysql数据库恢复
- 2025年首个故障恢复—ORA-600 kcbzib_kcrsds_1
- 第一例Oracle 21c恢复咨询
分类目录归档:PostgreSQL
PostgreSQL恢复系列:pg_control异常恢复
在PG中pg_control文件类似oracle数据库的control文件(控制文件),在Oracle中如果该文件丢失/损坏,可以通过alter database create controlfile命令进行创建,对于PG数据库来说也可以通过pg_resetwal命令来实现创建,由于pg_control文件损坏,需要人工指定一些参数完成pg_resetwal相关操作
pg_resetwal 使用说明
-bash-4.2$ pg_resetwal --help pg_resetwal resets the PostgreSQL write-ahead log. Usage: pg_resetwal [OPTION]... DATADIR Options: -c, --commit-timestamp-ids=XID,XID set oldest and newest transactions bearing commit timestamp (zero means no change) [-D, --pgdata=]DATADIR data directory -e, --epoch=XIDEPOCH set next transaction ID epoch -f, --force force update to be done -l, --next-wal-file=WALFILE set minimum starting location for new WAL -m, --multixact-ids=MXID,MXID set next and oldest multitransaction ID -n, --dry-run no update, just show what would be done -o, --next-oid=OID set next OID -O, --multixact-offset=OFFSET set next multitransaction offset -u, --oldest-transaction-id=XID set oldest transaction ID -V, --version output version information, then exit -x, --next-transaction-id=XID set next transaction ID --wal-segsize=SIZE size of WAL segments, in megabytes -?, --help show this help, then exit Report bugs to <pgsql-bugs@lists.postgresql.org>. PostgreSQL home page: <https://www.postgresql.org/>
确认现在业务表记录情况
-bash-4.2$ psql psql (14.3) Type "help" for help. postgres=# select count(1) from ac_event; count -------- 246266 (1 row)
模拟pg_control文件异常
-bash-4.2$ ps -ef|grep postgres postgres 37178 1 0 09:58 ? 00:00:00 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data postgres 37179 37178 0 09:58 ? 00:00:00 postgres: logger postgres 37181 37178 0 09:58 ? 00:00:00 postgres: checkpointer postgres 37182 37178 0 09:58 ? 00:00:00 postgres: background writer postgres 37183 37178 0 09:58 ? 00:00:00 postgres: walwriter postgres 37184 37178 0 09:58 ? 00:00:00 postgres: autovacuum launcher postgres 37185 37178 0 09:58 ? 00:00:00 postgres: stats collector postgres 37186 37178 0 09:58 ? 00:00:00 postgres: logical replication launcher root 41368 41314 0 11:06 pts/1 00:00:00 su - postgres postgres 41369 41368 0 11:06 pts/1 00:00:00 -bash postgres 45071 41369 0 12:07 pts/1 00:00:00 ps -ef postgres 45072 41369 0 12:07 pts/1 00:00:00 grep --color=auto postgres -bash-4.2$ kill -9 37178 -bash-4.2$ ps -ef|grep postgres root 41368 41314 0 11:06 pts/1 00:00:00 su - postgres postgres 41369 41368 0 11:06 pts/1 00:00:00 -bash postgres 45095 41369 0 12:08 pts/1 00:00:00 ps -ef postgres 45096 41369 0 12:08 pts/1 00:00:00 grep --color=auto postgres -bash-4.2$ pwd /var/lib/pgsql/14/data/global -bash-4.2$ ls -l pg_control -rw-------. 1 postgres postgres 8192 May 30 12:04 pg_control -bash-4.2$ rm -rf pg_control -bash-4.2$ ls -l pg_control ls: cannot access pg_control: No such file or directory
PG启动失败
-bash-4.2$ pg_ctl start pg_ctl: another server might be running; trying to start server anyway waiting for server to start....postgres: could not find the database system Expected to find it in the directory "/var/lib/pgsql/14/data", but could not open file "/var/lib/pgsql/14/data/global/pg_control": No such file or directory stopped waiting pg_ctl: could not start server Examine the log output.
创建空pg_control文件启动依旧失败
-bash-4.2$ touch /var/lib/pgsql/14/data/global/pg_control -bash-4.2$ pg_ctl start pg_ctl: another server might be running; trying to start server anyway waiting for server to start....2022-05-30 12:09:43.953 CST [45215] PANIC: could not read file "global/pg_control": read 0 of 296 stopped waiting pg_ctl: could not start server Examine the log output.
设置next-wal-file
-l, –next-wal-file=WALFILE,这个参数设置下一个新的WAL文件的最小值,这个值可以从$PGDATA/pg_wal目录下去看最后一个WAL 文件,这个文件的id+1即可
-bash-4.2$ pwd /var/lib/pgsql/14/data/pg_wal -bash-4.2$ ls -l total 16384 -rw-------. 1 postgres postgres 16777216 May 30 12:04 000000010000000000000014 drwx------. 2 postgres postgres 6 May 24 02:20 archive_status -bash-4.2$
这个文件+1,-l 000000010000000000000015
设置next-transaction
-x, –next-transaction-id=XID,这个参数设置pg_control中的下一个XID的值,这个值可以从pg_xact目录下的文件中查询
-bash-4.2$ pwd /var/lib/pgsql/14/data/pg_xact -bash-4.2$ ls -ltr total 8 -rw-------. 1 postgres postgres 8192 May 30 12:03 0000
最后一个是0000,那么下一个XID就是0001,然后乘以 1048576 (0×100000),实际上后面直接加5个0就行了。注意,这个值是16进制的。-x 0×000100000
multixact-ids设置
-m, –multixact-ids=MXID1,MXID2,这个参数包含两个部分,MXID1和MXID2,都可以从$PGDATA/pg_multixact/offsets目录下获得。MXID1的值,首先找到最大值,+1,再乘以 65536 (0×10000,相当于后面加4个0)作为这个参数的前半部分。找到最小的值,后面加4个0,作为MXID2的值
-bash-4.2$ pwd /var/lib/pgsql/14/data/pg_multixact/offsets -bash-4.2$ ls -ltr total 8 -rw-------. 1 postgres postgres 8192 May 29 22:06 0000 -bash-4.2$
-m 0×00010000, 0×00000000(由于oldest multitransaction ID不能为0,因此后续这个值需要适当调整)
multixact-offset设置
-O, –multixact-offset=OFFSET,这个参数可以从$PGDATA/pg_multixact/members目录下获得。找到最大值,+1,乘以 52352 (0xCC80)
-bash-4.2$ pwd /var/lib/pgsql/14/data/pg_multixact/members -bash-4.2$ ls -ltr total 8 -rw-------. 1 postgres postgres 8192 May 24 02:20 0000
-O 0xCC80
尝试执行pg_resetwal
-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00010000,0x00000000 -O 0xCC80 $PGDATA pg_resetwal: error: oldest multitransaction ID (-m) must not be 0
multixact-ids值不对,进行调整后处理
postmaster.pid文件需要清理
由于PG库异常关闭,需要人工清理掉该文件
-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 $PGDATA pg_resetwal: error: lock file "postmaster.pid" exists -bash-4.2$ rm -rf postmaster.pid
pg_resetwal结果预览
-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 $PGDATA pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it Guessed pg_control values: pg_control version number: 1300 Catalog version number: 202107181 Database system identifier: 7103392535324046312 Latest checkpoint's TimeLineID: 1 Latest checkpoint's full_page_writes: off Latest checkpoint's NextXID: 0:3 Latest checkpoint's NextOID: 12000 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID: 3 Latest checkpoint's oldestXID's DB: 0 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 0 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 Maximum data alignment: 8 Database block size: 8192 Blocks per segment of large relation: 131072 WAL block size: 8192 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum columns in an index: 32 Maximum size of a TOAST chunk: 1996 Size of a large-object chunk: 2048 Date/time type storage: 64-bit integers Float8 argument passing: by value Data page checksum version: 0 Values to be changed: First log segment after reset: 000000010000000000000015 NextMultiXactId: 131072 OldestMultiXid: 65536 OldestMulti's DB: 0 NextMultiOffset: 52352 NextXID: 1048576 OldestXID: 3 OldestXID's DB: 0 If these values seem acceptable, use -f to force reset.
pg_resetwal进行创建pg_control并启动PG
-bash-4.2$ pg_resetwal -l 000000010000000000000015 -x 0x000100000 -m 0x00020000,0x00010000 -O 0xCC80 -f $PGDATA pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it Write-ahead log reset -bash-4.2$ pg_ctl start waiting for server to start....2022-05-30 13:33:28.266 CST [51437] LOG: redirecting log output to logging collector process 2022-05-30 13:33:28.266 CST [51437] HINT: Future log output will appear in directory "log". done server started
验证数据
-bash-4.2$ psql psql (14.3) Type "help" for help. postgres=# select count(1) from ac_event; count -------- 245275 (1 row)
这种方法恢复之后,建议理解dump数据,然后导入到新库中
PostgreSQL恢复系列:wal日志丢失恢复
WAL是Write Ahead Log的简写,和oracle的redo日志类似,存放在$PGDATA/pg_xlog中,10版本以后在$PGDATA/pg_wal目录.在oracle数据库中,如果redo丢失,分为active/current和inactive的redo,分别有不同的处理方式,对于oracle需要实例恢复的redo丢失,需要屏蔽数据库一致性,强制打开数据库,对于PG数据库这部分日志丢失该如何恢复,主要是通过pg_resetwal/pg_resetxlog(10以前版本)命令来实现,这里通过一个测试来验证
创建测试表并强制kill数据库
-bash-4.2$ psql psql (14.3) Type "help" for help. postgres=# create table t_xifenfei as select * from pg_database; SELECT 4 postgres=# select count(1) from t_xifenfei; count ------- 4 (1 row) postgres=# \q -bash-4.2$ ps -ef|grep post root 1819 1 0 May28 ? 00:00:00 /usr/libexec/postfix/master -w postfix 1838 1819 0 May28 ? 00:00:00 qmgr -l -t unix -u postgres 11102 1 0 05:49 ? 00:00:00 /usr/pgsql-14/bin/postgres -D /var/lib/pgsql/14/data postgres 11103 11102 0 05:49 ? 00:00:00 postgres: logger postgres 11105 11102 0 05:49 ? 00:00:00 postgres: checkpointer postgres 11106 11102 0 05:49 ? 00:00:00 postgres: background writer postgres 11107 11102 0 05:49 ? 00:00:00 postgres: walwriter postgres 11108 11102 0 05:49 ? 00:00:00 postgres: autovacuum launcher postgres 11109 11102 0 05:49 ? 00:00:01 postgres: stats collector postgres 11110 11102 0 05:49 ? 00:00:00 postgres: logical replication launcher root 22743 22300 0 18:26 pts/3 00:00:00 su - postgres postgres 22744 22743 0 18:26 pts/3 00:00:00 -bash postgres 22937 22744 0 18:28 pts/3 00:00:00 psql postgres 22938 11102 0 18:28 ? 00:00:00 postgres: postgres postgres [local] idle postfix 32623 1819 0 21:10 ? 00:00:00 pickup -l -t unix -u root 33032 32912 0 21:15 pts/2 00:00:00 su - postgres postgres 33033 33032 0 21:15 pts/2 00:00:00 -bash postgres 35210 33033 0 21:51 pts/2 00:00:00 ps -ef postgres 35211 33033 0 21:51 pts/2 00:00:00 grep --color=auto post -bash-4.2$ kill -9 11102
删除wal日志
-bash-4.2$ pwd /var/lib/pgsql/14/data/pg_wal -bash-4.2$ ls -ltr total 311296 drwx------. 2 postgres postgres 6 May 24 02:20 archive_status -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000014 -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000015 -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000016 -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000017 -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000018 -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000019 -rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001A -rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001B -rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001C -rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001D -rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001E -rw-------. 1 postgres postgres 16777216 May 28 21:29 00000001000000000000001F -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000020 -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000021 -rw-------. 1 postgres postgres 16777216 May 28 21:29 000000010000000000000022 -rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000023 -rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000024 -rw-------. 1 postgres postgres 16777216 May 28 21:30 000000010000000000000025 -rw-------. 1 postgres postgres 16777216 May 29 21:51 000000010000000000000013 -bash-4.2$ rm -rf 0000000100000000000000* -bash-4.2$ ls archive_status
查询当时数据库需要的最小wal记录
-bash-4.2$ pg_controldata pg_control version number: 1300 Catalog version number: 202107181 Database system identifier: 7100998319216817119 Database cluster state: in production pg_control last modified: Sat 28 May 2022 09:36:11 PM CST Latest checkpoint location: 0/13692F80 Latest checkpoint's REDO location: 0/13692F48 Latest checkpoint's REDO WAL file: 000000010000000000000013 <===需要的记录 Latest checkpoint's TimeLineID: 1 Latest checkpoint's PrevTimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0:17824 Latest checkpoint's NextOID: 32769 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID: 727 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 17824 Latest checkpoint's oldestMultiXid: 1 Latest checkpoint's oldestMulti's DB: 1 Latest checkpoint's oldestCommitTsXid:0 Latest checkpoint's newestCommitTsXid:0 Time of latest checkpoint: Sat 28 May 2022 09:31:41 PM CST
尝试启动PG
-bash-4.2$ pg_ctl start pg_ctl: another server might be running; trying to start server anyway waiting for server to start....2022-05-29 21:52:22.926 CST [35270] LOG: redirecting log output to logging collector process 2022-05-29 21:52:22.926 CST [35270] HINT: Future log output will appear in directory "log". . stopped waiting pg_ctl: could not start server Examine the log output.
启动pg失败,查看日志记录
2022-05-29 21:52:22.926 CST [35270] LOG: starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2022-05-29 21:52:22.927 CST [35270] LOG: listening on IPv6 address "::1", port 5432 2022-05-29 21:52:22.927 CST [35270] LOG: listening on IPv4 address "127.0.0.1", port 5432 2022-05-29 21:52:22.929 CST [35270] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2022-05-29 21:52:22.931 CST [35270] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-05-29 21:52:22.936 CST [35272] LOG: database system was interrupted; last known up at 2022-05-28 21:36:11 CST 2022-05-29 21:52:23.049 CST [35272] LOG: invalid primary checkpoint record 2022-05-29 21:52:23.049 CST [35272] PANIC: could not locate a valid checkpoint record 2022-05-29 21:52:24.211 CST [35270] LOG: startup process (PID 35272) was terminated by signal 6: Aborted 2022-05-29 21:52:24.211 CST [35270] LOG: aborting startup due to startup process failure 2022-05-29 21:52:24.218 CST [35270] LOG: database system is shut down
错误比较明显,无法定位到有效的checkpoint记录,在oracle里面的意思可以理解为无法进行实例恢复,pg启动失败
重设wal
由于数据库为不一致状态,需要使用-f进行强制重设
-bash-4.2$ pg_resetwal $PGDATA The database server was not shut down cleanly. Resetting the write-ahead log might cause data to be lost. If you want to proceed anyway, use -f to force reset. -bash-4.2$ pg_resetwal -f $PGDATA Write-ahead log reset
启动PG成功
-bash-4.2$ pg_ctl start -D $PGDATA waiting for server to start....2022-05-29 22:01:02.647 CST [37178] LOG: redirecting log output to logging collector process 2022-05-29 22:01:02.647 CST [37178] HINT: Future log output will appear in directory "log". done server started
日志记录
2022-05-29 22:01:02.647 CST [37178] LOG: starting PostgreSQL 14.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit 2022-05-29 22:01:02.648 CST [37178] LOG: listening on IPv6 address "::1", port 5432 2022-05-29 22:01:02.648 CST [37178] LOG: listening on IPv4 address "127.0.0.1", port 5432 2022-05-29 22:01:02.649 CST [37178] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2022-05-29 22:01:02.651 CST [37178] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2022-05-29 22:01:02.653 CST [37180] LOG: database system was shut down at 2022-05-29 22:00:47 CST 2022-05-29 22:01:02.661 CST [37178] LOG: database system is ready to accept connections
查看wal日志,产生新记录
-bash-4.2$ pwd /var/lib/pgsql/14/data/pg_wal -bash-4.2$ ls -ltr total 16384 drwx------. 2 postgres postgres 6 May 24 02:20 archive_status -rw-------. 1 postgres postgres 16777216 May 29 22:01 000000010000000000000014
验证刚刚创建测试表
-bash-4.2$ psql psql (14.3) Type "help" for help. postgres=# select count(1) from t_xifenfei; ERROR: relation "t_xifenfei" does not exist LINE 1: select count(1) from t_xifenfei; ^
由于需要进行实例恢复的wal日志丢失导致这表记录也丢失.由此可见这类操作可能导致数据丢失风险,对于生产环境,需要慎重,
PostgreSQL常见字符串操作函数
转载:pmars blog中关于pg字符串相关函数
函数:string || string 说明:String concatenation 字符串连接操作 例子:select 'Post' || 'greSQL'; = PostgreSQL 函数:string || non-string or non-string || string 说明:String concatenation with one non-string input 字符串与非字符串类型进行连接操作 例子:select 'Value: ' || 42; = Value: 42 函数:bit_length(string) 说明:Number of bits in string 计算字符串的位数 例子:select bit_length('pmars') = 40 函数:char_length(string) or character_length(string) 说明:Number of characters in string 计算字符串中字符个数 例子:select char_length('pmars'); = 5 函数:lower(string) 说明:Convert string to lower case 转换字符串为小写 例子:select lower('PmArS'); = "pmars" 函数:octet_length(string) 说明:Number of bytes in string 计算字符串的字节数 例子:select octet_length('我是pmars'); = 11 select octet_length('我'); = 3 函数:overlay(string placing string from int [for int]) 说明:Replace substring 替换字符串中任意长度的子字串为新字符串 例子:select overlay('I am pmars' placing 'ming' from 6 for 5); = "I am ming" 函数:position(substring in string) 说明:Location of specified substring 子串在一字符串中的位置 例子:select position('ma' in 'pmars'); = 2 函数:substring(string [from int] [for int]) 说明:Extract substring 截取任意长度的子字符串 例子:select substring('topmars' from 3 for 3); = "pma" 函数:substring(string from pattern) 说明:Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching. 利用正则表达式对一字符串进行任意长度的字串的截取 例子:select substring('topmars' from 'p.*$'); = "pmars" 函数:substring(string from pattern for escape) 说明:Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching. 利于正则表达式对某类字符进行删除,以得到子字符串 例子:select substring('Thomas' from '%#"o_a#"_' for '#'); = "oma" 函数:trim([leading | trailing | both] [characters] from string) 说明:Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string 去除尽可能长开始,结束或者两边的某类字符,默认为去除空白字符,当然可以自己指定,可同时指定多个要删除的字符串 例子:select trim(leading 'p' from 'pmars'); = "mars" 函数:upper(string) 说明:Convert string to uppercase 将字符串转换为大写 例子:select upper('pmars'); = "PMARS" 函数:ascii(string) 说明:ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings. the argument must be a strictly ASCII character. 得到某一个字符的Assii值 例子:select ascii('pmars'); = select ascii('p'); = 112 函数:btrim(string text [, characters text]) 说明:Remove the longest string consisting only of characters in characters (a space by default) from the start and end of string 去除字符串两边的所有指定的字符,可同时指定多个字符 例子:select btrim('pmars','prs'); = "ma" 函数:chr(int) 说明:Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate a strictly ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes. 得到某ACSII值对应的字符 例子:select chr(65); = A 函数:convert(string bytea, src_encoding name, dest_encoding name) 说明:Convert string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See Table 9-7 for available conversions. 转换字符串编码,指定源编码与目标编码 例子:select convert('我是pmars_in_utf8', 'UTF8', 'GBK'); = "\316\322\312\307pmars_in_utf8" 函数:convert_from(string bytea, src_encoding name) 说明:Convert string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. 转换字符串编码,自己要指定源编码,目标编码默认为数据库指定编码, 例子:select convert_from('\316\322\312\307pmars','GBK'); = "我是pmars" 函数:convert_to(string text, dest_encoding name) 说明:Convert string to dest_encoding.转换字符串编码,源编码默认为数据库指定编码,自己要指定目标编码, 例子:select convert_to('我是pmars_in_utf8','GBK'); = "\316\322\312\307pmars_in_utf8" 函数:decode(string text, type text) 说明:Decode binary data from string previously encoded with encode. Parameter type is same as in encode. 对字符串按指定的类型进行解码 例子:select decode('MTIzAAE=', 'base64'); = "123\000\001" 函数:encode(data bytea, type text) 说明:Encode binary data to different representation. Supported types are: base64, hex, escape. Escape merely outputs null bytes as \000 and doubles backslashes. 与decode相反,对字符串按指定类型进行编码 例子:select encode('123\000\001','base64'); = "MTIzAAE=" 函数:initcap(string) 说明:Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. 将字符串所有的单词进行格式化,首字母大写,其它为小写 例子:select initcap('I AM PMARs'); = "I Am Pmars" 函数:length(string) 说明:Number of characters in string 讲算字符串长度 例子:select length('我是pmars'); = 7 函数:length(stringbytea, encoding name ) 说明:Number of characters in string in the given encoding. The string must be valid in this encoding. 计算字符串长度,指定字符串使用的编码 例子:select length('我是pmars','GBK'); = 8 函数:lpad(string text, length int [, fill text]) 说明:Fill up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right). 对字符串左边进行某类字符自动填充,即不足某一长度,则在左边自动补上指定的字符串,直至达到指定长度,可同时指定多个自动填充的字符 例子:select lpad('pmars', 10, 'to'); = "tototpmars" 函数:ltrim(string text [, characters text]) 说明:Remove the longest string containing only characters from characters (a space by default) from the start of string 删除字符串左边某一些的字符,可以时指定多个要删除的字符 例子:select ltrim('pmars','amp'); = "rs" 函数:md5(string) 说明:Calculates the MD5 hash of string, returning the result in hexadecimal 将字符串进行md5编码 例子:select md5('pmars'); = "1018ceb949f1472f7252f7da1f5eff42" 函数:pg_client_encoding() 说明:Current client encoding name 得到pg客户端编码 例子:select pg_client_encoding(); = "UTF8" 函数:quote_ident(string text) 说明:Return the given string suitably quoted to be used as an identifier in an SQL statement string. Quotes are added only if necessary (i.e., if the string contains non-identifier characters or would be case-folded). Embedded quotes are properly doubled. 对某一字符串加上两引号 例子:quote_ident('Foo bar') = "Foo bar" 函数:quote_literal(string text) 说明:Return the given string suitably quoted to be used as a string literal in an SQL statement string. Embedded single-quotes and backslashes are properly doubled. 对字符串里两边加上单引号,如果字符串里面出现sql编码的单个单引号,则会被表达成两个单引号 例子:quote_literal('O\'Reilly') = 'O''Reilly' 函数:quote_literal(value anyelement) 说明:Coerce the given value to text and then quote it as a literal. Embedded single-quotes and backslashes are properly doubled. 将一数值转换为字符串,并为其两边加上单引号,如果数值中间出现了单引号,也会被表示成两个单引号 例子:quote_literal(42.5) = '42.5' 函数:regexp_matches(string text, pattern text [, flags text]) 说明:Return all captured substrings resulting from matching a POSIX regular expression against the string. See Section 9.7.3 for more information. 对字符串按正则表达式进行匹配,如果存在则会在结果数组中表示出来 例子:regexp_matches('foobarbequebaz', '(bar)(beque)') = {bar,beque} 函数:regexp_replace(string text, pattern text, replacement text [, flags text]) 说明:Replace substring(s) matching a POSIX regular expression. See Section 9.7.3 for more information. 利用正则表达式对字符串进行替换 例子:regexp_replace('Thomas', '.[mN]a.', 'M') = ThM 函数:regexp_split_to_array(string text, pattern text [, flags text ]) 说明:Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information. 利用正则表达式将字符串分割成数组 例子:regexp_split_to_array('hello world', E'\\s+') = {hello,world} 函数:regexp_split_to_table(string text, pattern text [, flags text]) 说明:Split string using a POSIX regular expression as the delimiter. See Section 9.7.3 for more information. 利用正则表达式将字符串分割成表格 例子:regexp_split_to_table('hello world', E'\\s+') = hello world (2 rows) 函数:repeat(string text, number int) 说明:Repeat string the specified number of times 重复字符串一指定次数 例子:repeat('Pg', 4) = PgPgPgPg 函数:replace(string text, from text, to text) 说明:Replace all occurrences in string of substring from with substring to 将字符的某一子串替换成另一子串 例子:('abcdefabcdef', 'cd', 'XX') = abXXefabXXef 函数:rpad(string text, length int [, fill text]) 说明:Fill up the string to length length by appending the characters fill (a space by default). If the string is already longer than length then it is truncated. 对字符串进行填充,填充内容为指定的字符串 例子:rpad('hi', 5, 'xy') = hixyx 函数:rtrim(string text [, characters text]) 说明:Remove the longest string containing only characters from characters (a space by default) from the end of string 去除字符串右边指定的字符 例子:rtrim('trimxxxx', 'x') = trim 函数:split_part(string text, delimiter text, field int) 说明:Split string on delimiter and return the given field (counting from one) 对字符串按指定子串进行分割,并返回指定的数值位置的值 例子:split_part('abc~@~def~@~ghi', '~@~', 2) = def 函数:strpos(string, substring) 说明:Location of specified substring (same as position(substring in string), but note the reversed argument order) 指定字符串在目标字符串的位置 例子:strpos('high', 'ig') = 2 函数:substr(string, from [, count]) 说明:Extract substring (same as substring(string from from for count)) 截取子串 例子:substr('alphabet', 3, 2) = ph 函数:to_ascii(string text [, encoding text]) 说明:Convert string to ASCII from another encoding (only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings) 将字符串转换成ascii编码字符串 例子:to_ascii('Karel') = Karel 函数:to_hex(number int or bigint) 说明:Convert number to its equivalent hexadecimal representation 对数值进行十六进制编码 例子:to_hex(2147483647) = 7fffffff 函数:translate(string text, from text, to text) 说明:Any character in string that matches a character in the from set is replaced by the corresponding character in the to set 将字符串中某些匹配的字符替换成指定字符串,目标字符与源字符都可以同时指定多个 例子:translate('12345', '14', 'ax') = a23x5
转载:https://www.cnblogs.com/pmars/archive/2013/02/17/2914046.html
发表在 PostgreSQL
评论关闭