PostgreSQL 常用腳本

来源:https://www.cnblogs.com/nehcdahc/archive/2020/01/10/12176502.html

數據定義 資料庫 表 擴展 函數 索引 許可權控制 運行分析 運行維護 配置 備份還原 其他 時間處理 psql ...


數據定義

資料庫


-- 創建資料庫
-- database_name,資料庫名稱
-- database_user,用戶名
CREATE DATABASE database_name WITH OWNER = database_user;
CREATE DATABASE database_name OWNER database_user;

-- 複製資料庫
-- database_name,資料庫名稱
-- database_user,用戶名
-- original_database_name,原始資料庫名稱
CREATE DATABASE database_name WITH TEMPLATE original_database_name OWNER database_user;


-- 新增列
-- table_name,表名
-- column_name,列名
ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name VARCHAR(100) NULL;

擴展


-- 創建 UUID 擴展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- 驗證 UUID 擴展
SELECT uuid_generate_v4();

-- 創建 cube 擴展
CREATE EXTENSION IF NOT EXISTS cube;

-- 創建 earthdistance 擴展
CREATE EXTENSION IF NOT EXISTS earthdistance;

函數


-- 隱式將整形轉換成字元串,但是會有一些問題,參考 https://stackoverflow.com/questions/50025750/postgres-convert-integer-into-text。通常情況下還是建議使用 CAST 函數來實現。
-- 使用場景:在資料庫遷移的時候(比如 Microsoft SQL Server 轉成 PostgreSQL,Microsoft SQL Server 預設是支持的)需要隱式轉換,以達到快速實現的目的
CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(integer) IS 'convert integer to text';
CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
COMMENT ON FUNCTION pg_catalog.text(bigint) IS 'convert bigint to text';

索引


-- Query the indexes of a table
SELECT * FROM pg_indexes WHERE tablename IN ('table_name');

-- 查詢所有索引
SELECT
    i.relname AS indname ,
    i.relowner AS indowner ,
    idx.indrelid::REGCLASS ,
    am.amname AS indam ,
    idx.indkey ,
    ARRAY(
    SELECT
        pg_get_indexdef(idx.indexrelid,
        k + 1,
        TRUE)
    FROM
        GENERATE_SUBSCRIPTS(idx.indkey, 1) AS k
    ORDER BY
        k) AS indkey_names ,
    idx.indexprs IS NOT NULL AS indexprs ,
    idx.indpred IS NOT NULL AS indpred
FROM
    pg_index AS idx
JOIN pg_class AS i ON
    i.oid = idx.indexrelid
JOIN pg_am AS am ON
    i.relam = am.oid
JOIN pg_namespace AS ns ON
    ns.oid = i.relnamespace
    AND ns.nspname = ANY (CURRENT_SCHEMAS(FALSE));

-- 查詢所有索引,排除系統表
SELECT
    U.usename AS user_name,
    ns.nspname AS schema_name,
    idx.indrelid :: REGCLASS AS table_name,
    i.relname AS index_name,
    idx.indisunique AS is_unique,
    idx.indisprimary AS is_primary,
    am.amname AS index_type,
    idx.indkey,
    ARRAY(
    SELECT
        pg_get_indexdef(idx.indexrelid,
        k + 1,
        TRUE)
    FROM
        GENERATE_SUBSCRIPTS(idx.indkey, 1) AS k
    ORDER BY
        k ) AS index_keys,
    (idx.indexprs IS NOT NULL)
    OR (idx.indkey::INT[] @> ARRAY[0]) AS is_functional,
    idx.indpred IS NOT NULL AS is_partial
FROM
    pg_index AS idx
JOIN pg_class AS i ON
    i.oid = idx.indexrelid
JOIN pg_am AS am ON
    i.relam = am.oid
JOIN pg_namespace AS NS ON
    i.relnamespace = NS.OID
JOIN pg_user AS U ON
    i.relowner = U.usesysid
WHERE
    NOT nspname LIKE 'pg%';

許可權控制


-- CREATE USER OR ROLE,PostgreSQL 中創建用戶和角色是等效的
-- role_name,用戶角色名稱
-- user_password,用戶密碼
-- user_name,用戶角色名稱
CREATE ROLE role_name WITH CREATEDB CREATEROLE LOGIN PASSWORD 'user_password';
CREATE user user_name PASSWORD 'user_password';

-- 分配所有許可權
-- database_name,資料庫名稱
-- database_user,資料庫用戶
GRANT ALL PRIVILEGES ON database_name TO database_user;

-- 修改表的 Owner
ALTER TABLE table_name OWNER TO database_user;

-- 分配 FUNCTION 的許可權給指定用戶
-- function_name,函數名稱
-- parameter1_type,第一個函數參數類型
-- parameter2_type,第二個函數參數類型
-- database_user,資料庫用戶
GRANT EXECUTE ON FUNCTION function_name(parameter1_type, parameter2_type, ...) TO database_user;

-- 修改 FUNCTION 的 Owner
-- function_name,函數名稱
-- parameter1_type,第一個函數參數類型
-- parameter2_type,第二個函數參數類型
-- database_user,資料庫用戶
ALTER FUNCTION function_name(parameter1_type, parameter2_type, ...) OWNER TO database_user;

運行分析


-- 查詢當前資料庫 TOP 20 大表
SELECT table_name
    ,pg_size_pretty(pg_relation_size(table_schema || '.' || table_name)) AS size
FROM information_schema.tables
ORDER BY pg_relation_size(table_schema || '.' || table_name) DESC LIMIT 20;

-- 查詢單個表大小
SELECT pg_size_pretty(pg_relation_size(table_name));

-- 查詢資料庫活動的查詢
SELECT current_timestamp - query_start AS runtime
    ,query_start
    ,datname
    ,pid
    ,query
FROM pg_stat_activity
WHERE query_start IS NOT NULL
ORDER BY 1 DESC limit 20;

運行維護


-- Cancel Processes by pid
SELECT pg_cancel_backend(pid int);

-- Terminate Processes by pid
SELECT pg_terminate_backend(pid int);

-- Kill all existing connections in the original database
-- source_db,資料庫名稱
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'source_db'
AND pid <> pg_backend_pid();

-- garbage-collect and optionally analyze a database
-- table_name,資料庫表名
VACUUM table_name;
VACUUM FULL table_name;

配置


-- 修改 max_locks_per_transaction
ALTER SYSTEM SET max_locks_per_transaction = 300;

-- 重載配置信息,使配置生效
-- pg_hba.conf
SELECT pg_reload_conf();

備份還原


pg_dump -h host_name -U database_user -F c -b -v -f file_path database_name

pg_restore -h host_name -U database_user --no-owner -d database_name file_path

其他


-- Prepare a statement for execution
PREPARE foo(TEXT, TEXT, TEXT) AS
SELECT *
FROM foobar
WHERE foo = $1
    AND bar = $2
    OR baz = $3
EXECUTE foo('foo', 'bar', 'baz');
DEALLOCATE foo;

時間處理


-- 查詢時間差
SELECT EXTRACT(epoch FROM (begin_time - end_time));

-- Query the last month in format 'YYYYMM'
SELECT to_char(date_trunc('month', current_date - interval '1' month), 'YYYYMM');

psql


# 打開資料庫連接
psql -h host_name -U database_user

# 列出所有的資料庫
\l

# 連接數據
\c database_name

您的分享是我們最大的動力!

更多相關文章
  • 路由器和交換機里都有操作系統,cisco的操作系統叫ios。 怪不得路由器貴,原來也是帶操作系統的機器啊,以前真是小白。 ios里的幾種模式 1, :用戶模式:啥也幹不了。 2, :特權模式:用於檢驗在全局模式里的配置是否生效了。下麵的R1是路由器的名字(是在GNS3里設置的)。 從用戶模式進入特權 ...
  • 學了一些linux下的網路相關的命令,但是網路本身是啥,不知道啊, 所以只好找個ccna的網路知識,學一學,太難了我 網路種類: 乙太網 ARPA FR幀中繼 frame relay ATM交換機 網路範圍: 區域網 廣域網(也就俗稱的網際網路) 網路定義:通過物理線路將所有的終端設備連接到一起,並實 ...
  • linux學習 看完了基礎篇,下麵來看進階篇 我好想哭看這的時候,好多只是聽說過,但完全沒有試過,感覺自己懂得有點少,就是缺乏一些知識儲備,也就是必須知道了某些或學過了某些知識才適合來看這一部分,看得太早了,不過看看也好,以後再見到就不陌生了。這篇主要就是在linux編寫程式,調試程式,優化性能,這 ...
  • OJ 全名 online judge 線上判題系統,對於從事編程競賽的人來說一點都不陌生,今天我們討論的是怎麼樣自定義搭建 推薦的開源的OJ有hustOJ,JNOJ 因為hustOJ 是一鍵安裝腳本,對於安裝前的要求比較高,所以這一次我們使用jnoj 源代碼和自定義的安裝過程都在 倉庫地址 配置LA ...
  • MRAM是一種以電阻為存儲方式結合非易失性及隨機訪問兩種特性,可以兼做記憶體和硬碟的新型存儲介質。寫入速度可達NAND快閃記憶體的數千倍,此外,其製作工藝要求低,良品率高,可以很好的控製成本。在壽命方面,由於MRAM特殊的存儲方式,產品的壽命耐久性也遠超傳統RAM。大規模普及仍面臨挑戰 毫無疑問,MRAM因 ...
  • 學習使用linux 偶然間看到一篇介紹linux的使用,於是看了看,整體看完,雖然看的有些懵✒,但還是堅持看完了基礎部分,並做了一些摘要。 man頁面所屬的分類標識 常用的是分類1和分類3 (1)、用戶可以操作的命令或者是可執行文件 (2)、系統核心可調用的函數與工具等 (3)、一些常用的函數與數據 ...
  • Linux的預設管理員名即是root,只需要知道ROOT密碼即可直接登錄SSH。禁止Root從SSH直接登錄可以提高伺服器安全性。經過以下操作後即可實現。本文適用於CentOS、Debian等Linux系統。 新建用戶 配置密碼 配置不允許root用戶直接登錄 修改相關文件 禁止root登陸 重啟s ...
  • 前言:學習的課程來自極客時間的專欄《趣談 Linux 操作系統》,作者用形象化的比喻和豐富的圖片讓課程變得比較易懂,為了避免知識看過就忘,打算通過寫學習筆記的形式記錄自己的學習過程。Linux 系統的相關介紹不再贅述,目前比較熱門的技術,比如雲計算、虛擬化、容器、大數據、人工智慧,幾乎都是基於 Li ...
一周排行
  • 比如要拆分“呵呵呵90909086676喝喝999”,下麵當type=0返回的是中文字元串“呵呵呵,喝喝”,type=1返回的是數字字元串“90909086676,999”, private string GetStrings(string str,int type=0) { IList<strin ...
  • Swagger一個優秀的Api介面文檔生成工具。Swagger可以可以動態生成Api介面文檔,有效的降低前後端人員關於Api介面的溝通成本,促進項目高效開發。 1、使用NuGet安裝最新的包:Swashbuckle.AspNetCore。 2、編輯項目文件(NetCoreTemplate.Web.c ...
  • 2020 年 7 月 30 日, 由.NET基金會和微軟 將舉辦一個線上和為期一天的活動,包括 微軟 .NET 團隊的演講者以及社區的演講者。本次線上大會 專註.NET框架構建微服務,演講者分享構建和部署雲原生應用程式的最佳實踐、模式、提示和技巧。有關更多信息和隨時瞭解情況:https://focu... ...
  • #abp框架Excel導出——基於vue #1.技術棧 ##1.1 前端採用vue,官方提供 UI套件用的是iview ##1.2 後臺是abp——aspnetboilerplate 即abp v1,https://github.com/aspnetboilerplate/aspnetboilerp ...
  • 前言 本文的文字及圖片來源於網路,僅供學習、交流使用,不具有任何商業用途,版權歸原作者所有,如有問題請及時聯繫我們以作處理。 作者:碧茂大數據 PS:如有需要Python學習資料的小伙伴可以加下方的群去找免費管理員領取 input()輸入 Python提供了 input() 內置函數從標準輸入讀入一 ...
  • 從12年到20年,python以肉眼可見的趨勢超過了java,成為了當今It界人人皆知的編程語言。 python為什麼這麼火? 網路編程語言搜索指數 適合初學者 Python具有語法簡單、語句清晰的特點,這就讓初學者在學習階段可以把精力集中在編程對象和思維方法上。 大佬都在用 Google,YouT ...
  • 在社會上存在一種普遍的對培訓機構的學生一種歧視的現象,具體表現在,比如:當你去公司面試的時候,一旦你說了你是培訓機構出來的,那麼基本上你就涼了,那麼你瞞著不說,然後又通過了面試成功入職,但是以後一旦在公司被髮現有培訓經歷,可能會面臨被降薪,甚至被辭退,培訓機構出來的學生,在用人單位眼裡就是能力低下的 ...
  • from typing import List# 這道題看了大佬寫的代碼,經過自己的理解寫出來了。# 從最外圍的四周找有沒有為O的,如果有的話就進入深搜函數,然後深搜遍歷# 判斷上下左右的位置是否為Oclass Solution: def solve(self, board: List[List[s ...
  • import requests; import re; import os; # 1.請求網頁 header = { "user-agent":'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537.36 (KHTML, li ...
  • import requests; import re; import os; import parsel; 1.請求網頁 header = { "user-agent":'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_5) AppleWebKit/537. ...