PostgreSQL教程(十七)：用戶端命令（1），postgresql教程

PostgreSQL教程(十七)：用戶端命令（1），postgresql教程

零、口令檔案：

    在給出其它PostgreSQL用戶端命令之前，我們需要先介紹一下PostgreSQL中的口令檔案。之所以在這裡提前說明該檔案，是因為我們在後面的範例程式碼中會大量應用該檔案，從而保證我們的指令碼能夠自動化完成。換句話說，如果在用戶端命令執行時沒有提供該檔案，PostgreSQL的所有用戶端命令均會被口令輸入提示中斷。
    在目前使用者的HOME目錄下，我們需要手工建立檔案名稱為 .pgpass的口令檔案，這樣就可以在我們串連PostgreSQL伺服器時，用戶端命令自動讀取該檔案已獲得登入時所需要的口令資訊。該檔案的格式如下：
 複製代碼 代碼如下:
    hostname:port:database:username:password
 
    以上資料是用冒號作為分隔字元，總共分為五個欄位，分別表示伺服器主機名稱(IP)、伺服器監聽的連接埠號碼、登入訪問的資料庫名、登入使用者名稱和密碼，其中前四個欄位都可以使用星號(*)來表示匹配任意值。見如下樣本：
 複製代碼 代碼如下:
    /> cat > .pgpass
    *:5432:postgres:postgres:123456
    CTRL+D
    #.pgpass檔案的許可權必須為0600，從而防止任何全域或者同組的使用者訪問，否則這個檔案將被忽略。
    /> chmod 0600 .pgpass
 
    在學習後面的用戶端命令之前，我們需要根據自己的應用環境手工建立該檔案，以便後面所有的範例程式碼都會用到該口令檔案，這樣它們就都可以以批處理的方式自動完成。

一、createdb：

    建立一個新的PostgreSQL資料庫。該命令的使用方式如下：
 複製代碼 代碼如下:
    createdb [option...] [dbname] [description]
 
    1. 命令列選項列表：

選項	說明
-D(--tablespace=tablespace)	指定資料庫的預設資料表空間。
-e(--echo)	回顯createdb產生的命令並且把它發送到伺服器。
-E(--encoding=encoding)	指定用於此資料庫的字元編碼方式。
-l(--locale=locale)	指定用於此資料庫的本地化設定。
-O(--owner=owner)	指定建立資料庫的擁有者，如果未指定此選項，該值為當前登入的使用者。
-T(--template=template)	指定建立此資料庫的模板資料庫。
-h(--host=host)	指定PostgreSQL伺服器的主機名稱。
-p(--port=port)	指定伺服器的偵聽連接埠，如不指定，則為預設的5432。
-U(--username=username)	本次操作的登入使用者名稱，如果-O選項沒有指定，此資料庫的Owner將為該登入使用者。
-w(--no-password)	如果當前登入使用者沒有密碼，可以指定該選項直接登入。

2. 應用樣本：
複製代碼 代碼如下:
    #1. 以postgres的身份登入。(詳情參照上面口令檔案的內容)   
    /> psql
    #2. 建立資料表空間。
    postgres=# CREATE TABLESPACE my_tablespace LOCATION '/opt/PostgreSQL/9.1/mydata';
    CREATE TABLESPACE
    #3. 建立新資料庫的owner。
    postgres=# CREATE ROLE myuser LOGIN PASSWORD '123456';
    CREATE ROLE
    postgres=# \q
    #4. 建立新資料庫，其中本次串連的登入使用者為postgres，新資料庫的owner為myuser，資料表空間為my_tablespace，新資料庫名為mydatabase。
    /> createdb -U postgres -O myuser -D my_tablespace -e mydatabase
    CREATE DATABASE mydatabase OWNER myuser TABLESPACE my_tablespace;
    #5. 重新登入，通過查詢系統資料表查看該資料庫是否建立成功，以及資料表空間和所有者是否一致。
    /> psql
    postgres=# SELECT datname,rolname,spcname FROM pg_database db, pg_authid au, pg_tablespace ts WHERE datname = 'mydatabase' AND datdba = au.oid AND dattablespace = ts.oid;
      datname   | rolname |    spcname
    ------------+---------+---------------
     mydatabase | myuser  | my_tablespace
    (1 row)

二、dropdb：

    刪除一個現有PostgreSQL資料庫。
 複製代碼 代碼如下:
    dropdb [option...] dbname
 
    1. 命令列選項列表：

 

選項	說明
-e(--echo)	回顯dropdb產生的命令並且把它發送到伺服器。
-i(--interactive)	在做任何破壞性動作前提示。
-q(--quiet)	不顯示響應。
-h(--host=host)	指定PostgreSQL伺服器的主機名稱。
-p(--port=port)	指定伺服器的監聽連接埠，如不指定，則為預設的5432。
-U(--username=username)	本次操作的登入使用者名稱。
-w(--no-password)	如果當前登入使用者沒有密碼，可以指定該選項直接登入。

2. 應用樣本：
複製代碼 代碼如下:
    #以postgres的身份串連伺服器，刪除mydatabase資料庫。
    /> dropdb -U postgres -e mydatabase
    DROP DATABASE mydatabase;
    #通過查看系統資料表驗證該資料庫是否已經被刪除。
    /> psql
    postgres=# SELECT count(*) FROM pg_database WHERE datname = 'mydatabase';
     count
    -------
         0
    (1 row)

三、reindexdb：

    為一個指定的PostgreSQL資料庫重建索引。
 複製代碼 代碼如下:
    reindexdb [connection-option...] [--table | -t table ] [--index | -i index ] [dbname]
    reindexdb [connection-option...] [--all | -a]
    reindexdb [connection-option...] [--system | -s] [dbname]
 
    1. 命令列選項列表：

 

選項	說明
-a(-all)	重建整個資料庫的索引。
-e(--echo)	回顯reindexdb產生的命令並且把它發送到伺服器。
-i(--index=index)	僅重建指定的索引。
-q(--quiet)	不顯示響應。
-s(--system)	重建資料庫系統資料表的索引。
-t(--table=table)	僅重建指定資料表的索引。
-h(--host=host)	指定PostgreSQL伺服器的主機名稱。
-p(--port=port)	指定伺服器的監聽連接埠，如不指定，則為預設的5432。
-U(--username=username)	本次操作的登入使用者名稱。
-w(--no-password)	如果當前登入使用者沒有密碼，可以指定該選項直接登入。

 2. 應用樣本： 
 複製代碼 代碼如下:
    #僅重建資料表testtable上的全部索引。
    /> reindexdb -t testtable -e -U postgres postgres
    REINDEX TABLE testtable;
    #僅重建指定索引testtable_idx
    /> reindexdb -i testtable_idx -e -U postgres postgres
    REINDEX INDEX testtable_idx;   
    #重建指定資料庫mydatabase的全部索引。
    /> reindexdb mydatabase

四、vacuumdb：

    收集垃圾並且分析一個PostgreSQL資料庫。
 複製代碼 代碼如下:
    vacuumdb [-options] [--full | -f] [--verbose | -v] [--analyze | -z] [-t table [(column [,...])]] [dbname]
    vacuumdb [-options] [--all | -a] [--full | -f] [--verbose | -v] [--analyze | -z]
 
    1. 命令列選項列表：
 

選項	說明
-a(--all)	清理所有資料庫。
-e(--echo)	回顯vacuumdb產生的命令並且把它發送到伺服器。
-f(--full)	執行完全清理。
-q(--quiet)	不顯示響應。
-t table [(column[,...])]	僅僅清理或分析指定的資料表，欄位名只是在與--analyze選項聯合使用時才需要聲明。
-v(--verbose)	在處理過程中列印詳細資料。
-z(--analyze)	計算用於規劃器的統計值。
-h(--host=host)	指定PostgreSQL伺服器的主機名稱。
-p(--port=port)	指定伺服器的監聽連接埠，如不指定，則為預設的5432。
-U(--username=username)	本次操作的登入使用者名稱。
-w(--no-password)	如果當前登入使用者沒有密碼，可以指定該選項直接登入。

2. 應用樣本：
複製代碼 代碼如下:
    #清理整個資料庫mydatabase。 
    /> vacuumdb -e mydatabase
    VACUUM;
    #清理並分析postgres資料庫中的testtable表。
    /> vacuumdb -e --analyze --table 'testtable' postgres
    VACUUM ANALYZE testtable;
    #清理並分析postgres資料庫中的testtable表的i欄位。
    /> vacuumdb -e --analyze -t 'testtable(i)' postgres
    VACUUM ANALYZE testtable(i);
 

五、createuser:

    定義一個新的PostgreSQL使用者帳戶，需要說明的是只有超級使用者或者是帶有CREATEROLE許可權的使用者才可以執行該命令。如果希望建立的是超級使用者，那麼只能以超級使用者的身份執行該命令，換句話說，帶有CREATEROLE許可權的普通使用者無法建立超級使用者。該命令的使用方式如下：
 複製代碼 代碼如下:
    createuser [option...] [username]
 
    1. 命令列選項列表：

選項	說明
-c number	設定新建立使用者的最大串連數，預設為沒有限制。
-d(--createdb)	允許該建立使用者建立資料庫。
-D(--no-createdb)	禁止該建立使用者建立資料庫。
-e(--echo)	回顯createuser產生的命令並且把它發送到伺服器。
-E(--encrypted)	對儲存在資料庫裡的使用者口令加密。如果沒有聲明， 則使用預設值。
-i(--inherit)	新建立的角色將自動繼承它的組角色的許可權。
-I(--no-inherit)	新建立的角色不會自動繼承它的組角色的許可權。
-l(--login)	新角色將被授予登入許可權，該選項為預設選項。
-L(--no-login)	新角色沒有被授予登入許可權。
-N(--unencrypted)	不對儲存在資料庫裡的使用者口令加密。如果沒有聲明， 則使用預設值。
-P(--pwprompt)	如果給出該選項，在建立使用者時將提示設定口令。
-r(--createrole)	新角色被授予建立資料庫的許可權。
-R(--no-createrole)	新角色沒有被授予建立資料庫的許可權。
-s(--superuser)	新角色為超級使用者。
-S(--no-superuser)	新角色不是超級使用者。
-h(--host=host)	指定PostgreSQL伺服器的主機名稱。
-p(--port=port)	指定伺服器的監聽連接埠，如不指定，則為預設的5432。
-U(--username=username)	本次操作的登入使用者名稱。
-w(--no-password)	如果當前登入使用者沒有密碼，可以指定該選項直接登入。

2. 應用樣本：
複製代碼 代碼如下:
    # 對於有些沒有預設設定的選項，如-(d/D)、-(s/S)和-(r/R)，如果在命令列中沒有直接指定，那麼在執行該命令是將會給出提示資訊。
    # 需要注意的是該提示將會掛起自動化指令碼，直到輸入後命令才會繼續執行。
    /> createuser -U postgres myuser
    Shall the new role be a superuser? (y/n) n
    Shall the new role be allowed to create databases? (y/n) y
    Shall the new role be allowed to create more new roles? (y/n) n
    CREATE ROLE myuser NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;
    # 通過psql登入後查看系統檢視表，以驗證該使用者是否成功建立，以及新角色的許可權是否正確。
    /> psql
    postgres=# SELECT rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcanlogin FROM pg_roles WHERE rolname = 'myuser';
     rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin
    ---------+----------+------------+---------------+-------------+-------------
     myuser  | f           | t             | f                  | t                 | t
    (1 row)
    # 為了保證自動化指令碼不會被該命令的提示掛起，我們需要在執行該命令時指定所有沒有預設值的選項。
    /> createuser -U postgres -e -S -D -R myuser2
    CREATE ROLE myuser2 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
    # 我們可以在建立使用者時即刻指定該使用者的密碼，該操作由-P選項完成，然而這樣的用法一定會掛起自動化指令碼，
    # 因此我們可以採用一種折中的辦法，即在建立使用者時不指定密碼，在自動化指令碼執行成功後再手工該使用者的密碼。
    /> createuser -P -s -e myuser3
    Enter password for new role:
    Enter it again:
    CREATE ROLE myuser3 PASSWORD 'md5fe54c4f3129f2a766f53e4f4c9d2a698' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

六、dropuser:

    刪除一個PostgreSQL使用者帳戶，需要說明的是只有超級使用者或帶有CREATEROLE許可權的使用者可以執行該命令，如果要刪除超級使用者，只能通過超級使用者的身份執行該命令。該命令的使用方式如下：
 複製代碼 代碼如下:
    dropuser [option...] [username]
 
    1. 命令列選項列表：

 

選項	說明
-e(--echo)	回顯dropuser產生的命令並且把它發送到伺服器。
-i(--interactive)	在做任何破壞性動作前提示。
-h(--host=host)	指定PostgreSQL伺服器的主機名稱。
-p(--port=port)	指定伺服器的監聽連接埠，如不指定，則為預設的5432。
-U(--username=username)	本次操作的登入使用者名稱。

  2. 應用樣本：
  複製代碼 代碼如下:
    # 直接刪除指定使用者。
    /> dropuser -e myuser3
    DROP ROLE myuser3;
    # 在刪除指定使用者時，該命令會給出提示資訊，以免誤操作。
    /> dropuser -e -i myuser2
    Role "myuser2" will be permanently removed.
    Are you sure? (y/n) y
    DROP ROLE myuser2;