-
Notifications
You must be signed in to change notification settings - Fork 0
/
index.html
1583 lines (1200 loc) · 116 KB
/
index.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222"><meta name="generator" content="Hexo 7.0.0-rc1">
<link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
<link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
<link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
<link rel="mask-icon" href="/images/logo.svg" color="#222">
<link rel="stylesheet" href="/css/main.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/[email protected]/css/all.min.css" integrity="sha256-DfWjNxDkM94fVBWx1H5BMMp0Zq7luBlV8QRcSES7s+0=" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">
<script class="next-config" data-name="main" type="application/json">{"hostname":"example.com","root":"/","images":"/images","scheme":"Gemini","darkmode":false,"version":"8.11.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":true,"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"},"path":"/search.json","localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false}}</script><script src="/js/config.js"></script>
<meta property="og:type" content="website">
<meta property="og:title" content="singheart's blog">
<meta property="og:url" content="http://example.com/index.html">
<meta property="og:site_name" content="singheart's blog">
<meta property="og:locale" content="zh_CN">
<meta property="article:author" content="singheart">
<meta name="twitter:card" content="summary">
<link rel="canonical" href="http://example.com/">
<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":true,"isPost":false,"lang":"zh-CN","comments":"","permalink":"","path":"index.html","title":""}</script>
<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>singheart's blog</title>
<noscript>
<link rel="stylesheet" href="/css/noscript.css">
</noscript>
</head>
<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
<div class="headband"></div>
<main class="main">
<header class="header" itemscope itemtype="http://schema.org/WPHeader">
<div class="header-inner"><div class="site-brand-container">
<div class="site-nav-toggle">
<div class="toggle" aria-label="切换导航栏" role="button">
<span class="toggle-line"></span>
<span class="toggle-line"></span>
<span class="toggle-line"></span>
</div>
</div>
<div class="site-meta">
<a href="/" class="brand" rel="start">
<i class="logo-line"></i>
<h1 class="site-title">singheart's blog</h1>
<i class="logo-line"></i>
</a>
<p class="site-subtitle" itemprop="description">多读书，多思考</p>
</div>
<div class="site-nav-right">
<div class="toggle popup-trigger">
<i class="fa fa-search fa-fw fa-lg"></i>
</div>
</div>
</div>
<nav class="site-nav">
<ul class="main-menu menu"><li class="menu-item menu-item-home"><a href="/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a></li><li class="menu-item menu-item-tags"><a href="/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a></li><li class="menu-item menu-item-categories"><a href="/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a></li><li class="menu-item menu-item-archives"><a href="/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a></li>
<li class="menu-item menu-item-search">
<a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
</a>
</li>
</ul>
</nav>
<div class="search-pop-overlay">
<div class="popup search-popup"><div class="search-header">
<span class="search-icon">
<i class="fa fa-search"></i>
</span>
<div class="search-input-container">
<input autocomplete="off" autocapitalize="off" maxlength="80"
placeholder="搜索..." spellcheck="false"
type="search" class="search-input">
</div>
<span class="popup-btn-close" role="button">
<i class="fa fa-times-circle"></i>
</span>
</div>
<div class="search-result-container no-result">
<div class="search-result-icon">
<i class="fa fa-spinner fa-pulse fa-5x"></i>
</div>
</div>
</div>
</div>
</div>
<div class="toggle sidebar-toggle" role="button">
<span class="toggle-line"></span>
<span class="toggle-line"></span>
<span class="toggle-line"></span>
</div>
<aside class="sidebar">
<div class="sidebar-inner sidebar-overview-active">
<ul class="sidebar-nav">
<li class="sidebar-nav-toc">
文章目录
</li>
<li class="sidebar-nav-overview">
站点概览
</li>
</ul>
<div class="sidebar-panel-container">
<!--noindex-->
<div class="post-toc-wrap sidebar-panel">
</div>
<!--/noindex-->
<div class="site-overview-wrap sidebar-panel">
<div class="site-author site-overview-item animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
<img class="site-author-image" itemprop="image" alt="singheart"
src="http://img.singhe.art/FhsjiuZl1nWUeg6NRdM279QXbA1-">
<p class="site-author-name" itemprop="name">singheart</p>
<div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap site-overview-item animated">
<nav class="site-state">
<div class="site-state-item site-state-posts">
<a href="/archives/">
<span class="site-state-item-count">71</span>
<span class="site-state-item-name">日志</span>
</a>
</div>
<div class="site-state-item site-state-categories">
<a href="/categories/">
<span class="site-state-item-count">8</span>
<span class="site-state-item-name">分类</span></a>
</div>
<div class="site-state-item site-state-tags">
<a href="/tags/">
<span class="site-state-item-count">33</span>
<span class="site-state-item-name">标签</span></a>
</div>
</nav>
</div>
<div class="links-of-author site-overview-item animated">
<span class="links-of-author-item">
<a href="https://github.com/xinjiempolde" title="GitHub → https://github.com/xinjiempolde" rel="noopener" target="_blank"><i class="fab fa-github fa-fw"></i>GitHub</a>
</span>
</div>
</div>
</div>
</div>
</aside>
<div class="sidebar-dimmer"></div>
</header>
<div class="back-to-top" role="button" aria-label="返回顶部">
<i class="fa fa-arrow-up"></i>
<span>0%</span>
</div>
<noscript>
<div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>
<div class="main-inner index posts-expand">
<div class="post-block">
<article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
<link itemprop="mainEntityOfPage" href="http://example.com/2024/07/28/openGauss/Postgresql%E4%BD%93%E7%B3%BB%E6%9E%B6%E6%9E%84/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="http://img.singhe.art/FhsjiuZl1nWUeg6NRdM279QXbA1-">
<meta itemprop="name" content="singheart">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="singheart's blog">
<meta itemprop="description" content="">
</span>
<span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
<meta itemprop="name" content="undefined | singheart's blog">
<meta itemprop="description" content="">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2024/07/28/openGauss/Postgresql%E4%BD%93%E7%B3%BB%E6%9E%B6%E6%9E%84/" class="post-title-link" itemprop="url">未命名</a>
</h2>
<div class="post-meta-container">
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2024-07-28 20:49:21" itemprop="dateCreated datePublished" datetime="2024-07-28T20:49:21+08:00">2024-07-28</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar-check"></i>
</span>
<span class="post-meta-item-text">更新于</span>
<time title="修改时间：2024-03-08 13:18:20" itemprop="dateModified" datetime="2024-03-08T13:18:20+08:00">2024-03-08</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-folder"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope itemtype="http://schema.org/Thing">
<a href="/categories/openGauss/" itemprop="url" rel="index"><span itemprop="name">openGauss</span></a>
</span>
</span>
</div>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<figure>
<img
src="https://pic2.zhimg.com/80/v2-3da61b8c04b032b306294439bece99e9_1440w.webp"
alt="img" />
<figcaption aria-hidden="true">img</figcaption>
</figure>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
</div>
<div class="post-block">
<article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
<link itemprop="mainEntityOfPage" href="http://example.com/2024/07/28/openGauss/ubuntu%E5%AE%89%E8%A3%85postgresql9.2/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="http://img.singhe.art/FhsjiuZl1nWUeg6NRdM279QXbA1-">
<meta itemprop="name" content="singheart">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="singheart's blog">
<meta itemprop="description" content="">
</span>
<span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
<meta itemprop="name" content="undefined | singheart's blog">
<meta itemprop="description" content="">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2024/07/28/openGauss/ubuntu%E5%AE%89%E8%A3%85postgresql9.2/" class="post-title-link" itemprop="url">未命名</a>
</h2>
<div class="post-meta-container">
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2024-07-28 20:49:21" itemprop="dateCreated datePublished" datetime="2024-07-28T20:49:21+08:00">2024-07-28</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar-check"></i>
</span>
<span class="post-meta-item-text">更新于</span>
<time title="修改时间：2024-03-14 21:47:43" itemprop="dateModified" datetime="2024-03-14T21:47:43+08:00">2024-03-14</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-folder"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope itemtype="http://schema.org/Thing">
<a href="/categories/openGauss/" itemprop="url" rel="index"><span itemprop="name">openGauss</span></a>
</span>
</span>
</div>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<h1
id="一安装并配置并设置远程登陆的用户名和密码">一、安装并配置，并设置远程登陆的用户名和密码</h1>
<p>1、安装postgreSQL</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"</span><br><span class="line">wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -</span><br><span class="line"></span><br><span class="line">sudo apt-get update</span><br><span class="line"></span><br><span class="line">sudo apt-get install postgresql-9.6</span><br></pre></td></tr></table></figure>
<ul>
<li>在Ubuntu下安装Postgresql后，会自动注册为服务，并随操作系统自动启动。</li>
<li>在Ubuntu下安装Postgresql后，会自动添加一个名为postgres的操作系统用户，密码是随机的。并且会自动生成一个名字为postgres的数据库，用户名也为postgres，密码也是随机的。</li>
</ul>
<p>2、修改postgres数据库用户的密码为manage</p>
<p>打开客户端工具（psql）</p>
<p>sudo -u postgres psql</p>
<ul>
<li>其中，sudo -u postgres 是使用postgres 用户登录的意思</li>
<li>PostgreSQL数据默认会创建一个postgres的数据库用户作为数据库的管理员，密码是随机的</li>
</ul>
<p><strong><em>*postgres=#*</em> ALTER USER postgres WITH PASSWORD
'manage';</strong></p>
<ul>
<li>postgres=#为PostgreSQL下的命令提示符，--注意最后的分号；</li>
</ul>
<p>3、退出PostgreSQL psql客户端</p>
<p>**<em>*postgres=#*</em> *</p>
<p>4、修改<a
target="_blank" rel="noopener" href="https://so.csdn.net/so/search?q=ubuntu&spm=1001.2101.3001.7020">ubuntu</a>操作系统的postgres用户的密码（密码要与数据库用户postgres的密码相同）</p>
<p>切换到root用户</p>
<p>su root</p>
<p>删除PostgreSQL用户密码</p>
<p><strong>sudo passwd -d postgres</strong></p>
<ul>
<li>passwd -d 是清空指定用户密码的意思</li>
</ul>
<p>设置PostgreSQL系统用户的密码</p>
<p><strong>sudo -u postgres passwd</strong></p>
<p>按照提示，输入两次新密码</p>
<ul>
<li>输入新的 UNIX 密码</li>
<li>重新输入新的 UNIX 密码</li>
<li>passwd：已成功更新密码</li>
</ul>
<p>5、修改PostgresSQL数据库配置实现远程访问</p>
<p><strong>vi /etc/postgresql/9.6/main/postgresql.conf</strong></p>
<p>1.监听任何地址访问，修改连接权限</p>
<p><strong>#listen_addresses = 'localhost' 改为 listen_addresses =
'*'</strong></p>
<p>2.启用密码验证</p>
<p><strong>#password_encryption = on 改为 password_encryption =
on</strong></p>
<p><strong>vi /etc/postgresql/9.6/main/pg_hba.conf</strong></p>
<p>在文档末尾加上以下内容</p>
<p><strong>host all all 0.0.0.0 0.0.0.0 md5</strong></p>
<p>6、重启服务</p>
<p>/etc/init.d/postgresql restart</p>
<p>7、5432端口的防火墙设置</p>
<p>5432为postgreSQL默认的端口</p>
<p><strong>iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport
5432 -j ACCEPT</strong></p>
<h1
id="二内部登录管理数据库新建数据库用户和密码">二、内部登录，管理数据库、新建数据库、用户和密码</h1>
<p>1、登录postgre SQL数据库</p>
<p><strong>psql -U postgres -h 127.0.0.1</strong></p>
<p>2、创建新用户zhangps，但不给建数据库的权限</p>
<p><strong><em>*postgres=#*</em> create user "zhangps" with password
'123456' nocreatedb;</strong></p>
<ul>
<li>用户名处是双引号</li>
</ul>
<p>3、建立数据库，并指定所有者</p>
<p>**<em>*</em>*postgres=#****create database "testdb" with owner =
"zhangps";**</p>
<h1
id="三外部登录管理数据库新建数据库用户和密码">三、外部登录，管理数据库、新建数据库、用户和密码</h1>
<p>1、在外部命令行的管理命令，创建用户pencil</p>
<p><strong>sudo -u postgres createuser -D -P pencil</strong></p>
<ul>
<li>输入新的密码:</li>
<li>再次输入新的密码:</li>
</ul>
<p>2、建立数据库(tempdb)，并指定所有者为（pencil）</p>
<p><strong>sudo -u postgres createdb -O pencil tempdb</strong></p>
<ul>
<li>-O设定所有者为pencil</li>
</ul>
<p>postgres的 日志目录，</p>
<p>/var/lib/postgresql/9.6/main</p>
<p>如果不修改日志目录，则应该在</p>
<p>/var/log/postgresql中</p>
<p>在目录<strong>/etc/postgresql/9.6/main/postgresql.conf</strong></p>
<p><strong>可以修改日志，重新定向目录为*<em>/var/lib/postgresql/9.6/main*</em></strong></p>
<p>**<em>*</em>*log_destination = 'stderr'** **logging_collector = on**
**log_directory = 'pg_log'** **log_filename =
'postgresql-%Y-%m-%d_%H%M%S.log'** **log_rotation_age = 1d**
**log_rotation_size = 100MB** **log_min_messages = info******</p>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
</div>
<div class="post-block">
<article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
<link itemprop="mainEntityOfPage" href="http://example.com/2024/07/28/openGauss/postgresql%E4%B8%ADMVCC%E8%A7%A3%E6%9E%90/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="http://img.singhe.art/FhsjiuZl1nWUeg6NRdM279QXbA1-">
<meta itemprop="name" content="singheart">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="singheart's blog">
<meta itemprop="description" content="">
</span>
<span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
<meta itemprop="name" content="undefined | singheart's blog">
<meta itemprop="description" content="">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2024/07/28/openGauss/postgresql%E4%B8%ADMVCC%E8%A7%A3%E6%9E%90/" class="post-title-link" itemprop="url">未命名</a>
</h2>
<div class="post-meta-container">
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2024-07-28 20:49:21 / 修改时间：23:30:57" itemprop="dateCreated datePublished" datetime="2024-07-28T20:49:21+08:00">2024-07-28</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-folder"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope itemtype="http://schema.org/Thing">
<a href="/categories/openGauss/" itemprop="url" rel="index"><span itemprop="name">openGauss</span></a>
</span>
</span>
</div>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<blockquote>
<p>参考： - <a href="jasongj.com">MVCC PostgreSQL 事务模型
多版本并发控制</a> - <a
target="_blank" rel="noopener" href="https://blog.csdn.net/qq_31156277/article/details/90551978">PostgreSQL如何实现MVCC
(基于xmin、xmax、cmin、cmax)_postgresql cmin xmin-CSDN博客</a> # 0x01
PostgreSQL中MVCC原理</p>
</blockquote>
<p>PostgreSQL中，对于每一行数据（称为一个tuple），包含有4个隐藏字段。这四个字段是隐藏的，但可直接访问。</p>
<ul>
<li>xmin
在创建（insert）记录（tuple）时，记录此值为插入tuple的事务ID</li>
<li>xmax 默认值为0.在删除tuple时，记录此值</li>
<li>cmin和cmax
标识在同一个事务中多个语句命令的序列值，从0开始，用于同一个事务中实现版本可见性判断</li>
</ul>
<p>下面通过实验具体看看这些标记如何工作。在此之前，先创建测试表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">CREATE</span> <span class="keyword">TABLE</span> test </span><br><span class="line">(</span><br><span class="line"> id <span class="type">INTEGER</span>,</span><br><span class="line"> <span class="keyword">value</span> TEXT</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
<p>开启一个事务，查询当前事务ID（值为3277），并插入一条数据，xmin为3277，与当前事务ID相等。符合上文所述——插入tuple时记录xmin，记录未被删除时xmax为0</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">postgres=> BEGIN;</span><br><span class="line">BEGIN</span><br><span class="line">postgres=> SELECT TXID_CURRENT();</span><br><span class="line"> txid_current </span><br><span class="line">--------------</span><br><span class="line"> 3277</span><br><span class="line">(1 row)</span><br><span class="line"></span><br><span class="line">postgres=> INSERT INTO test VALUES(1, 'a');</span><br><span class="line">INSERT 0 1</span><br><span class="line">postgres=> SELECT *, xmin, xmax, cmin, cmax FROM test;</span><br><span class="line"> id | value | xmin | xmax | cmin | cmax </span><br><span class="line">----+-------+------+------+------+------</span><br><span class="line"> 1 | a | 3277 | 0 | 0 | 0</span><br><span class="line">(1 row)</span><br></pre></td></tr></table></figure>
<p>继续通过一条语句插入2条记录，xmin仍然为当前事务ID，即3277，xmax仍然为0，同时cmin和cmax为1，符合上文所述cmin/cmax在事务内随着所执行的语句递增。虽然此步骤插入了两条数据，但因为是在同一条语句中插入，故其cmin/cmax都为1，在上一条语句的基础上加一。</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">INSERT INTO test VALUES(2, 'b'), (3, 'c');</span><br><span class="line">INSERT 0 2</span><br><span class="line">postgres=> SELECT *, xmin, xmax, cmin, cmax FROM test;</span><br><span class="line"> id | value | xmin | xmax | cmin | cmax </span><br><span class="line">----+-------+------+------+------+------</span><br><span class="line"> 1 | a | 3277 | 0 | 0 | 0</span><br><span class="line"> 2 | b | 3277 | 0 | 1 | 1</span><br><span class="line"> 3 | c | 3277 | 0 | 1 | 1</span><br><span class="line">(3 rows)</span><br></pre></td></tr></table></figure>
<p>将id为1的记录的value字段更新为’d’，其xmin和xmax均未变，而cmin和cmax变为2，在上一条语句的基础之上增加一。此时提交事务。</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">UPDATE test SET value = 'd' WHERE id = 1;</span><br><span class="line">UPDATE 1</span><br><span class="line">postgres=> SELECT *, xmin, xmax, cmin, cmax FROM test;</span><br><span class="line"> id | value | xmin | xmax | cmin | cmax </span><br><span class="line">----+-------+------+------+------+------</span><br><span class="line"> 2 | b | 3277 | 0 | 1 | 1</span><br><span class="line"> 3 | c | 3277 | 0 | 1 | 1</span><br><span class="line"> 1 | d | 3277 | 0 | 2 | 2</span><br><span class="line">(3 rows)</span><br><span class="line"></span><br><span class="line">postgres=> COMMIT;</span><br><span class="line">COMMIT</span><br></pre></td></tr></table></figure>
<p>开启一个新事务，通过2条语句分别插入2条id为4和5的tuple。</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">BEGIN;</span><br><span class="line">BEGIN</span><br><span class="line">postgres=> INSERT INTO test VALUES (4, 'x');</span><br><span class="line">INSERT 0 1</span><br><span class="line">postgres=> INSERT INTO test VALUES (5, 'y'); </span><br><span class="line">INSERT 0 1</span><br><span class="line">postgres=> SELECT *, xmin, xmax, cmin, cmax FROM test;</span><br><span class="line"> id | value | xmin | xmax | cmin | cmax </span><br><span class="line">----+-------+------+------+------+------</span><br><span class="line"> 2 | b | 3277 | 0 | 1 | 1</span><br><span class="line"> 3 | c | 3277 | 0 | 1 | 1</span><br><span class="line"> 1 | d | 3277 | 0 | 2 | 2</span><br><span class="line"> 4 | x | 3278 | 0 | 0 | 0</span><br><span class="line"> 5 | y | 3278 | 0 | 1 | 1</span><br><span class="line">(5 rows)</span><br></pre></td></tr></table></figure>
<p>此时，将id为2的tuple的value更新为’e’，其对应的cmin/cmax被设置为2，且其xmin被设置为当前事务ID，即3278</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">UPDATE test SET value = 'e' WHERE id = 2;</span><br><span class="line">UPDATE 1</span><br><span class="line">postgres=> SELECT *, xmin, xmax, cmin, cmax FROM test;</span><br><span class="line"> id | value | xmin | xmax | cmin | cmax </span><br><span class="line">----+-------+------+------+------+------</span><br><span class="line"> 3 | c | 3277 | 0 | 1 | 1</span><br><span class="line"> 1 | d | 3277 | 0 | 2 | 2</span><br><span class="line"> 4 | x | 3278 | 0 | 0 | 0</span><br><span class="line"> 5 | y | 3278 | 0 | 1 | 1</span><br><span class="line"> 2 | e | 3278 | 0 | 2 | 2</span><br></pre></td></tr></table></figure>
<p>在另外一个窗口中开启一个事务，可以发现id为2的tuple，xin仍然为3277，但其xmax被设置为3278，而cmin和cmax均为2。符合上文所述——若tuple被删除，则xmax被设置为删除tuple的事务的ID。</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">BEGIN;</span><br><span class="line">BEGIN</span><br><span class="line">postgres=> SELECT *, xmin, xmax, cmin, cmax FROM test;</span><br><span class="line"> id | value | xmin | xmax | cmin | cmax </span><br><span class="line">----+-------+------+------+------+------</span><br><span class="line"> 2 | b | 3277 | 3278 | 2 | 2</span><br><span class="line"> 3 | c | 3277 | 0 | 1 | 1</span><br><span class="line"> 1 | d | 3277 | 0 | 2 | 2</span><br><span class="line">(3 rows)</span><br></pre></td></tr></table></figure>
<p>这里有几点要注意</p>
<ul>
<li>新旧窗口中id为2的tuple对应的value和xmin、xmax、cmin/cmax均不相同，实际上它们是该tuple的2个不同版本</li>
<li>在旧窗口中，更新之前，数据的顺序是2，3，1，4，5，更新后变为3，1，4，5，2。因为在PostgreSQL中更新实际上是将旧tuple标记为删除，并插入更新后的新数据，所以更新后id为2的tuple从原来最前面变成了最后面</li>
<li>在新窗口中，id为2的tuple仍然如旧窗口中更新之前一样，排在最前面。这是因为旧窗口中的事务未提交，更新对新窗口不可见，新窗口看到的仍然是旧版本的数据</li>
</ul>
<p>提交旧窗口中的事务后，新旧窗口中看到数据完全一致——id为2的tuple排在了最后，xmin变为3278，xmax为0，cmin/cmax为2。前文定义中，xmin是tuple创建时的事务ID，并没有提及更新的事务ID，但因为PostgreSQL的更新操作并非真正更新数据，而是将旧数据标记为删除，并插入新数据，所以“更新的事务ID”也就是“创建记录的事务ID”。</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"> SELECT *, xmin, xmax, cmin, cmax FROM test;</span><br><span class="line"> id | value | xmin | xmax | cmin | cmax </span><br><span class="line">----+-------+------+------+------+------</span><br><span class="line"> 3 | c | 3277 | 0 | 1 | 1</span><br><span class="line"> 1 | d | 3277 | 0 | 2 | 2</span><br><span class="line"> 4 | x | 3278 | 0 | 0 | 0</span><br><span class="line"> 5 | y | 3278 | 0 | 1 | 1</span><br><span class="line"> 2 | e | 3278 | 0 | 2 | 2</span><br><span class="line">(5 rows)</span><br></pre></td></tr></table></figure>
<h2 id="mvcc保证原子性">MVCC保证原子性</h2>
<p>原子性（Atomicity）指得是一个事务是一个不可分割的工作单位，事务中包括的所有操作要么都做，要么都不做。</p>
<p>对于插入操作，PostgreSQL会将当前事务ID存于xmin中。对于删除操作，其事务ID会存于xmax中。对于更新操作，PostgreSQL会将当前事务ID存于旧数据的xmax中，并存于新数据的xin中。换句话说，事务对增、删和改所操作的数据上都留有其事务ID，可以很方便的提交该批操作或者完全撤销操作，从而实现了事务的原子性。</p>
<h2 id="mvcc保证事物的隔离性">MVCC保证事物的隔离性</h2>
<p>隔离性（Isolation）指一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。</p>
<p>标准SQL的事务隔离级别分为如下四个级别</p>
<table>
<thead>
<tr class="header">
<th style="text-align: left;">隔离级别</th>
<th style="text-align: left;">脏读</th>
<th style="text-align: left;">不可重复读</th>
<th style="text-align: left;">幻读</th>
</tr>
</thead>
<tbody>
<tr class="odd">
<td style="text-align: left;">未提交读（read uncommitted）</td>
<td style="text-align: left;">可能</td>
<td style="text-align: left;">可能</td>
<td style="text-align: left;">可能</td>
</tr>
<tr class="even">
<td style="text-align: left;">提交读（read committed）</td>
<td style="text-align: left;">不可能</td>
<td style="text-align: left;">可能</td>
<td style="text-align: left;">可能</td>
</tr>
<tr class="odd">
<td style="text-align: left;">可重复读（repeatable read）</td>
<td style="text-align: left;">不可能</td>
<td style="text-align: left;">不可能</td>
<td style="text-align: left;">可能</td>
</tr>
<tr class="even">
<td style="text-align: left;">串行读（serializable）</td>
<td style="text-align: left;">不可能</td>
<td style="text-align: left;">不可能</td>
<td style="text-align: left;">不可能</td>
</tr>
</tbody>
</table>
<p>从上表中可以看出，从未提交读到串行读，要求越来越严格。</p>
<p>注意，SQL标准规定，具体数据库实现时，对于标准规定不允许发生的，绝不可发生；对于可能发生的，并不要求一定能发生。换句话说，具体数据库实现时，对应的隔离级别只可更严格，不可更宽松。</p>
<p>事实中，PostgreSQL实现了三种隔离级别——未提交读和提交读实际上都被实现为提交读。</p>
<p>下面将讨论提交读和可重复读的实现方式</p>
<h2 id="mvcc隔离级别探索">MVCC隔离级别探索</h2>
<p>无论提交成功或回滚的事务，xid 都会递增，对于repeatable read 和
serializable 隔离级别的事务，如果它的xid 小于另外一个事务的xid
。也就是xmin小于另外一个事务的xmin，那么另外一个事务对这个事务是不可见的。而read
committed 则不会。</p>
<p><strong>设置一个读已提交隔离级别</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">technology=# begin transaction isolation level read committed;</span><br><span class="line">BEGIN</span><br><span class="line">technology=# SELECT ival FROM tb_mvcc WHERE id = 1;</span><br><span class="line"> id | ival </span><br><span class="line">----+------</span><br><span class="line"> 1 | 1</span><br><span class="line">(1 row)</span><br></pre></td></tr></table></figure>
<p><strong>另外一个事务对id=1 的进行修改，并commit</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">technology=# BEGIN;</span><br><span class="line">BEGIN</span><br><span class="line">technology=# update tb_mvcc set ival = 11 where id = 1;</span><br><span class="line">UPDATE 1</span><br><span class="line">technology=# commit;</span><br></pre></td></tr></table></figure>
<p><strong>在从第一个事务进行读取时，发现数据已经被修改，即在同一个事务中两次读取结果不一致。发现ival
被修改成了11</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">technology=# SELECT id,ival FROM tb_mvcc WHERE id = 1;</span><br><span class="line">id | ival </span><br><span class="line">----+------</span><br><span class="line"> 1 | 11</span><br><span class="line">(1 row)</span><br></pre></td></tr></table></figure>
<p><strong>完整信息如下所示：</strong></p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">technology=# begin transaction isolation level read committed;</span><br><span class="line">BEGIN</span><br><span class="line">technology=# SELECT xmin,xmax,cmin,xmax,id,ival FROM tb_mvcc WHERE id = 1;</span><br><span class="line"> xmin | xmax | cmin | xmax | id | ival </span><br><span class="line">------+------+------+------+----+------</span><br><span class="line"> 630 | 0 | 0 | 0 | 1 | 1</span><br><span class="line">(1 row)</span><br><span class="line"></span><br><span class="line">technology=# SELECT xmin,xmax,cmin,xmax,id,ival FROM tb_mvcc WHERE id = 1;</span><br><span class="line"> xmin | xmax | cmin | xmax | id | ival </span><br><span class="line">------+------+------+------+----+------</span><br><span class="line"> 635 | 0 | 0 | 0 | 1 | 11</span><br><span class="line">(1 row)</span><br><span class="line"></span><br><span class="line">technology=# END;</span><br><span class="line">COMMIT</span><br><span class="line"></span><br></pre></td></tr></table></figure>
<h3 id="mvcc提交读">MVCC提交读</h3>
<p>提交读只可读取其它已提交事务的结果。PostgreSQL中通过pg_clog来记录哪些事务已经被提交，哪些未被提交。具体实现方式将在下一篇文章《SQL优化（七）
WAL PostgreSQL实现事务和高并发的重要技术》中讲述。</p>
<h3 id="mvcc可重复读">MVCC可重复读</h3>
<p>相对于提交读，重复读要求在同一事务中，前后两次带条件查询所得到的结果集相同。实际中，PostgreSQL的实现更严格，不紧要求可重复读，还不允许出现幻读。它是通过只读取在当前事务开启之前已经提交的数据实现的。结合上文的四个隐藏系统字段来讲，PostgreSQL的可重复读是通过只读取xmin小于当前事务ID且已提交的事务的结果来实现的。</p>
<h1 id="x02-postgresql中的mvcc优势">0x02 PostgreSQL中的MVCC优势</h1>
<ul>
<li>使用MVCC，读操作不会阻塞写，写操作也不会阻塞读，提高了并发访问下的性能</li>
<li>事务的回滚可立即完成，无论事务进行了多少操作</li>
<li>数据可以进行大量更新，不像MySQL和Innodb引擎和Oracle那样需要保证回滚段不会被耗尽</li>
</ul>
<h1 id="x03-postgresql中的mvcc缺点">0x03 PostgreSQL中的MVCC缺点</h1>
<h2 id="事务id个数有限制">事务ID个数有限制</h2>
<p>事务ID由32位数保存，而事务ID递增，当事务ID用完时，会出现wraparound问题。</p>
<p>PostgreSQL通过VACUUM机制来解决该问题。对于事务ID，PostgreSQL有三个事务ID有特殊意义：</p>
<ul>
<li>0代表invalid事务号</li>
<li>1代表bootstrap事务号</li>
<li>2代表frozon事务。frozon transaction id比任何事务都要老</li>
</ul>
<p>可用的有效最小事务ID为3。VACUUM时将所有已提交的事务ID均设置为2，即frozon。之后所有的事务都比frozon事务新，因此VACUUM之前的所有已提交的数据都对之后的事务可见。PostgreSQL通过这种方式实现了事务ID的循环利用。</p>
<h2
id="大量过期数据占用磁盘并降低查询性能">大量过期数据占用磁盘并降低查询性能</h2>
<p>由于上文提到的，PostgreSQL更新数据并非真正更改记录值，而是通过将旧数据标记为删除，再插入新的数据来实现。对于更新或删除频繁的表，会累积大量过期数据，占用大量磁盘，并且由于需要扫描更多数据，使得查询性能降低。</p>
<p>PostgreSQL解决该问题的方式也是VACUUM机制。从释放磁盘的角度，VACUUM分为两种</p>
<ul>
<li>VACUUM
该操作并不要求获得排它锁，因此它可以和其它的读写表操作并行进行。同时它只是简单的将dead
tuple对应的磁盘空间标记为可用状态，新的数据可以重用这部分磁盘空间。但是这部分磁盘并不会被真正释放，也即不会被交还给操作系统，因此不能被系统中其它程序所使用，并且可能会产生磁盘碎片。</li>
<li>VACUUM FULL
需要获得排它锁，它通过“标记-复制”的方式将所有有效数据（非dead
tuple）复制到新的磁盘文件中，并将原数据文件全部删除，并将未使用的磁盘空间还给操作系统，因此系统中其它进程可使用该空间，并且不会因此产生磁盘碎片。</li>
</ul>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
</div>
<div class="post-block">
<article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
<link itemprop="mainEntityOfPage" href="http://example.com/2024/07/28/openGauss/postgresql%E6%95%B0%E6%8D%AE%E5%BA%93%E6%8B%93%E5%B1%95pageinspect/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="http://img.singhe.art/FhsjiuZl1nWUeg6NRdM279QXbA1-">
<meta itemprop="name" content="singheart">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="singheart's blog">
<meta itemprop="description" content="">
</span>
<span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
<meta itemprop="name" content="undefined | singheart's blog">
<meta itemprop="description" content="">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2024/07/28/openGauss/postgresql%E6%95%B0%E6%8D%AE%E5%BA%93%E6%8B%93%E5%B1%95pageinspect/" class="post-title-link" itemprop="url">未命名</a>
</h2>
<div class="post-meta-container">
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2024-07-28 20:49:21" itemprop="dateCreated datePublished" datetime="2024-07-28T20:49:21+08:00">2024-07-28</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar-check"></i>
</span>
<span class="post-meta-item-text">更新于</span>
<time title="修改时间：2024-03-07 21:42:26" itemprop="dateModified" datetime="2024-03-07T21:42:26+08:00">2024-03-07</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-folder"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope itemtype="http://schema.org/Thing">
<a href="/categories/openGauss/" itemprop="url" rel="index"><span itemprop="name">openGauss</span></a>
</span>
</span>
</div>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<blockquote>
<p>转载自</p>
<ul>
<li><a
target="_blank" rel="noopener" href="https://blog.csdn.net/asmartkiller/article/details/118686612">postgresql数据库扩展——pageinspect_pgpageinspect-CSDN博客</a></li>
</ul>
</blockquote>
<p>如果使用MYSQL
相对页面的层次进行一些了解，估计你就的找大佬们的工具集合，并且为此膜拜大佬们，但PG并不需要这样，PG自身自带的pageinspect
工具，就可以让你对页面级别的层次来进行一个 “透心凉”
的查看和分析，并不在为此苦恼。</p>
<p>首先确认您是否拥有了 pageinspect 这个 extension
，下图通过查看pg_extension这个表您可以确认，当前您的PG上已经安装了这个extension.</p>
<figure>
<img src="https://img-blog.csdnimg.cn/20210712235629691.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>如果没有请 create extension pageinspect;
执行这条预计在您当前的数据块中，如果还不行，请您确认您的PG
安装与编译是否正常。 select * from
heap_page_items(get_raw_page(‘test’,0)) order by lp_off desc;</p>
<figure>
<img src="https://img-blog.csdnimg.cn/2021071223570099.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>通过上面的的图，是可以推理出数据存储是从页尾开始的，数据的插入顺序与步进之间的关系。</p>
<figure>
<img
src="https://img-blog.csdnimg.cn/20210712235731255.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg==,size_16,color_FFFFFF,t_70"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>SELECT * from page_header(get_raw_page(‘test’, 0));</p>
<figure>
<img src="https://img-blog.csdnimg.cn/20210712235753934.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>lower = 72 , 通过这里可以获知当前PG的表TEST
中曾经有过多少tumple(在这一刻)，PG的每页有28bytes
的页头，同时每个指针是4bytes ，(72 - 28)/4 = 11 ,证明当前的指针有11个。
我们插入一条记录 insert into test select generate_series(1,1),
random()<em>100, random()</em>1, now();</p>
<figure>
<img
src="https://img-blog.csdnimg.cn/20210712235824374.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg==,size_16,color_FFFFFF,t_70"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>从上图可以看出，指针并未有变化，并通过查看数据和页面的情况，看到新插入的记录，使用了之前空出的
ctid (0,1) 位置，所以指针并不需要在重新分配。
我们继续在插入两条记录，可以看出指针分配了4个字节，并且新的记录也插入了未分配的空间，每行的偏移量是64bytes</p>
<figure>
<img src="https://img-blog.csdnimg.cn/20210712235851211.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>我们删除 ID > 5 的记录</p>
<figure>
<img src="https://img-blog.csdnimg.cn/20210712235911727.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>然后 vacuum test表</p>
<figure>
<img
src="https://img-blog.csdnimg.cn/20210712235936476.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg==,size_16,color_FFFFFF,t_70"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>通过命令我们也可以看到 vacuum
后的空间回收了，并且页头也重新标记了次页面的容量，但指针是不在回收了。</p>
<figure>
<img
src="https://img-blog.csdnimg.cn/2021071300000387.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg==,size_16,color_FFFFFF,t_70"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>通过上面几个简单的命令就可以，理解一些枯燥乏味的PG
某些原理，也是不错的体验。
如果还不理解上面的意思可以看下面这个图（由于信息量太大，所以只能截断成两个图）</p>
<figure>
<img src="https://img-blog.csdnimg.cn/20210713000028764.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<figure>
<img src="https://img-blog.csdnimg.cn/20210713000042613.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>这两张图拼在一起，呈现的就是一个完整的页面上面28个字节头，+ 每个指针
下面就是你存储的每行数据，所以在此证明了页面存储的方式和逻辑中间的0
都是未占用的空间。</p>
<p>我想到此也就没有什么人不在不理解
PG的页面了，试问还有那个数据库在不通过第三方的插件或软件的情况下，能如此通透的展现一个页面在你面前。</p>
<p>SELECT get_raw_page::text FROM get_raw_page(‘test’, 0);</p>
<p>相关的页面获得的源代码，将页面的内容memcpy到buffer
然后给大家展现出来。</p>
<figure>
<img
src="https://img-blog.csdnimg.cn/20210713000110851.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzbWFydGtpbGxlcg==,size_16,color_FFFFFF,t_70"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>那如果有人问，你的数据到底占用了多少个页面，我看看看怎么来通过某种方式来回答他。
1 一个页面我有多少数据 2 一共有多少行数据 2 /1 约等于 多少页面
我们看看上面的算法是不是可以应用到PG 中</p>
<figure>
<img src="https://img-blog.csdnimg.cn/20210713000139797.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
<p>从结果看，还是比较准确的。</p>
<figure>
<img src="https://img-blog.csdnimg.cn/20210713000206193.png"
alt="在这里插入图片描述" />
<figcaption aria-hidden="true">在这里插入图片描述</figcaption>
</figure>
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
</div>
<div class="post-block">
<article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
<link itemprop="mainEntityOfPage" href="http://example.com/2024/07/28/openGauss/openGauss_docker/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="http://img.singhe.art/FhsjiuZl1nWUeg6NRdM279QXbA1-">
<meta itemprop="name" content="singheart">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="singheart's blog">
<meta itemprop="description" content="">
</span>
<span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
<meta itemprop="name" content="undefined | singheart's blog">
<meta itemprop="description" content="">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">
<a href="/2024/07/28/openGauss/openGauss_docker/" class="post-title-link" itemprop="url">openGauss通过docker运行</a>
</h2>
<div class="post-meta-container">
<div class="post-meta">
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-calendar"></i>
</span>
<span class="post-meta-item-text">发表于</span>
<time title="创建时间：2024-07-28 20:47:24 / 修改时间：21:55:38" itemprop="dateCreated datePublished" datetime="2024-07-28T20:47:24+08:00">2024-07-28</time>
</span>
<span class="post-meta-item">
<span class="post-meta-item-icon">
<i class="far fa-folder"></i>
</span>
<span class="post-meta-item-text">分类于</span>
<span itemprop="about" itemscope itemtype="http://schema.org/Thing">
<a href="/categories/openGauss/" itemprop="url" rel="index"><span itemprop="name">openGauss</span></a>
</span>
</span>
</div>
</div>
</header>
<div class="post-body" itemprop="articleBody">
<blockquote>
<p>参考<a
target="_blank" rel="noopener" href="https://blog.csdn.net/dive668/article/details/117268140">【openGauss】Ubuntu
三条命令装好 opengauss_ubuntu安装opengauss-CSDN博客</a></p>
</blockquote>
<h1 id="x00-安装docker">0x00 安装docker</h1>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">curl -fsSL https://get.docker.com | bash -s docker --mirror Aliyun</span><br></pre></td></tr></table></figure>
<p>如果官方docker源较慢，可以修改docker源。编辑<code>/etc/docker/daemon.json</code>，添加以下内容：</p>
<figure class="highlight json"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="punctuation">{</span></span><br><span class="line"> <span class="attr">"registry-mirrors"</span><span class="punctuation">:</span> <span class="punctuation">[</span><span class="string">"https://docker.mirrors.ustc.edu.cn"</span><span class="punctuation">]</span></span><br><span class="line"><span class="punctuation">}</span></span><br></pre></td></tr></table></figure>
<!--noindex-->
<div class="post-button">
<a class="btn" href="/2024/07/28/openGauss/openGauss_docker/#more" rel="contents">
阅读全文 »
</a>
</div>
<!--/noindex-->
</div>
<footer class="post-footer">
<div class="post-eof"></div>
</footer>
</article>
</div>
<div class="post-block">
<article itemscope itemtype="http://schema.org/Article" class="post-content" lang="">
<link itemprop="mainEntityOfPage" href="http://example.com/2024/07/28/rust/rust%E4%B8%ADArc%E5%92%8CMutex/">
<span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
<meta itemprop="image" content="http://img.singhe.art/FhsjiuZl1nWUeg6NRdM279QXbA1-">
<meta itemprop="name" content="singheart">
</span>
<span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
<meta itemprop="name" content="singheart's blog">
<meta itemprop="description" content="">
</span>
<span hidden itemprop="post" itemscope itemtype="http://schema.org/CreativeWork">
<meta itemprop="name" content="undefined | singheart's blog">
<meta itemprop="description" content="">
</span>
<header class="post-header">
<h2 class="post-title" itemprop="name headline">