ReaderMX.
开源软件联盟PostgreSQL分会

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

开开源软件联盟PostgreSQL分会约 6 分钟

软件信息：

操作系统 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)


KEEP THE THREAD GOING

更多来源文章