postgresql常用命令

標籤：

1.createdb 資料庫名稱
  產生資料庫
2.dropdb  資料庫名稱
  刪除資料庫

3.CREATE USER 使用者名稱稱
  建立使用者
4.drop User 使用者名稱稱
  刪除使用者

5.SELECT usename FROM pg_user;
  查看系統使用者資訊
  \du

7.SELECT version();
  查看版本資訊

8.psql 資料庫名
  開啟psql互動工具

9.mydb=> \i basics.sql
\i 命令從指定的檔案中讀取命令。

10.COPY weather FROM ‘/home/user/weather.txt‘;
   批量將文字檔中內容匯入到wether表

11.SHOW search_path;
   顯示搜尋路徑

12.建立使用者
   CREATE USER 使用者名稱 WITH PASSWORD ‘密碼‘

13.建立模式
   CREATE SCHEMA myschema;

14.刪除模式
   DROP SCHEMA myschema;

15.查看搜尋模式
   SHOW search_path;

16.設定搜尋模式
   SET search_path TO myschema,public;

17.建立資料表空間
  create tablespace 資料表空間名稱 location ‘檔案路徑‘;

18.顯示預設資料表空間
  show default_tablespace;

19.設定預設資料表空間
  set default_tablespace=資料表空間名稱;

20.指定使用者登入
    psql MTPS  -u

21.顯示當前系統時間、
   now()

22.配置plpgsql語言
CREATE LANGUAGE ‘plpgsql‘ HANDLER plpgsql_call_handler

23.刪除規則

DROP RULE name ON relation [ CASCADE | RESTRICT ]
 
輸入
name
要刪除的現存的規則．

relation
該規則應用的關係名字(可以有大綱修飾)．

CASCADE
自動刪除依賴於此規則的對象。

RESTRICT
如果有任何依賴對象，則拒絕刪除此規則。這個是預設。

24.日期格式函數
  select ‘P‘||to_char(current_date,‘YYYYMMDD‘)||‘01‘

25.產生組
  Create Group 組名稱

26.修改使用者歸屬組
  Alter Group 組名稱 add user 使用者名稱稱

26.為組賦值許可權
 
  grant 操作 On 表名稱  to group 組名稱：

27.建立角色
  Create Role 角色名稱

28.刪除角色
  Drop Role 角色名稱

29.獲得當前postgresql版本
  SELECT version();

30.在linux中執行計畫任務
   通過crontab執行
   su root -c "psql -p 5433 -U developer MTPS -c‘select test()‘"
   developer使用者的密碼儲存於環境變數PGPASSWORD中。

31.查詢表是否存在
  select * from pg_statio_user_tables where relname=‘你的表名‘;

32.為使用者複製SCHEMA許可權
  grant all on SCHEMA 範圍名稱 to 使用者名稱稱

33.整個資料庫匯出
  pg_dumpall -D -p 連接埠號碼 -h 伺服器IP -U postgres(使用者名稱) > /home/xiaop/all.bak

34.Database Backup恢複
  psql -h 192.168.0.48 -p 5433  -U postgres

35.當前日期函數
  current_date

36.返回第十條開始的5條記錄
select   *   from   tabname   limit   5   offset   10;

37.為使用者賦模式許可權
Grant   on schema developer  to UDataHouse
  
38.將字元轉換為日期時間
  select  to_timestamp(‘2010-10-21 12:31:22‘, ‘YYYY-MM-DD hh24:mi:ss‘)

39.Database Backup
  pg_dumpall   -h 192.168.0.4 -p 5433 -U postgres   >/DataBack/Postgresql2010012201.dmp
  如8.1以後多次輸入密碼

40.\dn
   查看schema

41.刪除schema
  drop schema _clustertest cascade;

42.匯出表

  ./pg_dump -p 連接埠號碼 -U 使用者 -t 表名稱 -f 備份檔案位置 資料庫  ;

43.字串操作函數
select distinct(split_part(ip,‘.‘,1)||‘.‘||split_part(ip,‘.‘,2)) from t_t_userip order by (split_part(ip,‘.‘,1)||‘.‘||split_part(ip,‘.‘,2));

44.刪除表主鍵
alter table 表名 drop CONSTRAINT 主鍵名稱;

45.建立資料表空間
create tablespace 空間名稱 location ‘路徑‘

46.查看錶結構
select * from information_schema.columns 

 

    ./postgres -D /usr/local/src/data
or
    ./pg_ctl -D /usr/local/src/data -l logfile start

47.查看資料庫大小
SELECT pg_size_pretty(pg_database_size(‘MTPS‘)) As fulldbsize;

48.查看資料庫表大小
SELECT pg_size_pretty(pg_total_relation_size(‘developer.t_L_collectfile‘)) As fulltblsize,
pg_size_pretty(pg_relation_size(‘developer.t_L_collectfile‘)) As justthetblsize

49.設定執行超過指定秒數的sql語句輸出到日誌
log_min_duration_statement = 3

50.超過一定秒數sql自動執行執行計畫
shared_preload_libraries = ‘auto_explain‘
custom_variable_classes = ‘auto_explain‘
auto_explain.log_min_duration = 4s

51.Database Backup
 select pg_start_backup(‘backup baseline‘);
 select pg_stop_backup();

 recovery.conf
 restore_command=‘cp /opt/buxlog/%f %p‘

52.重建索引
 REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ]
 INDEX
 重建立立聲明了的索引。

 TABLE
 重建立立聲明的表的所有索引。如果表有個從屬的"TOAST"表，那麼這個表也會重新索引。

 DATABASE
 重建當前資料庫裡的所有索引。 除非在獨立運行模式下，會忽略在共用系統資料表上的索引（見下文）。

 SYSTEM
 在當前資料庫上重建所有系統資料表上的索引。不會處理在使用者表上的索引。 另外，除了是在單主機模式下，共用的系統資料表也會被忽略（見下文）。

 name
 需要重建索引的索引，表或者資料庫的名稱。 表和索引名可以有模式修飾。 目前，REINDEX DATABASE 和 REINDEX SYSTEM 只能重建當前資料庫的索引， 因此其參數必須匹配當前資料庫的名字。

 FORCE
 這是一個廢棄的選項，如果聲明，會被忽略。

54.資料字典查看錶結構
SELECT column_name, data_type from information_schema.columns where table_name = ‘blog_sina_content_train‘;

 

52.查看被鎖定表
SELECT pg_class.relname AS table, pg_database.datname AS database,  pid, mode, granted
FROM pg_locks, pg_class, pg_database
WHERE pg_locks.relation = pg_class.oid
AND pg_locks.database = pg_database.oid;

53.查看用戶端串連情況
SELECT client_addr ,client_port,waiting,query_start,current_query FROM pg_stat_activity;

54.常看資料庫.conf配置
show all

55.修改資料庫postgresql.conf參數
修改postgresql.conf內容
pg_ctl reload

56.復原日誌強制恢複
pg_resetxlog -f 資料庫檔案路徑

 idvalue  | remark
----------+--------
 33953557 | inser

57.當前日期屬於一年中第幾周
select EXTRACT(week from TIMESTAMP ‘2010-10-22‘);

58.顯示最近執行命令
\s

I. SQL 命令
ABORT — 退出當前事務
ALTER AGGREGATE — 修改一個聚集合函式的定義
ALTER CONVERSION — 修改一個編碼轉換的定義
ALTER DATABASE — 修改一個資料庫
ALTER DOMAIN — 改變一個域的定義
ALTER FUNCTION — 修改一個函數的定義
ALTER GROUP — 修改一個使用者組
ALTER INDEX — 改變一個索引的定義
ALTER LANGUAGE — 修改一個過程語言的定義
ALTER OPERATOR — 改變一個操作符的定義
ALTER OPERATOR CLASS — 修改一個操作符表的定義
ALTER ROLE — 修改一個資料庫角色
ALTER SCHEMA — 修改一個模式的定義
ALTER SEQUENCE — 更改一個序列產生器的定義
ALTER TABLE — 修改表的定義
ALTER TABLESPACE — 改變一個資料表空間的定義
ALTER TRIGGER — 改變一個觸發器的定義
ALTER TYPE — 改變一個類型的定義
ALTER USER — 改變資料庫使用者帳號
ANALYZE — 收集與資料庫有關的統計
BEGIN — 開始一個事務塊
CHECKPOINT — 強制一個交易記錄檢查點
CLOSE — 關閉一個遊標
CLUSTER — 根據一個索引對某個表集簇
COMMENT — 定義或者改變一個對象的評註
COMMIT — 提交當前事務
COMMIT PREPARED — 提交一個早先為兩階段交易認可準備好的事務
COPY — 在表和檔案之間拷貝資料
CREATE AGGREGATE — 定義一個新的聚集合函式
CREATE CAST — 定義一個使用者定義的轉換
CREATE CONSTRAINT TRIGGER — 定義一個新的約束觸發器
CREATE CONVERSION — 定義一個新的的編碼轉換
CREATE DATABASE — 建立新資料庫
CREATE DOMAIN — 定義一個新域
CREATE FUNCTION — 定義一個新函數
CREATE GROUP — 定義一個新的使用者組
CREATE INDEX — 定義一個新索引
CREATE LANGUAGE — 定義一種新的過程語言
CREATE OPERATOR — 定義一個新的操作符
CREATE OPERATOR CLASS — 定義一個新的操作符表
CREATE ROLE — define a new database role
CREATE RULE — 定義一個新的重寫規則
CREATE SCHEMA — 定義一個新的模式
CREATE SEQUENCE — 建立一個新的序列發生器
CREATE TABLE — 定義一個新表
CREATE TABLE AS — 從一條查詢的結果中定義一個新表
CREATE TABLESPACE — 定義一個新的資料表空間
CREATE TRIGGER — 定義一個新的觸發器
CREATE TYPE — 定義一個新的資料類型
CREATE USER — 建立一個新的資料庫使用者帳戶
CREATE VIEW — 定義一個視圖
DEALLOCATE — 刪除一個準備好的查詢
DECLARE — 定義一個遊標
DELETE — 刪除一個表中的行
DROP AGGREGATE — 刪除一個使用者定義的聚集合函式
DROP CAST — 刪除一個使用者定義的類型轉換
DROP CONVERSION — 刪除一個使用者定義的編碼轉換
DROP DATABASE — 刪除一個資料庫
DROP DOMAIN — 刪除一個使用者定義的域
DROP FUNCTION — 刪除一個函數
DROP GROUP — 刪除一個使用者組
DROP INDEX — 刪除一個索引
DROP LANGUAGE — 刪除一個過程語言
DROP OPERATOR — 刪除一個操作符
DROP OPERATOR CLASS — 刪除一個操作符表
DROP ROLE — 刪除一個資料庫角色
DROP RULE — 刪除一個重寫規則
DROP SCHEMA — 刪除一個模式
DROP SEQUENCE — 刪除一個序列
DROP TABLE — 刪除一個表
DROP TABLESPACE — 刪除一個資料表空間
DROP TRIGGER — 刪除一個觸發器定義
DROP TYPE — 刪除一個使用者定義資料類型
DROP USER — 刪除一個資料庫使用者帳號
DROP VIEW — 刪除一個視圖
END — 提交當前的事務
EXECUTE — 執行一個準備好的查詢
EXPLAIN — 顯示語句執行規劃
FETCH — 用遊標從查詢中抓取行
GRANT — 定義存取權限
INSERT — 在表中建立新行
LISTEN — 監聽一個通知
LOAD — 裝載或重載一個共用庫檔案
LOCK — 明確地鎖定一個表
MOVE — 重定位一個遊標
NOTIFY — 產生一個通知
PREPARE — 建立一個準備好的查詢
PREPARE TRANSACTION — 為當前事務做兩階段交易認可的準備
REINDEX — 重建索引
RELEASE SAVEPOINT — 刪除一個前面定義的儲存點
RESET — 把一個運行時參數值恢複為預設值
REVOKE — 刪除存取權限
ROLLBACK — 退出當前事務
ROLLBACK PREPARED — 取消一個早先為兩階段交易認可準備好的事務
ROLLBACK TO — 復原到一個儲存點
SAVEPOINT — 在當前事務裡定義一個新的儲存點
SELECT — 從表或視圖中取出若干行
SELECT INTO — 從一個查詢的結果中定義一個新表
SET — 改變運行時參數
SET CONSTRAINTS — 設定當前事務的約束檢查模式
SET ROLE — set the current user identifier of the current session
SET SESSION AUTHORIZATION — 為當前會話設定會話使用者識別碼和目前使用者標識符
SET TRANSACTION — 設定當前事務的特性
SHOW — 顯示運行時參數的數值
START TRANSACTION — 開始一個事務塊
TRUNCATE — 清空一個或者一堆表
UNLISTEN — 停止監聽通知資訊
UPDATE — 更新一個表中的行
VACUUM — 垃圾收集以及可選地分析一個資料庫
II. 用戶端應用
clusterdb — 對一個PostgreSQL資料庫進行建簇
createdb — 建立一個新的 PostgreSQL 資料庫
createlang — 定義一種新的 PostgreSQL 過程語言
createuser — 定義一個新的 PostgreSQL 使用者帳戶
dropdb — 刪除一個現有 PostgreSQL 資料庫
droplang — 刪除一種 PostgreSQL 過程語言
dropuser — 刪除一個 PostgreSQL 使用者帳戶
ecpg — 嵌入的 SQL C 前置處理器
pg_config — 檢索已安裝版本的 PostgreSQL 的資訊
pg_dump — 將一個PostgreSQL資料庫抽出到一個指令檔或者其它歸檔檔案中
pg_dumpall — 抽出一個 PostgreSQL 資料庫叢集到指令檔中
pg_restore — 從一個由 pg_dump 建立的備份檔案中恢複 PostgreSQL 資料庫。
psql — PostgreSQL 互動終端
vacuumdb — 收集垃圾並且分析一個PostgreSQL 資料庫
III. PostgreSQL 伺服器應用
initdb — 建立一個新的 PostgreSQL資料庫叢集
ipcclean — 從失效的PostgreSQL伺服器中刪除共用記憶體和號誌
pg_controldata — 顯示一個 PostgreSQL 叢集的控制資訊
pg_ctl — 啟動，停止和重起 PostgreSQL
pg_resetxlog — 重設一個 PostgreSQL 資料庫叢集的預寫記錄檔以及其它控制內容
postgres — 以單一使用者模式運行一個 PostgreSQL伺服器
postmaster — PostgreSQL多使用者資料庫伺服器

59.匯出資料庫角色

 /data/pgsql/bin/pg_dumpall -p 5432 -U postgres -r >/tmp/postgres_8.3_role.bak

60.修改sequence所有者

 grant all on sequence名稱 to 所有者;

61.修改sequence初始值

Alter SEQUENCE  sequencename START  value;

62.查看sequence當前值

SELECT currval(‘sequencename‘);

63.查看sequence下一值

SELECT nextval(‘sequencename‘);

64.設定sequence當前值

 alter SEQUENCE  sequencename  restart with  startvalue;

SELECT nextval(‘sequencename‘);

65.查詢表結構

 

SELECT a.attnum,a.attname AS field,t.typname AS type,a.attlen AS length,a

.atttypmod AS lengthvar,a.attnotnull AS notnull

FROM pg_class c,pg_attribute a,pg_type t

WHERE c.relname=表名稱and  a.attnum > 0  and  a.attrelid = c.oid and a

.atttypid = t.oid

66.將查詢結果直接輸出到檔案

在psql中

\o 檔案路徑

select datname,rolname from pg_database  a left outer join pg_roles b on a.datdba=b.oid ;

\o

 

67.查詢資料庫所有則

select datname,rolname from pg_database  a left outer join pg_roles b on a.datdba=b.oid ;

68.結束正在執行的事務

SELECT * from  pg_stat_activity;

select pg_cancel_backend(‘procpid‘);

 

60.結束session

SELECT * from  pg_stat_activity;

select  pg_terminate_backend(‘procpid‘);

61.postgresql取消逸出字元功能

 將postgresql.conf檔案中的standard_conforming_strings設定為on

62.查詢正在執行SQL

SELECT 
    procpid, 
    start, 
    now() - start AS lap, 
    current_query 
FROM 
    (SELECT 
        backendid, 
        pg_stat_get_backend_pid(S.backendid) AS procpid, 
        pg_stat_get_backend_activity_start(S.backendid) AS start, 
       pg_stat_get_backend_activity(S.backendid) AS current_query 
    FROM 
        (SELECT pg_stat_get_backend_idset() AS backendid) AS S 
    ) AS S 
WHERE 
   current_query <> ‘‘ 
ORDER BY 
   lap DESC;

postgresql常用命令