老男孩教育-第7期-mysql高級(jí)專業(yè)dba vip課堂講義第二十一課postgresql

1、PostgreSQL老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL介紹 PostgreSQL是以加州伯克利分校計(jì)算機(jī)系開發(fā)的 POSTGRES，現(xiàn)在已經(jīng)更名為PostgreSQL，版本 4.2為基礎(chǔ)的對(duì)象關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（ORDBMS）。PostgreSQL支持大部分 SQL標(biāo)準(zhǔn)并且提供了許多其他現(xiàn)代特性：復(fù)雜查詢、外鍵、觸發(fā)器 、視圖、事務(wù)完整性、MVCC。同樣，PostgreSQL 可以用許多方法擴(kuò)展，比如， 通過增加新的數(shù)據(jù)類型、函數(shù)、操作符、聚 集函數(shù)、索引。免費(fèi)使用、修改、和分發(fā) PostgreSQL，不管是私用、商用、還是學(xué)術(shù)研究使用。 PostgreSQL的官方網(wǎng)站是：h

2、ttps://PostgreSQL的特點(diǎn)如下 -PostgreSQL可在所有主要操作系統(tǒng)(即Linux，UNIX(AIX，BSD，HP-UX，SGI IRIX，Mac OS X，Solaris，Tru Windows等)上運(yùn)行。 )和PostgreSQL支持文本，圖像，聲音和視頻，并包括用于C/C+，Java，Perl，Python，Ruby，Tcl和開放數(shù)據(jù)庫連接(ODBC)的編程接口。 PostgreSQL支持SQL的許多功能，例如復(fù)雜SQL查詢，SQL子選擇，外鍵，觸發(fā)器，視圖，事務(wù)，多進(jìn)程并發(fā)控制(MVCC)，流式復(fù)制(9.0)，熱備(9.0)。 在

3、PostgreSQL中，表可以設(shè)置為從“父”表繼承其特征。可以安裝多個(gè)擴(kuò)展以向PostgreSQL添加附加功能。 老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL安裝 下載地址：/download/linux/redhat/安裝步驟： 1. yumtall /pub/repos/yum/11/redhat/rhel-7-x86_/pgdg-centos11-11-2.noarch.rpm2. yumtall postgresql11yumtall postgresql11-serve

4、r初始化數(shù)據(jù)庫： /usr/pgsql-11/bin/postgresql-11-setup initdb啟動(dòng)數(shù)據(jù)庫 systemctl enable postgresql-11 systemctl start postgresql-11老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL安裝 查看數(shù)據(jù)庫運(yùn)行進(jìn)程： -bash-4.2$ ps -ef|grep postgres關(guān)閉數(shù)據(jù)庫： -bash-4.2$ /usr/pgsql-11/bin/pg_ctl stopwaiting for server to shut downdone server stopped重啟數(shù)據(jù)庫： -bash-4

5、.2$ /usr/pgsql-11/bin/pg_ctl restart waiting for server to shut downdoneserver stoppedwaiting for server to start2018-11-13 21:51:05.533 EST 12823 LOG: listening on IPv6 address :1, port 54322018-11-13 21:51:05.533 EST 12823 LOG: listening on IPv4 address 127.0 0.1, port 54322018-11-13 21:51:05.534

6、EST 12823 LOG: listening on Unix socket /var/run/postgresql/.s.PGSQL.5432 2018-11-13 21:51:05.536 EST 12823 LOG: listening on Unix socket /tmp/.s.PGSQL.5432 2018-11-1321:51:05.542 EST 12823 LOG: redirecting log output to logging collector process 2018-11-13 21:51:05.542 EST 12823 HINT: Future log ou

7、tput will appear in directory log. doneserver started老男孩IT教育，只培養(yǎng)技術(shù)精英postgres127061 021:34 pts/100:00:00 /usr/pgsql-11/bin/postgres -D /var/lib/pgsql/11/data/postgres12707127060 21:34 ?00:00:00 postgres: loggerpostgres12709127060 21:34 ?00:00:00 postgres: checkpointerpostgres12710127060 21:34 ?00:00:

8、00 postgres: background writerpostgres12711127060 21:34 ?00:00:00 postgres: walwriterpostgres12712127060 21:34 ?00:00:00 postgres: autovacuum launcherpostgres12713127060 21:34 ?00:00:00 postgres: stats collectorpostgres12714127060 21:34 ?00:00:00 postgres: logical replication launcherPostgreSQL常見操作

9、連接數(shù)據(jù)庫： 初始的超級(jí)管理員用戶是postgres，需要操作系統(tǒng)切換到postgres用戶下直接無 登 錄 rootlocalhost # su - postgresLast login: Tue Nov 13 21:33:00 EST 2018 on-Tbyapseh-h4e.l2p$ fporsqhl elp.ql (11.1)postgres=#創(chuàng)建數(shù)據(jù)庫：pts/1postgres=# create database course;CREATE DATABASE查看所有數(shù)據(jù)庫：postgres=# lList of databases| Encoding | Collate|Ctyp

10、e|AccessName|O-w-n-er- -+-p-r-iv-il-eg-e-s-+ -+-+- + |course|postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |postgrespostgres | UTF8| en_US.UTF-8 | en_US.UTF-8 |+templ|ate0| postg|res | UT|F8| epno_sUtgSr.UesT=FC-T8c/| penos_tUgSr.eUsTF-8 |=tce/mppolsatger1es| postgres | en_US.UTF-8 | en_US.UTF-8 |UTF8

11、rows)=c/postgres| postgres=CTc/postgres|(4老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL數(shù)據(jù)類型 老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL常見操作 切換到某個(gè)數(shù)據(jù)庫下： postgres=# c courseYou are now connected to database course as user postgres.course=#創(chuàng)建表：course=# create table students(sid int primary key, sname varchar(10),genderint,dept_id int);CREAT

12、E TABLE插入數(shù)據(jù)： course=#ert into students values(1,a,1,1);ERT 0 1修改數(shù)據(jù)： course=# update students set sname=abc where sid=1;UPDATE 1查詢數(shù)據(jù)： course=# select * from students;sid | sname | gender |dept_id + + + 1 | abc |1 |1(1 row)刪除數(shù)據(jù)：course=# delete from students where sid=1;DELETE 1老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQ

13、L常見操作 查看該數(shù)據(jù)庫下所有的表：course=# dtList of relationsSchema | Name| Type | Owner + + public | dept| table | postgrespublic | students | table | postgres (2 rows)查看該數(shù)據(jù)庫下所有表的大小： course=# dt+List of relations|Size|Schema |Name| Type |O-w-n-er- -+-+- -D-e-s+cr-i-pt-i-on +- + public | dept | table | postgres |

14、0 bytes students | table | postgres | 8192 bytes | (2 rows)查看某個(gè)表的表結(jié)構(gòu)定義： |public |course=# d+ students;Tablep|uCbolilcl.astiuodnen| tNsullable | Default | Storage | Stats target |Column|Type-+- -D-e-s-cr-ip-t-io-n- -+-+-+-+s-id-|-in-t-e-g-er-+- -| -| -no-t-n-u+l-l -| -|-p-la-i-n-| -| extended |sname|

15、 character varying(10) | plain|gender | plain|integerdept_idI|nidnetxegse:rstudents_pkey PRIMARY KEY, btree(sid)老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL常見操作 創(chuàng)建索引： course=# create index idx_sname on students(sname);CREATE INDEXcourse=# create unique index idx_union on students(sname,gender); #創(chuàng)建聯(lián)合索引，唯一索引 CREATE IND

16、EXcourse=# create index idx_sname on dept(dept_name); #索引名字必須是唯一的 ERROR: relation idx_sname already exists刪除索引：course=# drop indexidx_union;DROP INDEX截?cái)啾頂?shù)據(jù)：course=# truncate table students;TRUNCATE TABLE老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL自增字段設(shè)置 PostgreSQL具有數(shù)據(jù)類型allserial，serial和 b類ig型se名ri稱alserial用于創(chuàng)建整數(shù)列。 類型名

17、稱bigserial創(chuàng)建一個(gè)bigint類型的列。 如果您期望在表的使用期限內(nèi)使用超過231個(gè)標(biāo)識(shí)符，則 應(yīng)使用bigserial。 類型名稱allserial創(chuàng)建一個(gè)allint列。 course=# create table students2(sid serial ,sname varchar(10),genderint,dept_id int);CREATE TABLEcourse=# d+ students2;Table public.students2Column |Type| Collation | Nullable |Default| Storage | Stats targe

18、t | Description| plain|gender | integer|dept_id | integer| plain|course=#ert intostudents2(sname,gender,dept_id) values(a,1,1),(b,2,2);ERT 0 2course=# select * from students2;sid | sname | gender | dept_id + + + 1 | a|1 |12 | b|2 |2(2 rows)rse=#ert into students2values(10,c,1,1);ERT 0 1course=# sele

19、ct * from students2;sid | sname | gender | dept_id + + + 1 | a|1 |12 | b|2 |210 | c|1 |1(3 rows)rse=#ert into students2(sname,gender,dept_id) values(d,1,1);ERT 0 1course=# select * from students2;sid | sname | gender | dept_id + + + 1 | a|1 |12 | b|2 |210 | c|1 |13 | d|1 |1(4 rows)course=# d+List of

20、 relationsSchema |Name|Type| Owner|Size| Description + + + + + public | dept| table| postgres | 0 bytes|public | students| table| postgres | 0 bytes|public | students2| table| postgres | 8192 bytes |public | students2_sid_seq | sequence | postgres | 8192 bytes|(4 rows)01老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL事務(wù)操作

21、 事務(wù)具有以下四個(gè)標(biāo)準(zhǔn)屬性，一般是由首字母縮寫詞ACID簡稱： 原子性(Atomicity)：確保工作單位內(nèi)的所有操作成功完成; 否則事務(wù)將在故障點(diǎn)中止，以前的操作回滾到其以前的狀態(tài)。一致性(Consistency)：確保數(shù)據(jù)庫在成功提交的事務(wù)時(shí)正確更改狀態(tài)。 隔離性(Isolation)：使事務(wù)能夠獨(dú)立運(yùn)作并相互透明。 持久性(Durability)：確保在系統(tǒng)發(fā)生故障的情況下，提交的事務(wù)的結(jié)果或效果仍然存在。 以下用于控制事務(wù)： BEGIN TRANSACTION/ BEGIN：開始事務(wù)。 COMMIT：保存更改，或者您可以使用END TRANSACTION。 ROLLBACK：回滾更改。

22、 ERT，UPDATE和DELETE。 創(chuàng)建表或刪除它們時(shí)不能使用它們，因?yàn)檫@些操作會(huì)在數(shù)據(jù)庫中自動(dòng)提僅用于DML事務(wù)控制交。 course=# begin; BEGINcourse=# update students set sname=bb;UPDATE 1#在另一個(gè)連接里看到的數(shù)據(jù)還是update前的數(shù)據(jù) course=# select * from students; sid | sname | gender | dept_id + + + 1 | bb|1 |1(1 row)course=# rollback; ROLLBACK course=# select * from stud

23、ents; sid | sname | gender | dept_id + + + 1 | a|1 |1老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL文件目錄結(jié)構(gòu) -bash-4.2$ pwd/var/lib/pgsql/11/data-bash-4.2$ lsbaselogpg_hba.confpg_multixactpg_serialpg_stat_tmp pg_twophasepg_xactpostmaster.optscurrent_logfiles pg_commit_tspg_ident.confpg_notify postgresql.auto.conf postmaste

24、r.pidpg_snapshots pg_subtransPG_VERSIONglobalpg_dynshmempg_logicalpg_replslotpg_statpg_tblspcpg_walpostgresql.conf重要文件和目錄： 配置文件：postgresql.conf登錄權(quán)限控制文件: pg_hba.conf數(shù) 據(jù) 目 錄 ：base 運(yùn)行日志目錄：log 歸檔日志目錄: pg_wal老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL重要配置參數(shù) Postgresql.conf文件作為控制postgresql啟動(dòng)運(yùn)行的參數(shù)文件，其作用和mysql中的f一樣data_

25、directory = ConfigDir# use data in anotherdirectory # (change requires restart)hba_file = ConfigDir/pg_hba.conf# host-based authentication file# (change requires restart)listen_addresses = localhost# what IP address(es) to listen on;# comma-separated list of addresses; # defaults to localhost; use *

26、 for all # (change requires restart)# (change requires restart)# (change requires restart)port = 5432max_connections = 100wal_level = replica# minimal, replica, or logical # (change requires restart)# - Archiving -archive_mode = on# enables archiving; off, on, or always # (change requires restart)ar

27、chive_command = cd .# command to use to archive a logfile segmentmax_wal_senders = 10# max number of walsender processes # (changerequires restart)wal_keep_segments = 100# in logfile segments; 0 disableshot_standby = on# off disallows queries during recovery # (change requires restart)老男孩IT教育，只培養(yǎng)技術(shù)精

28、英PostgreSQL重要配置參數(shù) 比如修改配置文件 listen_addresses = localhost,29重啟數(shù)據(jù)庫 -bash-4.2$ /usr/pgsql-11/bin/pg_ctl restart查看端口偵聽情況是否改變老男孩IT教育，只培養(yǎng)技術(shù)精英tcp00 29:543:*LISTENtcp00 :543:*LISTENPostgreSQL授權(quán) 在數(shù)據(jù)庫中創(chuàng)建對(duì)象時(shí)，都會(huì)為其分配所有者。 所有者通常是執(zhí)行創(chuàng)建語句的用戶。 對(duì)于大多數(shù)類型的對(duì)象，初始狀態(tài)是只有所有者(或超級(jí)用戶

29、)可以修改或刪除對(duì)象。 要允許其他角色或 用戶使用它，必須授予權(quán)限或權(quán)限。PostgreSQL中的不同類型的權(quán)限是：SELECT,ERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY,EXECUTE 和USAGE。創(chuàng)建用戶： postgres=# create user cdq with password cdq; CREATE ROLEpostgres=# du+List of rolesRole name |Attributeember of | Description + + + cdq| |post

30、gres | Superuser, Create role, Create DB, Replication, Bypass RLS | |GRANT的語法 GRANT的基本語法如下： GRANT privilege , . ON object , .TO PUBLIC | GROUP group | username privilege值可以是：SELECT，ERT，UPDATE，DELETE，RULE，ALL。object：要向其授予訪問權(quán)限的對(duì)象的名稱。 可能的對(duì)象是：表，視圖，序列PUBLIC：表示所有用戶的簡短形式。 GROUP group：授予權(quán)限的組。 username：授予權(quán)限的

31、用戶的名稱。 PUBLIC是表示所有用戶的簡短形式老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL授權(quán) 授權(quán)： course=# grant all on all tables GRANTchema public tocdq;course=# dp+Access privilegesSchema |Name|Type|Access privileges| Column privileges | Policies + + + + + public | dept|public | students| table| postgres=arwdDxt/postgres+| cdq=arwdDxt/p

32、ostgres| table| postgres=arwdDxt/postgres+| cdq=arwdDxt/postgres|public | students2| table| postgres=arwdDxt/postgres+| cdq=arwdDxt/postgres|public | students2_sid_seq | sequence|course=# revoke all on all tables REVOKEchema public fromcdq;course=# grant select,update on students,dept tocdq; GRANTco

33、urse=# dp+Access privilegesSchema |Name|Type|Access privileges| Column privileges | Policies + + + + + public | dept|public | students| table| postgres=arwdDxt/postgres+| cdq=rw/postgres| table| postgres=arwdDxt/postgres+| cdq=rw/postgres|public | students2| table| postgres=arwdDxt/postgres |public

34、| students2_sid_seq | sequence|老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL授權(quán) 特定用戶本地登錄： -bash-4.2$ psql -U cdq -W -h postgres psql: FATAL: Peer authentication failed for user“cdqvi pg_hba.confhostallcdq/32md5-bash-4.2$ /usr/pgsql-11/bin/pg_ctl reload server signaled-bash-4.2$ psql -U cdq -W -h 127.0

35、.0.1 postgres Password:psql (11.1)Type help for help.postgres= postgres= c course Password for user cdq: You are now connected to database course as user cdq. course= select * from students;sid | sname | gender | dept_id + + + (0 rows)course= select * from dept; id |dept_name + (0 rows)course= delet

36、e from students;ERROR: permission denied for table students老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL授權(quán) 授權(quán)之后新創(chuàng)建的表沒有權(quán)限：-bash-4.2$ psql -U cdq -W -h postgresPassword:psql (11.4)Type help for help.postgres= c coursePassword for user cdq:You are now connected to database course as user cdq.course= dtList of re

37、lationsSchema |Name| Type|Owner + + + public | students| table | postgrespublic | students2 | table | postgrespublic | students3 | table | postgres(3 rows)course= select * from students3;ERROR:permission denied for table students3需要授予默認(rèn)權(quán)限： course=# ALTER DEFAULT PRIVILEGES FOR ROLE postgres grant al

38、l on tables to cdq;ALTER DEFAULT PRIVILEGEScourse=# create table students4(id int);CREATE TABLEcourse=# dp+ students4Access privilegesSchema |Name| Type|Access privileges| Column privileges | Policies + + + + + public | students4 | table | postgres=arwdDxt/postgres+| cdq=arwdDxt/postgres|老男孩IT教育，只培養(yǎng)

39、技術(shù)精英PostgreSQL授權(quán) 遠(yuǎn)程登錄： -bash-4.2$ psql -h 29 -U cdq -W course Password:psql: FATAL: no pg_hba.conf entry for host 30, user cdq, database course, SSL off修改數(shù)據(jù)庫端的pg_hba.conf文件hostcoursecdq30/32md5reload配置 -bash-4.2$ /usr/pgsql-11/bin/pg_ctl reload-bash-4.2$ psql -h 1

40、29 -U cdq -W course Password: psql (11.1)Type help for help.course= c postgres Password for user cdq:FATAL: no pg_hba.conf entry for host 30, user cdq, database postgres, SSL off Previous connection keptcourse=老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL復(fù)制 主庫上創(chuàng)建復(fù)制用戶： postgres=# create user repli

41、cator replication password cdq; CREATE ROLEpostgres=# du+List of rolesRole name |Attributeember of | Description + + + cdq cdq2 postgres| | | Superuser, Create role, Create DB, Replication, Bypass RLS| |replicator | Replication| |主庫修改pg_hba.conf，允許replicator用戶遠(yuǎn)程登錄： hostreplicationreplicator192.168.5

42、7.130/32md5reload配置 -bash-4.2$ /usr/pgsql-11/bin/pg_ctl reloadserver signaled從庫上執(zhí)行復(fù)制數(shù)據(jù)庫： -bash-4.2$ pg_basebackup -checkpoint=fast -h 29 -D /var/lib/pgsql/11/data -U replicator -v -P Password:pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpo

43、int completedpg_basebackup: write-ahead log start point: 0/2000028 on timeline 1 pg_basebackup: starting background WAL receiverpg_basebackup: created temporary replication slot pg_basebackup_14561 32713/32713 kB (100%), 1/1 tablespacepg_basebackup: write-ahead log end point: 0/2000130 pg_basebackup

44、: waiting for background process to finish streaming . pg_basebackup: base backup completed老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL復(fù)制 在從庫創(chuàng)建恢復(fù)參數(shù)文件： -bash-4.2$ vi recovery.conf standby_mode = onprimary_conninfo = user=replicator password=cdq host=29 port=5432啟動(dòng)從庫： -bash-4.2$ /usr/pgsql-11/bin/pg_ctl star

45、t在主庫查看同步狀態(tài)： postgres=# table pg_stat_replication;pid| usesysid |usename| application_name | client_addr| client_hostname | client_port |backend_start|backend_xmtate| sent_lsn| write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state + + + + + + + + + + + +

46、 + + + + + + 14574 |108 | replicator | walreceiver| 30 |41506 | 2018-11-14 03:46:58.636784-05 | streaming | 0/3000140 | 0/30001 40 | 0/3000140 | 0/3000140|(1 row)|0 | async老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL復(fù)制 驗(yàn)證同步是否工作： 在主庫上執(zhí)行： course=#ERT 0 1ert into students values(100,aaa,1,1);查看從庫是否有數(shù)據(jù)同步： cour

47、se=# select * from students; sid | sname | gender | dept_id + + + 1 | a1 | 11 |1100 | aaa|老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL高可用 pgpool-II 是一個(gè)位于 PostgreSQL 服務(wù)器和 PostgreSQL 數(shù)據(jù)庫客戶端之間的中間件，它提供以下功能：連接池、復(fù)制、負(fù)載均衡、限制超過限度的連接以及 并行查詢安裝pgpool:RHEL/CentOS 7.x (Pgpool-I 4.0)# yum x86_tall/yum/rpms/4.0/re

48、dhat/rhel-7-/pgpool-II-release-4.0-1.noarch.rpmyumtall -y pgpool-I -pg11mkdir -p/var/log/pgpool/修改配置文件:vi /etc/pgpool I /pgpoolconfrootlocalhost pgpool-I # systemctl enable pgpool.service rootlocalhost pgpool-I # systemctl start pgpool.service rootlocalhost pgpool- I # ps -ef|greppgpoolroot148801 0

49、04:39 ?00:00:00 /usr/bin/pgpool -f /etc/pgpool-I老男孩IT教育，只培養(yǎng)技術(shù)精英啟動(dòng)pgpool:PostgreSQL高可用 創(chuàng)建pgpool用戶： postgres=# CREATE user pgpoolmgr passwordcdq SUPERUSER; CREATE ROLE在主從postgresql中修改pg_hba.conf文件： hostallpgpoolmgr/24md5-bash-4.2$ /usr/pgsql-11/bin/pg_ctl reload配置pgpool.conf:# - Backend Co

50、nnection Settings - backend_hostname0 =29backend_port0 = 5432backend_weight0 = 1backend_data_directory0 =/var/lib/pgsql/11/data backend_flag0 = ALLOW_TO_FAILOVERbackend_hostname1 = 30backend_port1 = 5432backend_weight1 = 1backend_data_directory1 =/var/lib/pgsql/11/data backen

51、d_flag1 = ALLOW_TO_FAILOVERenable_pool_hba = on pool_passwd = pool_passwd load_balance_mode = on master_slave_mode = on sr_check_period = 5 sr_check_user = pgpoolmgr sr_check_password= cdq health_check_period = 10 health_check_user = pgpoolmgr health_check_password = cdq health_check_database = postgres老男孩IT教育，只培養(yǎng)技術(shù)精英PostgreSQL高可用 修改pgpool可登錄用戶pool_hba.conf # IPv4 local connections:hostallall/32md5設(shè)置pgpool可用的數(shù)據(jù)庫用戶：（事先在postgresql