PostgreSQL学习实践:逻辑备份工具pg_dump与pg_dumpall

软件信息:

操作系统 rhel-server-7.6
数据库版本 postgresql-15.3
备份文件路径 /backup

逻辑备份:

包括:pg_dump,pg_dumpall等。
pg_dump优点:
即使数据库正在被并发使用,它也能创建一致的备份。pg_dump不阻塞其他用户访问数据库。
可以支持跨版本平台数据导入。
pg_dump缺点:
只能备份单个数据库。
同oracle的逻辑备份相似,无法恢复到故障发生的时刻。

pg_dumpall优点:
可以备份所有数据库,并且备份角色,表空间,数据库用户和组以及适用于整个数据库的访问权限。
pg_dumpall缺点:
pg_dumpall需要多次连接到postgresql服务器,每个数据库一次。如果你使用口令认证,可能每次都会要求口令。这种情况下使用一个~/.pgpass会比较方便。

pg_dump恢复实验:

一般情况下使用pg_dump —help语句查看帮助,普通文件可使用psql恢复,二进制文件使用pg_restore恢复。

数据准备:
  
  
  
  1. 数据库:wydb01

  2. 用户名:wy01

  3. 表名:wytb01

实验一:
  
  
  
  1. --备份数据

  2. [postgres@wypg15 backup]$ pg_dump -h 192.168.70.15 -U wy01 wydb01>wybak1.sql

  3. Password:

  4. [postgres@wypg15 backup]$ ll

  5. total 4

  6. -rw-rw-r--. 1 postgres postgres 1398 Jan 4 20:05 wybak1.sql

  7. --删除数据库

  8. [postgres@wypg15 backup]$ psql

  9. psql (15.3)

  10. Type "help" for help.

  11. postgres=# drop database wydb01;

  12. DROP DATABASE

  13. --创建数据库

  14. postgres=# create database wydb01 with owner=wy01;

  15. CREATE DATABASE

  16. --恢复数据库

  17. postgres=# \q

  18. [postgres@wypg15 backup]$ psql -h 192.168.70.15 -U wy01 wydb01<wybak1.sql

  19. Password for user wy01:

  20. SET

  21. SET

  22. SET

  23. SET

  24. SET

  25. set_config

  26. ------------

  27. (1 row)

  28. SET

  29. SET

  30. SET

  31. SET

  32. SET

  33. SET

  34. CREATE TABLE

  35. ALTER TABLE

  36. COPY 3

  37. ALTER TABLE

  38. CREATE PUBLICATION

  39. ALTER PUBLICATION

  40. ALTER PUBLICATION

  41. --查看数据库恢复情况

  42. [postgres@wypg15 backup]$ psql -h 192.168.70.15 -U wy01 wydb01 -c "select * from wytb01;"

  43. Password for user wy01:

  44. id | name

  45. ----+------

  46. 1 | 小李

  47. 2 | 小张

  48. 4 | 老徐

  49. (3 rows)

实验二:
  
  
  
  1. --二进制格式备份文件:-F c,可使用j参数指定同时几个进程同时执行

  2. [postgres@wypg15 backup]$ pg_dump -F c -f wybak2.dmp -C -E UTF8 -h 192.168.70.15 -U wy01 wydb01

  3. Password:

  4. [postgres@wypg15 backup]$ ll

  5. total 8

  6. -rw-rw-r--. 1 postgres postgres 1398 Jan 4 20:05 wybak1.sql

  7. -rw-rw-r--. 1 postgres postgres 2011 Jan 4 20:10 wybak2.dmp

  8. #查看二进制文件中的内容

  9. [postgres@wypg15 backup]$ pg_restore -l wybak2.dmp

  10. ;

  11. ; Archive created at 2024-01-04 20:10:08 CST

  12. ; dbname: wydb01

  13. ; TOC Entries: 9

  14. ; Compression: -1

  15. ; Dump Version: 1.14-0

  16. ; Format: CUSTOM

  17. ; Integer: 4 bytes

  18. ; Offset: 8 bytes

  19. ; Dumped from database version: 15.3

  20. ; Dumped by pg_dump version: 15.3

  21. ;

  22. ;

  23. ; Selected TOC Entries:

  24. ;

  25. 214; 1259 16433 TABLE public wytb01 wy01

  26. 3456; 0 16433 TABLE DATA public wytb01 wy01

  27. 3311; 2606 16437 CONSTRAINT public wytb01 wytb01_pkey wy01

  28. 3454; 6104 16438 PUBLICATION - pub01 wy01

  29. 3455; 6106 16439 PUBLICATION TABLE public pub01 wytb01 wy01

  30. --删除数据库

  31. [postgres@wypg15 backup]$ psql

  32. psql (15.3)

  33. Type "help" for help.

  34. postgres=# drop database wydb01;

  35. DROP DATABASE

  36. --创建数据库

  37. postgres=# create database wydb01 with owner=wy01;

  38. CREATE DATABASE

  39. --恢复数据库

  40. postgres=# \q

  41. [postgres@wypg15 backup]$ pg_restore -h 192.168.70.15 -U wy01 -d wydb01 wybak2.dmp

  42. Password:

  43. --查看数据库恢复情况

  44. [postgres@wypg15 backup]$ psql -h 192.168.70.15 -U wy01 wydb01 -c"select * from wytb01;"

  45. Password for user wy01:

  46. id | name

  47. ----+------

  48. 1 | 小李

  49. 2 | 小张

  50. 4 | 老徐

  51. (3 rows)

pg_dumpall恢复实验:

  
  
  
  1. --修改密码配置文件

  2. cd

  3. vi ~/.pgpass

  4. 192.168.70.15:6543:*:postgres:postgres

  5. chmod 600 ~/.pgpass

  6. --备份数据库集簇

  7. [postgres@wypg15 backup]$ pg_dumpall -h 192.168.70.15 -U postgres -f wybak_all.sql

  8. [postgres@wypg15 backup]$ ll

  9. total 20

  10. -rw-rw-r--. 1 postgres postgres 1398 Jan 4 20:05 wybak1.sql

  11. -rw-rw-r--. 1 postgres postgres 2011 Jan 4 20:10 wybak2.dmp

  12. -rw-rw-r--. 1 postgres postgres 8333 Jan 4 20:19 wybak_all.sql

  13. --删除数据库

  14. [postgres@wypg15 backup]$ psql

  15. psql (15.3)

  16. Type "help" for help.

  17. postgres=# drop database wydb01;

  18. DROP DATABASE

  19. --恢复数据库,不需要创建数据库(已存在对象的创建可忽略报错)

  20. [postgres@wypg15 backup]$ psql -h 192.168.70.15 -U postgres<wybak_all.sql

  21. SET

  22. SET

  23. SET

  24. ERROR: role "postgres" already exists

  25. ALTER ROLE

  26. ERROR: role "repuser" already exists

  27. ALTER ROLE

  28. ERROR: role "wy01" already exists

  29. ALTER ROLE

  30. ERROR: role "wy03" already exists

  31. ALTER ROLE

  32. You are now connected to database "template1" as user "postgres".

  33. SET

  34. SET

  35. SET

  36. SET

  37. SET

  38. set_config

  39. ------------

  40. (1 row)

  41. SET

  42. SET

  43. SET

  44. SET

  45. You are now connected to database "postgres" as user "postgres".

  46. SET

  47. SET

  48. SET

  49. SET

  50. SET

  51. set_config

  52. ------------

  53. (1 row)

  54. SET

  55. SET

  56. SET

  57. SET

  58. SET

  59. SET

  60. ERROR: relation "wytb01" already exists

  61. ALTER TABLE

  62. ERROR: duplicate key value violates unique constraint "wytb01_pkey"

  63. DETAIL: Key (id)=(1) already exists.

  64. CONTEXT: COPY wytb01, line 1

  65. ERROR: multiple primary keys for table "wytb01" are not allowed

  66. GRANT

  67. GRANT

  68. SET

  69. SET

  70. SET

  71. SET

  72. SET

  73. set_config

  74. ------------

  75. (1 row)

  76. SET

  77. SET

  78. SET

  79. SET

  80. CREATE DATABASE

  81. ALTER DATABASE

  82. You are now connected to database "wydb01" as user "postgres".

  83. SET

  84. SET

  85. SET

  86. SET

  87. SET

  88. set_config

  89. ------------

  90. (1 row)

  91. SET

  92. SET

  93. SET

  94. SET

  95. SET

  96. SET

  97. CREATE TABLE

  98. ALTER TABLE

  99. COPY 3

  100. ALTER TABLE

  101. CREATE PUBLICATION

  102. ALTER PUBLICATION

  103. ALTER PUBLICATION

  104. SET

  105. SET

  106. SET

  107. SET

  108. SET

  109. set_config

  110. ------------

  111. (1 row)

  112. SET

  113. SET

  114. SET

  115. SET

  116. ERROR: database "wydb03" already exists

  117. ALTER DATABASE

  118. You are now connected to database "wydb03" as user "postgres".

  119. SET

  120. SET

  121. SET

  122. SET

  123. SET

  124. set_config

  125. ------------

  126. (1 row)

  127. SET

  128. SET

  129. SET

  130. SET

  131. SET

  132. SET

  133. ERROR: relation "wytb03" already exists

  134. ALTER TABLE

  135. ERROR: relation "wytb04" already exists

  136. ALTER TABLE

  137. ERROR: relation "wytb05" already exists

  138. ALTER TABLE

  139. ERROR: relation "wytb06" already exists

  140. ALTER TABLE

  141. ALTER TABLE

  142. ERROR: duplicate key value violates unique constraint "wytb03_pkey"

  143. DETAIL: Key (id)=(1) already exists.

  144. CONTEXT: COPY wytb03, line 1

  145. ERROR: duplicate key value violates unique constraint "wytb04_pkey"

  146. DETAIL: Key (id)=(1) already exists.

  147. CONTEXT: COPY wytb04, line 1

  148. COPY 0

  149. COPY 1

  150. ERROR: multiple primary keys for table "wytb03" are not allowed

  151. ERROR: multiple primary keys for table "wytb04" are not allowed

  152. ERROR: multiple primary keys for table "wytb05" are not allowed

  153. ERROR: publication "pub03" already exists

  154. ALTER PUBLICATION

  155. --查看数据库恢复情况

  156. [postgres@wypg15 backup]$ psql -h 192.168.70.15 -U wy01 wydb01 -c"select * from wytb01;"

  157. id | name

  158. ----+------

  159. 1 | 小李

  160. 2 | 小张

  161. 4 | 老徐

  162. (3 rows)



本文分享自微信公众号 - 开源软件联盟PostgreSQL分会(kaiyuanlianmeng)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部