數(shù)據(jù)庫操作

?專欄內(nèi)容：

• postgresql內(nèi)核源碼分析
• 手寫數(shù)據(jù)庫toadb
• 并發(fā)編程

?開源貢獻：

• toadb開源庫

個人主頁：我的主頁
管理社區(qū)：開源數(shù)據(jù)庫
座右銘：天行健，君子以自強不息；地勢坤，君子以厚德載物.

系列文章

• 入門準備
• postgrersql基礎(chǔ)架構(gòu)
• 快速使用
• 初始化集群
• 數(shù)據(jù)庫服務(wù)管理
• psql客戶端使用
• 創(chuàng)建數(shù)據(jù)庫
• 數(shù)據(jù)庫操作

前言

postgresql 數(shù)據(jù)庫是一款通用的關(guān)系型數(shù)據(jù)，在開源數(shù)據(jù)庫中能與商業(yè)數(shù)據(jù)媲美，在業(yè)界也越來越流行。

因為是開源數(shù)據(jù)庫，不僅公開源碼，還有很多使用案例，好用的插件，所以它的慢慢變成了數(shù)據(jù)庫的先驅(qū)和標準，通過postgresql可以很好從使用到原理，徹底搞懂；

如果是學(xué)習(xí)編程，也可以學(xué)到豐富的編程知識，數(shù)據(jù)結(jié)構(gòu)，編程技巧，它里面還有很多精妙的架構(gòu)設(shè)計，分層思想，可以靈活定制的思想。

本專欄主要介紹postgresql 入門使用，數(shù)據(jù)庫維護管理，通過這些使用來了解數(shù)據(jù)庫原理，慢慢了解postgresql是什么樣的數(shù)據(jù)庫，能做那些事情，以及如何做好服務(wù)，最關(guān)鍵的是這些知識都是面試的必備項。

概述

本文主要分享數(shù)據(jù)庫系統(tǒng)中最基本的管理單位database，對它的一系列管理操作，主要從以下幾方面展開：

• 查詢所有數(shù)據(jù)庫
• 進入指定數(shù)據(jù)庫
• 創(chuàng)建數(shù)據(jù)庫
• 修改數(shù)據(jù)庫
• 刪除數(shù)據(jù)庫
• 查看數(shù)據(jù)庫的統(tǒng)計

查看所有數(shù)據(jù)庫

當(dāng)作為DBA進行維護時，我們需要知道當(dāng)前集群中有多少數(shù)據(jù)庫，查看方法常用以下幾種；

• 通過SQL查詢數(shù)據(jù)字典
• 通過psql命令查看數(shù)據(jù)庫概要列表

通過SQL查看數(shù)據(jù)字典

在數(shù)據(jù)庫系統(tǒng)中，所有數(shù)據(jù)的組織信息，我們統(tǒng)稱為數(shù)據(jù)字典，數(shù)據(jù)字典呈現(xiàn)出來的邏輯形式，就是一系列的表，因為它是數(shù)據(jù)庫系統(tǒng)自動生成，并且自動維護的，我們也叫做系統(tǒng)表。

對于database的信息，也記錄在一張系統(tǒng)表當(dāng)中，它就是 pg_database, 先來看一下它的表中有那些字段；

postgres=# \d pg_database
               Table "pg_catalog.pg_database"
     Column     |   Type    | Collation | Nullable | Default
----------------+-----------+-----------+----------+---------
 oid            | oid       |           | not null |
 datname        | name      |           | not null |
 datdba         | oid       |           | not null |
 encoding       | integer   |           | not null |
 datlocprovider | "char"    |           | not null |
 datistemplate  | boolean   |           | not null |
 datallowconn   | boolean   |           | not null |
 datconnlimit   | integer   |           | not null |
 datfrozenxid   | xid       |           | not null |
 datminmxid     | xid       |           | not null |
 dattablespace  | oid       |           | not null |
 datcollate     | text      | C         | not null |
 datctype       | text      | C         | not null |
 daticulocale   | text      | C         |          |
 daticurules    | text      | C         |          |
 datcollversion | text      | C         |          |
 datacl         | aclitem[] |           |          |
Indexes:
    "pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"
Tablespace: "pg_global"

各字段的含義解釋如下：

重點了解幾個字段，在系統(tǒng)內(nèi)部每個數(shù)據(jù)庫對象都有唯一標識 OID,也就是一個編號，對應(yīng)一個表空間，也就是存儲層次；另外還有訪問權(quán)限的限制，并不是隨意都可以訪問；
特別的，我們需要知道，database的所有者是誰，也就是datdba字段，因為它可是這個database的老大；

好了，了解之后，就很簡單，我們查詢一下就知道當(dāng)前數(shù)據(jù)庫系統(tǒng)有多少個database了；

db_factory=> select oid,datname,datdba,dattablespace , datacl from pg_database ;
  oid  |   datname   | datdba | dattablespace |                     datacl
-------+-------------+--------+---------------+------------------------------------------------
     5 | postgres    |     10 |          1663 |
     1 | template1   |     10 |          1663 | {=c/senllang,senllang=CTc/senllang}
     4 | template0   |     10 |          1663 | {=c/senllang,senllang=CTc/senllang}
 16446 | db_factory1 |     10 |          1663 |
 16445 | db_factory  |  16451 |          1663 | {=Tc/manager,manager=CTc/manager,hr=C/manager}
(5 rows)

剛說到訪問權(quán)限，那么對于系統(tǒng)表的訪問權(quán)限，因為是字典嘛，默認是所有用戶都有權(quán)限；

psql查看數(shù)據(jù)庫列表

psql是常用的命令行客戶端，它本身提供了很多簡單的操作命令，比如查看數(shù)據(jù)庫的列表就是 \l, 在之前分享中也多次用過；

db_factory=> \l
                                                        List of databases
    Name     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 db_factory  | manager  | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =Tc/manager          +
             |          |          |                 |             |             |            |           | manager=CTc/manager  +
             |          |          |                 |             |             |            |           | hr=C/manager
 db_factory1 | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 postgres    | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |
 template0   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +
             |          |          |                 |             |             |            |           | senllang=CTc/senllang
 template1   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +
             |          |          |                 |             |             |            |           | senllang=CTc/senllang
(5 rows)

可以看到執(zhí)行結(jié)果，數(shù)量是一樣，但是信息比從pg_database查到的少一些，它主要提供一個概要信息，如果需要詳細信息還是要從系統(tǒng)表中查看。

進入指定數(shù)據(jù)庫

在每次登錄時，都是需要指定某個數(shù)據(jù)庫；當(dāng)然登錄的方式可以是圖形化客戶端，或者是JDBC的連接，也可以是psql；
比如通過psql進入數(shù)據(jù)庫 db_factory ，用戶為 hr;

[senllang@hatch bin]$ ./psql -d db_factory  -U hr
psql (16beta1)
Type "help" for help.

db_factory=>

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE db_factory;

創(chuàng)建數(shù)據(jù)庫詳細步驟和方面，已經(jīng)在之前分享過了，這里不再重復(fù)，查看本專欄內(nèi)容；

修改數(shù)據(jù)庫

很少有人修改數(shù)據(jù)庫的屬性，因為數(shù)據(jù)庫一般都是提前規(guī)劃好的，而且影響比較大。 但是作為DBA需要了解，有那些屬性是可以修改，以及修改帶來的影響會是什么。

數(shù)據(jù)庫可以被修改的大致主要有以下幾類型：

• 修改數(shù)據(jù)庫的屬性；
• 修改數(shù)據(jù)庫的名稱；
• 修改數(shù)據(jù)庫的表空間；
• 修改數(shù)據(jù)庫的擁有者onwer；
• 數(shù)據(jù)庫運行時參數(shù)

當(dāng)然做這些操作時，當(dāng)前數(shù)據(jù)庫用戶為超級管理員用戶或者擁有者;

數(shù)據(jù)庫屬性修改

數(shù)據(jù)庫屬性中的以下值可以進行修改

• ALLOW_CONNECTIONS 連接屬性；
• CONNECTION LIMIT 連接數(shù)量限制；
• IS_TEMPLATE 模版屬性
  這些參數(shù)就是pg_database系統(tǒng)表中所列的內(nèi)容，影響數(shù)據(jù)庫的訪問；

數(shù)據(jù)庫名稱修改

數(shù)據(jù)庫名稱變更

postgres=# \c postgres manager
You are now connected to database "postgres" as user "manager".
postgres=> alter database db_factory rename to db_factory2;
ALTER DATABASE

manager用戶具用createdb權(quán)限，同時也是db_factory的擁有者；不能修改當(dāng)前登錄數(shù)據(jù)庫的名稱，所以我們登錄到其它數(shù)據(jù)庫，再執(zhí)行rename的SQL語句。

數(shù)據(jù)庫所屬表空間變更

在分享存儲空間時，提到表空間其實是數(shù)據(jù)庫的物理存儲位置；那么變更表空間，也意味著給數(shù)據(jù)庫搬個家，聽起來就覺得麻煩。

這個命令的應(yīng)用場景也很少，只有當(dāng)前表空間的存儲空間不足時，或者當(dāng)前表空間對應(yīng)的磁盤需要維護時，給數(shù)據(jù)庫挪個位置，如果數(shù)據(jù)庫中數(shù)據(jù)比較關(guān)鍵，提前需要做好備份歸檔。

首先修改的用戶必須有對應(yīng)表空間的使用權(quán)限，我們還是用manager用戶操作，給manager用戶賦予表空間tblspc_test1的所有權(quán)限。

postgres=# grant all ON tablespace tblspc_test1 TO manager ;
GRANT

切換到以manager用戶登錄，更換db_factory2的表空間，原來是在默認表空間下；

postgres=# \c postgres manager
You are now connected to database "postgres" as user "manager".
postgres=> alter database db_factory2 set tablespace tblspc_test1 ;
ALTER DATABASE

數(shù)據(jù)庫的owner變更

owner是這個數(shù)據(jù)庫的所有權(quán)限的擁有者，更換之后原來的owner用戶會失去權(quán)限；

可以執(zhí)行的前提前，執(zhí)行者可以是超級管理員，或者是當(dāng)前的owner用戶，同時還是新owner用戶的成員，否則不能執(zhí)行；也就是說，新owner和舊的owner在執(zhí)行后，會具用相同的權(quán)限；如果不需要舊owner用戶時，可以從成員中踢除即可；這一限制保證變更過程的平滑；

假設(shè)要把db_factory2的擁有者從manager更換為 vp用戶；

• 先用超級管理員用戶創(chuàng)建新用戶 vp，并且賦予 createdb權(quán)限；

postgres=# create user vp createdb password '123456';
CREATE ROLE

• 將manager作為新用戶的成員

postgres=# grant manager to vp;
GRANT ROLE

• 然后用 manager用戶登錄數(shù)據(jù)庫 db_factory2，進行擁有者切換

postgres=# \c db_factory2 manager
You are now connected to database "db_factory2" as user "manager".
db_factory2=> alter database db_factory2 owner to vp;
ALTER DATABASE

運行時參數(shù)修改

數(shù)據(jù)庫中的參數(shù)很多，具體可以查看配置文件

比如在數(shù)據(jù)庫 db_factory2 禁用索引掃描，可以執(zhí)行以下SQL

ALTER DATABASE db_factory2 SET enable_indexscan TO off;

刪除數(shù)據(jù)庫

當(dāng)我們不需要某個數(shù)據(jù)庫時，可以進行刪除，釋放對應(yīng)的磁盤空間，同時避免后臺數(shù)據(jù)庫服務(wù)不斷掃描廢棄數(shù)據(jù)庫，進行維護任務(wù)；

刪除數(shù)據(jù)庫的方法，可以用命令dropdb,也可以使用SQL語句；

命令刪庫

在安裝目錄下的bin目錄下，可以看到dropdb命令，查看幫助如下

[senllang@hatch bin]$ ./dropdb --help
dropdb removes a PostgreSQL database.

Usage:
  dropdb [OPTION]... DBNAME

Options:
  -e, --echo                show the commands being sent to the server
  -f, --force               try to terminate other connections before dropping
  -i, --interactive         prompt before deleting anything
  -V, --version             output version information, then exit
  --if-exists               don't report error if database doesn't exist
  -?, --help                show this help, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt
  --maintenance-db=DBNAME   alternate maintenance database

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

可以看到，默認情況下，參數(shù)是數(shù)據(jù)庫名稱就可以了。

這也是推薦的刪除數(shù)據(jù)庫的方式，不需要登錄到某一個數(shù)據(jù)庫中；

SQL刪庫

postgresql 還提供了SQL方式，SQL標準中沒有這個命令；

DROP DATABASE db_factory2;

當(dāng)然這個由管理員和擁有者執(zhí)行，當(dāng)有該數(shù)據(jù)庫還有連接時，會執(zhí)行失??；

• 特別注意，刪除數(shù)據(jù)庫是不能撤銷的動作，要特別小心；

查看數(shù)據(jù)庫的統(tǒng)計

當(dāng)然在維護時，我們還可以查看數(shù)據(jù)庫上的一些指標，也就是統(tǒng)計信息，來衡量數(shù)據(jù)庫的狀態(tài)；

這些信息在pg_stat_database 這張系統(tǒng)表中

postgres=# select * from pg_stat_database where datname='db_factory2';
 datid |   datname   | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | che
cksum_failures | checksum_last_failure | blk_read_time | blk_write_time | session_time | active_time | idle_in_transaction_time | sessions | sessions_abandoned | sessions_fatal | sessions_killed | stats_reset
-------+-------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+----
---------------+-----------------------+---------------+----------------+--------------+-------------+--------------------------+----------+--------------------+----------------+-----------------+-------------
 16445 | db_factory2 |           0 |        6339 |            19 |       218 |   244990 |      2750338 |       52930 |          134 |          12 |          94 |         0 |          0 |          0 |         0 |
               |                       |             0 |              0 | 56623309.239 |     253.288 |                        0 |       29 |                  0 |              0 |               0 |
(1 row)

前綴是 pg_stat_ 系統(tǒng)表，是一類統(tǒng)計信息記錄表，可以看到一些運行狀態(tài)的統(tǒng)計；比如數(shù)據(jù)庫統(tǒng)計信息中連接數(shù)量，事務(wù)提交、回滾的數(shù)量，等等。這些信息在后面用到時會再詳細介紹。

總結(jié)

希望通過本篇分享，能對數(shù)據(jù)庫有進一步了解，對于數(shù)據(jù)庫的在系統(tǒng)中的維一標識，擁有者的權(quán)限，它是擁有該數(shù)據(jù)庫所有權(quán)限，同時每個數(shù)據(jù)庫都在存儲位置，對應(yīng)的就是表空間，這些信息可以通過登錄，查看數(shù)據(jù)庫的系統(tǒng)字典，以及統(tǒng)計信息來詳細了解。

結(jié)尾

非常感謝大家的支持，在瀏覽的同時別忘了留下您寶貴的評論，如果覺得值得鼓勵，請點贊，收藏，我會更加努力！

作者郵箱：study@senllang.onaliyun.com
如有錯誤或者疏漏歡迎指出，互相學(xué)習(xí)。

注：未經(jīng)同意，不得轉(zhuǎn)載！文章來源地址http://www.zghlxwxcb.cn/news/detail-709650.html

到了這里，關(guān)于【postgresql 基礎(chǔ)入門】從了解數(shù)據(jù)庫訪問權(quán)限，訪問數(shù)據(jù)庫，到認識數(shù)據(jù)庫的所有者及屬性，從此打開了數(shù)據(jù)庫使用的大門的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！