主頁(yè) > 知識(shí)庫(kù) > 15個(gè)postgresql數(shù)據(jù)庫(kù)實(shí)用命令分享

15個(gè)postgresql數(shù)據(jù)庫(kù)實(shí)用命令分享

熱門(mén)標(biāo)簽:小裙科技電銷機(jī)器人怎樣 外呼線路資源屬于電信業(yè)務(wù)嗎 河南電話外呼系統(tǒng)招商 內(nèi)蒙古營(yíng)銷智能外呼系統(tǒng)哪個(gè)好 crm外呼系統(tǒng)聯(lián)系方式 青白江400企業(yè)電話申請(qǐng) 智能外呼系統(tǒng)官網(wǎng) 呼和浩特外呼系統(tǒng)原理是什么 長(zhǎng)沙電銷外呼防封卡是什么

最初是想找postgresql數(shù)據(jù)庫(kù)占用空間命令發(fā)現(xiàn)的這篇blog,發(fā)現(xiàn)其中提供的幾
條命令很有用(但也有幾條感覺(jué)是充數(shù)的=。=),于是就把它翻譯過(guò)來(lái)了。另外這篇文章是09年的,所以里面的內(nèi)容可能有點(diǎn)過(guò)時(shí),我收集了原文中有用的評(píng)論放在了最后面。

現(xiàn)在有不少開(kāi)源軟件都在使用postgreSQL作為它們的數(shù)據(jù)庫(kù)系統(tǒng)。但公司可能不會(huì)招一些全職的postgreSQL DBA來(lái)維護(hù)它(piglei: 在國(guó)內(nèi)基本也找不到)。而會(huì)讓一些比如說(shuō)Oracle DBA、Linux系統(tǒng)管理員或者程序員去 維護(hù)。在這篇文章中我們會(huì)介紹15個(gè)無(wú)論是對(duì)psql老鳥(niǎo)還是DBA都非常實(shí)用的postgresql數(shù)據(jù)庫(kù)命令。

1. 如何找到postgreSQL數(shù)據(jù)庫(kù)中占空間最大的表?

復(fù)制代碼 代碼如下:

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \&; for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
              relname              | relpages
-----------------------------------+----------
 pg_proc                           |       50
 pg_proc_proname_args_nsp_index    |       40
 pg_depend                         |       37
 pg_attribute                      |       30

如果你只想要最大的那個(gè)表,可以用limit參數(shù)來(lái)限制結(jié)果的數(shù)量,就像這樣:

復(fù)制代碼 代碼如下:

# SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit 1;
 relname | relpages
---------+----------
 pg_proc |       50
(1 row)

1.relname - 關(guān)系名/表名
2.relpages - 關(guān)系頁(yè)數(shù)(默認(rèn)情況下一個(gè)頁(yè)大小是8kb)
3.pg_class - 系統(tǒng)表, 維護(hù)著所有relations的詳細(xì)信息
4.limit 1 - 限制返回結(jié)果只顯示一行

2. 如何計(jì)算postgreSQL數(shù)據(jù)庫(kù)所占用的硬盤(pán)大小?

pg_database_size 這個(gè)方法是專門(mén)用來(lái)查詢數(shù)據(jù)庫(kù)大小的,它返回的結(jié)果單位是字節(jié)(bytes)。:

復(fù)制代碼 代碼如下:

# SELECT pg_database_size('geekdb');
pg_database_size
------------------
         63287944
(1 row)

如果你想要讓結(jié)果更直觀一點(diǎn),那就使用**pg_size_pretty**方法,它可以把字節(jié)數(shù)轉(zhuǎn)換成更友好易讀的格式。
復(fù)制代碼 代碼如下:

# SELECT pg_size_pretty(pg_database_size('geekdb'));
 pg_size_pretty
----------------
 60 MB
(1 row)

3. 如何計(jì)算postgreSQL表所占用的硬盤(pán)大???

下面這個(gè)命令查出來(lái)的表大小是包含索引和toasted data的,如果你對(duì)除去索引外僅僅是表占的大小感興趣,可以 使用后面提供的那個(gè)命令。

復(fù)制代碼 代碼如下:

# SELECT pg_size_pretty(pg_total_relation_size('big_table'));
 pg_size_pretty
----------------
 55 MB
(1 row)

如何查詢不含索引的postgreSQL表的大?。?/p>

使用**pg_relation_size**而不是**pg_total_relation_size**方法。

復(fù)制代碼 代碼如下:

# SELECT pg_size_pretty(pg_relation_size('big_table'));
 pg_size_pretty
----------------
 38 MB
(1 row)

4. 如何查看postgreSQL表的索引?

復(fù)制代碼 代碼如下:
Syntax: # \d table_name

讓我們看下面這個(gè)例子,注意如果你的表有索引的話,你會(huì)在命令輸出內(nèi)容的后面那部分找到一個(gè)標(biāo)題 Indexes ,在這個(gè)例子中,pg_attribut表有兩個(gè)btree類型的索引,默認(rèn)情況下postgreSQL使用的索引類型都 是btree,因?yàn)樗m用于絕大多數(shù)情況。
復(fù)制代碼 代碼如下:

test=# \d pg_attribute
   Table "pg_catalog.pg_attribute"
    Column     |   Type   | Modifiers
---------------+----------+-----------
 attrelid      | oid      | not null
 attname       | name     | not null
 atttypid      | oid      | not null
 attstattarget | integer  | not null
 attlen        | smallint | not null
 attnum        | smallint | not null
 attndims      | integer  | not null
 attcacheoff   | integer  | not null
 atttypmod     | integer  | not null
 attbyval      | boolean  | not null
 attstorage    | "char"   | not null
 attalign      | "char"   | not null
 attnotnull    | boolean  | not null
 atthasdef     | boolean  | not null
 attisdropped  | boolean  | not null
 attislocal    | boolean  | not null
 attinhcount   | integer  | not null
Indexes:
    "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname)
    "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum)

5. 如何創(chuàng)建一個(gè)指定類型的索引?

默認(rèn)情況下的索引都是btree類型的,但是你可以用下面的方法來(lái)指定新索引的類型。

復(fù)制代碼 代碼如下:

Syntax: CREATE INDEX name ON table USING index_type (column);

# CREATE INDEX test_index ON numbers using hash (num);

6. 如何在postgreSQL中使用事務(wù)?

如何開(kāi)始一個(gè)事務(wù)?

復(fù)制代碼 代碼如下:

# BEGIN -- 開(kāi)始事務(wù)

如何提交或回滾一個(gè)事務(wù)?

只有當(dāng)你調(diào)用COMMIT命令后,你在BEGIN命令后所做的所有操作才會(huì)真正的被提交到postgreSQL數(shù)據(jù)庫(kù)。另外你還 可以使用ROLLBACK命令來(lái)回滾事務(wù)中做的所有操作。

復(fù)制代碼 代碼如下:

# ROLLBACK -- 回滾當(dāng)前事務(wù)
# COMMIT -- 提交當(dāng)前事務(wù)

7. 如何查看postgreSQL數(shù)據(jù)庫(kù)對(duì)某個(gè)SQL查詢的執(zhí)行計(jì)劃?

復(fù)制代碼 代碼如下:
# EXPLAIN query;


8. 如何通過(guò)在服務(wù)端執(zhí)行查詢來(lái)顯示執(zhí)行計(jì)劃?

下面這個(gè)命令會(huì)在服務(wù)器端執(zhí)行查詢,但是并不會(huì)把查詢結(jié)果給用戶,而是返回它實(shí)際的執(zhí)行計(jì)劃。
復(fù)制代碼 代碼如下:

# EXPLAIN ANALYZE query;


9. 怎么生成一個(gè)序列的數(shù)字并把它們插入到一個(gè)表中?

下面這個(gè)命令將會(huì)生成1到1000這一千個(gè)數(shù)字并插入到numbers表中。
復(fù)制代碼 代碼如下:

# INSERT INTO numbers (num) VALUES ( generate_series(1,1000));

10. 如何統(tǒng)計(jì)postgreSQL表里面的行數(shù)?

這個(gè)命令可以查詢出表里所有記錄的條數(shù)。

復(fù)制代碼 代碼如下:
# select count(*) from table;

這個(gè)命令會(huì)查詢出表中指定列的值不為空的所有行數(shù).
復(fù)制代碼 代碼如下:
# select count(col_name) from table;

這個(gè)命令會(huì)查詢出表中按制定列的值去重后的總行數(shù)。
復(fù)制代碼 代碼如下:
# select count(distinct col_name) from table;

11. 如何查詢表中某列**第二大**的值?

查詢某列最大的值

復(fù)制代碼 代碼如下:
# select max(col_name) from table;

查詢某列中第二大的值
復(fù)制代碼 代碼如下:

# SELECT MAX(num) from number_table where num  ( select MAX(num) from number_table );

12. 如何查詢表中某列**第二小**的值?

查詢某列最小的值

復(fù)制代碼 代碼如下:
# select min(col_name) from table;

查詢某列第二小的值
復(fù)制代碼 代碼如下:

# SELECT MIN(num) from number_table where num > ( select MIN(num) from number_table );

13. 如何列出postgreSQL數(shù)據(jù)庫(kù)中基本的數(shù)據(jù)類型?

下面截取了部分內(nèi)容,這個(gè)命令可以展示可用的數(shù)據(jù)類型和它們所占用的字節(jié)數(shù)。

復(fù)制代碼 代碼如下:

test=# SELECT typname,typlen from pg_type where typtype='b';
    typname     | typlen
----------------+--------
 bool           |      1
 bytea          |     -1
 char           |      1
 name           |     64
 int8           |      8
 int2           |      2
 int2vector     |     -1

1.typname - 類型的名稱
2.typlen - 類型的大小

14. 如何把某一次查詢的結(jié)果保存為一個(gè)文件?

復(fù)制代碼 代碼如下:
# \o output_file
# SELECT * FROM pg_class;

上面這個(gè)查詢的結(jié)果將會(huì)被保存到到"output_file"文件中。當(dāng)重定向被激活后,之后的所有查詢都不再會(huì)把結(jié)果 打印在屏幕上了。如果要再次打開(kāi)屏幕輸出,需要再執(zhí)行一次不帶任何參數(shù)的 o 命令。
復(fù)制代碼 代碼如下:

# \o

15. 存儲(chǔ)加密后的密碼

PostgreSQL數(shù)據(jù)庫(kù)可以使用下面的crypt命令來(lái)加密數(shù)據(jù)。這可以用來(lái)方便的用來(lái)保存你的用戶名和密碼。

復(fù)制代碼 代碼如下:

# SELECT crypt ( 'sathiya', gen_salt('md5') );

PostgreSQL crypt方法可能存在的問(wèn)題:

crypt在你的環(huán)境下可能會(huì)用不了,并提供下面這個(gè)報(bào)錯(cuò)信息。

復(fù)制代碼 代碼如下:

ERROR:  function gen_salt("unknown") does not exist
HINT:  No function matches the given name and argument types.
         You may need to add explicit type casts.

解決方法:

為了解決這個(gè)問(wèn)題,你需要安裝 postgresql-contrib-版本 這個(gè)包,然后在psql中執(zhí)行下面這個(gè)命令。

復(fù)制代碼 代碼如下:

# \i /usr/share/postgresql/8.1/contrib/pgcrypto.sql

原文后的評(píng)論

在第13個(gè)命令中,那個(gè)typtype='b'是什么意思?

typtype='b'表示basetype。b==basetype.

PostgreSQL有這么幾種數(shù)據(jù)類型: composite types, domains, and pseudo-types.

http://developer.postgresql.org/pgdocs/postgres/extend-type-system.html

獲取第二大/小的值效率問(wèn)題

如果要查詢一個(gè)表中某列第二小的值,這樣查詢要快得多:

復(fù)制代碼 代碼如下:
SELECT m FROM mytable ORDER BY m LIMIT 1 OFFSET 1;

如果m列有索引的話。

COUNT(*)效率問(wèn)題

在大表上執(zhí)行count(*)會(huì)有比較明顯的效率問(wèn)題

您可能感興趣的文章:
  • PostgreSQL教程(十八):客戶端命令(2)
  • PostgreSQL教程(十七):客戶端命令(1)
  • PostgreSql新手必學(xué)入門(mén)命令小結(jié)
  • PostgreSQL copy 命令教程詳解

標(biāo)簽:黃石 舟山 楚雄 安順 白山 菏澤 呼倫貝爾 池州

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《15個(gè)postgresql數(shù)據(jù)庫(kù)實(shí)用命令分享》,本文關(guān)鍵詞  15個(gè),postgresql,數(shù)據(jù)庫(kù),實(shí)用,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《15個(gè)postgresql數(shù)據(jù)庫(kù)實(shí)用命令分享》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于15個(gè)postgresql數(shù)據(jù)庫(kù)實(shí)用命令分享的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章