数据库升级报ORA-07445 joxchrcnv

朋友在aix平台数据库10.2.0.3升级到10.2.0.5过程中,运行catupgrd.sql脚本,遭遇ORA-07445 joxchrcnv错误

--catupgrd.sql前台报错
Table altered.


Table altered.

BEGIN
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel 


ERROR:
ORA-03114: not connected to ORACLE 

--trace文件报错
*** 2018-08-09 14:41:29.742
ksedmp: internal or fatal error
ORA-07445: exception encountered: core dump [joxchrcnv+00a8] 
   [SIGSEGV] [Address not mapped to object] [0x500000040] [] []
Current SQL statement for this session:
REVOKE all ON SYS.hs_bulk_seq FROM PUBLIC
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
7000000e9942b80         2  anonymous block
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
ksedst+001c          bl       ksedst1              000000000 ? 000000001 ?
ksedmp+0290          bl       ksedst               104C1FC50 ?
ssexhd+03e0          bl       ksedmp               300001D15 ?
0000441C             ?        00000000             
joxcdtu+0088         bl       joxchrcnv            11019B760 ? 7000000E2CEBC70 ?
                                                   FFFFFFFFFFE82A0 ?
                                                   7000000E37EFDC8 ? 000000000 ?
                                                   000000000 ? 000000002 ?
                                                   7000000E1A8B250 ?
joxcnas+007c         bl       joxcdtu              110008BE0 ? 7000000E5FD3670 ?
                                                   FFFFFFFFFFE71C0 ?
                                                   4642408300000004 ?
                                                   104BAF6F0 ? 7000000E6BE6B48 ?
joxsnm_+0020         bl       joxcnas              7000000E5FD4E30 ? 000000001 ?
                                                   000000000 ? 7000000E2CEB8D0 ?
                                                   7000000E991C6E0 ? 11019B760 ?
                                                   FFFFFFFFFFE82E0 ?
joxsnm+0010          bl       03754C58             
kkdlJavasnmAltName+  bl       01FC3B1C             
0048                                               
qcdlgbo+0908         bl       _ptrgl               
qcdlgob+0560         bl       qcdlgbo              FFFFFFFFFFEF8C0 ? 11019B760 ?
                                                   11057B380 ? 7000000E2CEC960 ?
                                                   000000000 ? 104DA95B0 ?
                                                   700000010017FA0 ? 400000000 ?
qcsfgob+0220         bl       qcdlgob              FFFFFFFFFFE8B20 ? 1058251F8 ?
                                                   FFFFFFFFFFE9000 ? 000000001 ?
                                                   00000000B ? 000000000 ?
                                                   0000000C7 ?
qcsprfro+04a4        bl       qcsfgob              1104300C0 ? 000000000 ?
                                                   1100C1848 ? 11019B760 ?
qcsprfro_tree+0454   bl       qcsprfro             000000000 ? 000000000 ?
                                                   FFFFFFFFFFE8DE8 ? 000000000 ?
qcsprfro_tree+0258   bl       qcsprfro_tree        000000001 ? 000000000 ?
                                                   FFFFFFFFFFE8DA0 ? 11057B380 ?
                                                   7000000E1ACA8F8 ? 000000000 ?
qcspafq+0068         bl       qcsprfro_tree        11058DEA0 ? FFFFFFFFFFE9040 ?
                                                   FFFFFFFFFFE8E50 ?
                                                   4822208200000010 ?
                                                   10277AD14 ? 110118F58 ?
qcspqbDescendents+0  bl       qcspafq              000000000 ? 110008BE0 ?
3a0                                                FFFFFFFFFFEFF70 ?
qcspqb+00ac          bl       qcspqbDescendents    10008CC20 ? 000000000 ?
                                                   7000000E2CEC638 ?
kkmdrv+003c          bl       qcspqb               FFFFFFFFFFE9080 ?
                                                   88832081E9999F10 ?
                                                   FFFFFFFFFFE9080 ?
opiSem+1450          bl       kkmdrv               000000000 ? 000001068 ?
                                                   11019E058 ? 11058DA68 ?
opiprs+01ac          bl       opiSem               FFFFFFFFFFEFF70 ? 110502A18 ?
                                                   00000002A ? 000000001 ?
kksParseChildCursor  bl       opiprs               10008CC20 ? 000000000 ?
+0640                                              104C29C50 ? 07FFFFFF8 ?
                                                   000000FE8 ? 000000001 ?
rpiswu2+0368         bl       _ptrgl               
kksLoadChild+20cc    bl       rpiswu2              7000000EAA86FE0 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
                                                   000000000 ? 000000000 ?
kxsGetRuntimeLock+0  bl       kksLoadChild         11019B760 ? 7000000E9AE5520 ?
89c                                                7000000E38176D8 ?
kksfbc+2830          bl       kxsGetRuntimeLock    11019B760 ? 11057B380 ?
                                                   FFFFFFFFFFF18D8 ? 000000000 ?
                                                   000000000 ?
kkspsc0+1570         bl       kksfbc               11057B380 ? 3FFFF2858 ?
                                                   108E381B6C0 ? 110502A18 ?
                                                   00000002A ? 000000000 ?
                                                   000000000 ? 000000000 ?
kksParseCursor+00d4  bl       kkspsc0              110431A68 ? 110502A18 ?
                                                   00000002A ? 310231CF0 ?
                                                   610431A68 ?
                                                   222000000000000A ?
                                                   000000000 ?
opiosq0+0b40         bl       01FC69A4             
opipls+0638          bl       opiosq0              3EA6B0DF8 ? 110008BE0 ?
                                                   110118F58 ?
                                                   22200000EAADBE88 ?
                                                   000000000 ?
opiodr+0b2c          bl       _ptrgl               
rpidrus+01dc         bl       opiodr               6600000000 ? 6E2D017E0 ?
                                                   FFFFFFFFFFF6C80 ? AFFFF5CD0 ?
skgmstack+00c8       bl       _ptrgl               
rpidru+0088          bl       skgmstack            7000000E37EFB20 ?
                                                   FFFFFFFFFFF5C30 ?
                                                   FFFFFFFFFFF6080 ?
                                                   26214040000010F0 ?
                                                   100417334 ?
rpiswu2+0368         bl       _ptrgl               
rpidrv+097c          bl       rpiswu2              7000000EAA86FE0 ? 11019B760 ?
                                                   FFFFFFFFFFF6400 ? 000000004 ?
                                                   7000000E37EFB50 ? 0E9942BE0 ?
                                                   7000000E9942BF0 ? 000000000 ?
psddr0+02dc          bl       01FC6E18             
psdnal+01d0          bl       psddr0               A00000000 ? 6600000000 ?
                                                   FFFFFFFFFFF6C80 ?
                                                   3100000000 ?
pevm_EXIM+0120       bl       _ptrgl               
pfrinstr_EXIM+0034   bl       pevm_EXIM            1105BD288 ? 000000000 ?
pfrrun_no_tool+005c  bl       _ptrgl               
pfrrun+1064          bl       pfrrun_no_tool       5800000000 ? 110231CF0 ?
                                                   FFFFFFFFFFF6FA0 ?
plsql_run+06e8       bl       pfrrun               110449078 ?
peicnt+0244          bl       plsql_run            110449078 ? 1000000000418 ?
                                                   000000000 ?
kkxexe+0250          bl       peicnt               FFFFFFFFFFF82D8 ? 110449078 ?
opiexe+3024          bl       kkxexe               1104DAEC8 ?
kpoal8+0ef0          bl       opiexe               FFFFFFFFFFFB954 ? 1103AA798 ?
                                                   FFFFFFFFFFF9AF0 ?
opiodr+0b2c          bl       _ptrgl               
ttcpip+1020          bl       _ptrgl               
opitsk+117c          bl       01FC6888             
opiino+09d0          bl       opitsk               1EFFFFD920 ? 000000000 ?
opiodr+0b2c          bl       _ptrgl               
opidrv+04a4          bl       opiodr               3C102A7758 ? 404C72B18 ?
                                                   FFFFFFFFFFFF8E0 ? 0102A7750 ?
sou2o+0090           bl       opidrv               3C02A288DC ? 400000010 ?
                                                   FFFFFFFFFFFF8E0 ?
opimai_real+01bc     bl       01FC30F4             
main+0098            bl       opimai_real          000000000 ? 000000000 ?
__start+0098         bl       main                 000000000 ? 000000000 ?
 
--------------------- Binary Stack Dump ---------------------

根据Catupgrd.sql Fails with ORA-3113/ORA-7445 During Grant Select On ALL_JOBS to Public with Grant Option (Doc ID 419309.1)文档,处理建议

1. Make sure LD_LIBRARY_PATH or LIBPATH is set correctly:
  AIX
  ====
  export LIBPATH=$ORACLE_HOME/lib:$LIBPATH

  HP-UX
  =====
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib
  export SHLIB_PATH=$ORACLE_HOME/lib32

  HP Tru-64
  ========
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib

  SUN Sparc Solaris-64Bit
  ==================
  export LD_LIBRARY_PATH_64=$ORACLE_HOME/lib
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib32

  SUN Sparc Solaris-32Bit
  ==================
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib

  Linux
  =====
  export LD_LIBRARY_PATH=$ORACLE_HOME/lib

2. Make sure ORA_NLS parameter  is set correctly. See the following notes for assistance:

Note 77442.1  (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained.

3. Make sure Listener is also started with the correct environment variables if you were doing 
  remote connection from client through sqlnet rather than local connection to database.

4. Shutdown the database and rerun the upgrade:

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
5.  If the above failed then it might be due to the libjox *.a file is not replaced during upgrade or install.

检查该环境发现libjox动态库丢失
libjox


由于软件升级不正常,导致数据库升级出现该问题,在升级软件过程中,一定检查升级软件和升级结果

发表在 Oracle安装升级 | 标签为 | 留下评论

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 | 留下评论

psql: FATAL: no pg_hba.conf entry for host

pg远程登录拒绝

C:\Users\Administrator>psql -h 192.168.222.2 -U u_xifenfei -d db_xifenfei
psql: FATAL:  no pg_hba.conf entry for host "192.168.222.1", user "u_xifenfei", database "db_xifenfei", SSL off

解决方法

vi $PGDATA/pg_hba.conf
--加上
host   all             all           192.168.222.0/24          md5
--重启pg服务
-bash-4.2$ pg_ctl stop
waiting for server to shut down.... done
server stopped
-bash-4.2$ pg_ctl start
waiting for server to start....2018-08-05 23:46:08.237 EDT [44173] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-08-05 23:46:08.237 EDT [44173] LOG:  listening on IPv6 address "::", port 5432
2018-08-05 23:46:08.239 EDT [44173] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-08-05 23:46:08.250 EDT [44173] LOG:  redirecting log output to logging collector process
2018-08-05 23:46:08.250 EDT [44173] HINT:  Future log output will appear in directory "log".
 done
server started

测试远程访问

C:\Users\Administrator>psql -h 192.168.222.2 -U u_xifenfei -d db_xifenfei
用户 u_xifenfei 的口令:
psql (10.4)
输入 "help" 来获取帮助信息.

db_xifenfei=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 行记录)
发表在 PostgreSQL | 留下评论

pg_rman 备份还原测试

通过试验验证pg_rman的基础使用
创建测试环境
模拟创建新表空间,新数据库,新用户,新schema,并且创建表插入测试数据,这样的环境下,pg_rman 备份还原效果

[root@localhost ~]# psql
Password: 
psql.bin (10.4)
Type "help" for help.

postgres=#  CREATE USER u_xifenfei WITH
postgres-#  LOGIN
postgres-#  SUPERUSER
postgres-#  CREATEDB
postgres-#  CREATEROLE
postgres-#  INHERIT
postgres-#  REPLICATION
postgres-#  CONNECTION LIMIT -1
postgres-#  PASSWORD 'xifenfei';
CREATE ROLE
postgres=#  CREATE TABLESPACE tbs_xifenfei
postgres-#    OWNER u_xifenfei
postgres-#    LOCATION '/opt/PostgreSQL/10/tbs_xifenfei';
CREATE TABLESPACE
postgres=#  CREATE DATABASE db_xifenfei
postgres-#      WITH
postgres-#      OWNER = u_xifenfei
postgres-#      ENCODING = 'UTF8'
postgres-#      TABLESPACE = tbs_xifenfei
postgres-#      CONNECTION LIMIT = -1;
CREATE DATABASE
postgres=# \q
[root@localhost ~]# psql -U u_xifenfei
Password for user u_xifenfei: 
psql.bin (10.4)
Type "help" for help.

postgres=# \q
[root@localhost ~]# psql -U u_xifenfei -d db_xifenfei
Password for user u_xifenfei: 
psql.bin (10.4)
Type "help" for help.

db_xifenfei=# 
db_xifenfei=# 
db_xifenfei=# create schema u_xifenfei;
CREATE SCHEMA
db_xifenfei=# create table t_xifenfei as select * from pg_tables;
SELECT 69
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 69
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 138
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 276
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 552
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 1104
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 2208
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 4416
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 8832
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 17664
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 35328
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 70656
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 141312
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 282624
db_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 565248
db_xifenfei=# select count(*) from t_xifenfei;
  count  
---------
 1130496
(1 row)

db_xifenfei=# 

第一次全备数据库

[root@localhost backup]# pg_rman backup --backup-mode=full \
[root@localhost backup]# --backup-path=/backup -d db_xifenfei -U u_xifenfei -h 127.0.0.1
Password for user u_xifenfei: 
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

模拟继续插入数据
t_xifenfei表一共有数据2260992条

b_xifenfei=# insert into t_xifenfei select * from t_xifenfei;
INSERT 0 1130496
db_xifenfei=# select count(*) from t_xifenfei;
  count  
---------
 2260992
(1 row)

查看全备情况

[root@localhost 10]#  pg_rman show --backup-path=/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2018-08-05 10:47:29  2018-08-05 10:47:43  FULL   611MB     1  DONE

备份归档日志

[root@localhost 10]# pg_rman backup --backup-mode=archive \
[root@localhost 10]# --backup-path=/backup -d db_xifenfei -U u_xifenfei -h 127.0.0.1
Password for user u_xifenfei: 
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.

检查全备和归档备份情况

[root@localhost 10]#  pg_rman show --backup-path=/backup
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status 
=====================================================================
2018-08-05 10:54:51  2018-08-05 10:56:07  ARCH   620MB     1  DONE
2018-08-05 10:47:29  2018-08-05 10:47:43  FULL   611MB     1  DONE

停掉pg

[root@localhost data]# su - postgres
Last login: Sun Aug  5 02:19:57 EDT 2018 on pts/1
-bash-4.2$ source pg_env.sh 
-bash-4.2$ pg_ctl stop
waiting for server to shut down.... done
server stopped
-bash-4.2$ ps -ef|grep postgres
root      39902  30494  0 11:05 pts/0    00:00:00 su - postgres
postgres  39903  39902  0 11:05 pts/0    00:00:00 -bash
postgres  40021  39903  0 11:06 pts/0    00:00:00 ps -ef
postgres  40022  39903  0 11:06 pts/0    00:00:00 grep --color=auto postgres

删除原库并创建相关目录
注意:对应的空间目录权限为700,所有者和组为postgres

[root@localhost 10]# pwd
/opt/PostgreSQL/10
[root@localhost 10]# mv data data_bak
[root@localhost 10]# mv tbs_xifenfei tbs_xifenfei_bak
[root@localhost 10]# mkdir data
[root@localhost 10]# mkdir tbs_xifenfei
[root@localhost 10]# chmod 700 data
[root@localhost 10]# chmod 700 tbs_xifenfei
[root@localhost 10]# chown postgres:postgres data
[root@localhost 10]# chown postgres:postgres tbs_xifenfei

还原数据库

-bash-4.2$ pg_rman restore --backup-path=/backup
WARNING: pg_controldata file "/opt/PostgreSQL/10/data/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2018-08-05 10:47:29"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2018-08-05 10:47:29" backup and archive log files by SIZE
INFO: backup "2018-08-05 10:47:29" is valid
INFO: restoring database files from the full mode backup "2018-08-05 10:47:29"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2018-08-05 10:47:29" is valid
INFO: restoring WAL files from backup "2018-08-05 10:47:29"
INFO: restoring online WAL files and server log files
INFO: generating recovery.conf
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.

启动pg

-bash-4.2$ pg_ctl start
waiting for server to start....2018-08-05 11:23:40.190 EDT [40855] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-08-05 11:23:40.190 EDT [40855] LOG:  listening on IPv6 address "::", port 5432
2018-08-05 11:23:40.193 EDT [40855] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-08-05 11:23:40.200 EDT [40855] LOG:  redirecting log output to logging collector process
2018-08-05 11:23:40.200 EDT [40855] HINT:  Future log output will appear in directory "log".
 done
server started

-bash-4.2$ ps -ef|grep postgres
root      40328  21806  0 11:14 pts/3    00:00:00 su - postgres
postgres  40329  40328  0 11:14 pts/3    00:00:00 -bash
postgres  40855      1  0 11:23 pts/3    00:00:00 /opt/PostgreSQL/10/bin/postgres
postgres  40856  40855  0 11:23 ?        00:00:00 postgres: logger process   
postgres  40857  40855 57 11:23 ?        00:00:16 postgres: startup process   waiting for 000000010000000000000025
postgres  40859  40855  0 11:23 ?        00:00:00 postgres: checkpointer process  
postgres  40860  40855  7 11:23 ?        00:00:02 postgres: writer process   
postgres  40862  40855  0 11:23 ?        00:00:00 postgres: stats collector process  
postgres  40892  40329  0 11:24 pts/3    00:00:00 ps -ef
postgres  40893  40329  0 11:24 pts/3    00:00:00 grep --color=auto postgres
-bash-4.2$ 

验证数据库还原结果

-bash-4.2$ psql -U u_xifenfei -d db_xifenfei
Password for user u_xifenfei: 
psql.bin (10.4)
Type "help" for help.

db_xifenfei=#  select count(*) from t_xifenfei;
  count  
---------
 2260992
(1 row)

db_xifenfei=# 

破坏环境之前表条数和还原之后完全匹配,证pg_rman在功能上备份恢复没有问题

发表在 PostgreSQL | 标签为 , | 留下评论

PostgreSQL修改归档模式

pg版本

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

归档配置参数

--pgdata中的postgresql.conf
wal_level = replica 
archive_mode = on 
archive_command = 'test ! -f /pg_archivedir/%f && cp %p /pg_archivedir/%f'   
--重启pg systemctl restart postgresql-10.service 

postgres=# show wal_level;
 wal_level 
-----------
 replica
(1 row)

postgres=# show archive_mode;
 archive_mode 
--------------
 on
(1 row)

postgres=# show archive_command;
                    archive_command                     
--------------------------------------------------------
 test ! -f /pg_archivedir/%f && cp %p /pg_archivedir/%f
(1 row)

测试归档效果

[root@localhost pg_wal]# ps -ef|grep "postgres: archiver process"
postgres  35300  35293  0 08:43 ?        00:00:00 postgres: archiver process   last was 000000010000000000000004
root      37504  20853  0 09:55 pts/1    00:00:00 grep --color=auto postgres: archiver process
[root@localhost pg_wal]# pwd
/opt/PostgreSQL/10/data/pg_wal
[root@localhost pg_wal]# ls -ltr
total 49156
-rw------- 1 postgres postgres      309 Aug  5 06:05 000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Aug  5 08:43 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Aug  5 08:50 000000010000000000000004
drwx------ 2 postgres postgres       94 Aug  5 08:50 archive_status
-rw------- 1 postgres postgres 16777216 Aug  5 08:53 000000010000000000000005

[root@localhost pg_wal]# ls -l /pg_archivedir/
total 65540
-rw------- 1 postgres postgres 16777216 Aug  5 08:37 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Aug  5 08:37 000000010000000000000002
-rw------- 1 postgres postgres      309 Aug  5 08:37 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Aug  5 08:43 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Aug  5 08:50 000000010000000000000004

--切换日志
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/5000158
(1 row)

[root@localhost pg_wal]# ps -ef|grep "postgres: archiver process"
postgres  35300  35293  0 08:43 ?        00:00:00 postgres: archiver process   last was 000000010000000000000005
root      37531  20853  0 09:56 pts/1    00:00:00 grep --color=auto postgres: archiver process
[root@localhost pg_wal]# ls -ltr
total 49156
-rw------- 1 postgres postgres      309 Aug  5 06:05 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Aug  5 08:50 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Aug  5 09:55 000000010000000000000005
drwx------ 2 postgres postgres      130 Aug  5 09:55 archive_status
-rw------- 1 postgres postgres 16777216 Aug  5 09:55 000000010000000000000006

[root@localhost pg_wal]# ls -l /pg_archivedir/
total 81924
-rw------- 1 postgres postgres 16777216 Aug  5 08:37 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Aug  5 08:37 000000010000000000000002
-rw------- 1 postgres postgres      309 Aug  5 08:37 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Aug  5 08:43 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Aug  5 08:50 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Aug  5 09:55 000000010000000000000005
发表在 PostgreSQL | 标签为 | 留下评论