PostgreSQL實時查看數據庫實例正在執(zhí)行的SQL語句實例詳解
更新時間：2023年01月13日 10:02:23 作者：技術很渣
在任何數據庫中,分析和優(yōu)化SQL的執(zhí)行,最重要的工作就是執(zhí)行計劃的解讀,而說到執(zhí)行計劃得先了解postgresql的查詢執(zhí)行過程,下面這篇文章主要給大家介紹了關于PostgreSQL實時查看數據庫實例正在執(zhí)行的SQL語句的相關資料,需要的朋友可以參考下
一、查詢當前正在執(zhí)行所有SQL語句
SELECT pid, datname, usename, client_addr, application_name, STATE, backend_start, xact_start, xact_stay, query_start, query_stay, REPLACE ( query, chr( 10 ), ' ' ) AS query FROM ( SELECT pgsa.pid AS pid, pgsa.datname AS datname, pgsa.usename AS usename, pgsa.client_addr client_addr, pgsa.application_name AS application_name, pgsa.STATE AS STATE, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay, pgsa.query_start AS query_start, EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay, pgsa.query AS query FROM pg_stat_activity AS pgsa WHERE pgsa.STATE != 'idle' AND pgsa.STATE != 'idle in transaction' AND pgsa.STATE != 'idle in transaction (aborted)' ) idleconnections ORDER BY query_stay DESC
二、判斷是否存在慢查詢語句
| 字段 | 解釋 |
|---|---|
| PID | 數據庫查詢進程ID |
| query_stay | 查詢時長秒 |
| query | 查詢SQL語句 |
三、按查詢進程，殺掉慢查詢釋放資源
SELECT pg_terminate_backend(PID); SELECT pg_terminate_backend(6289);
四、擴展query的顯示長度能展示出全部語句
vi /var/lib/pgsql/12/data/postgresql.conf
修改track_activity_query_size的值重啟數據庫服務
五、殺掉某個數據庫的所有數據庫連接
SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activity WHERE datname='數據庫名稱' AND pid<>pg_backend_pid();
六、導出數據庫某個表
pg_dump -t 某個表名 "host=192.168.16.31 port=18921 user=postgres password=數據庫密碼 dbname=數據庫" -f /app/zjfbeifen/1.sql
七、shell腳本自動導入按表
touch insert_sql.sh
#!/bin/bash . /etc/profile . ~/.bash_profile export PATH=/usr/pgsql-12/bin:/usr/bin; export PGPASSWORD=數據庫密碼;psql -U postgres -d 數據庫名稱 < /mnt/nas/bak-06/1.sql;
指定postgres用戶執(zhí)行導入某個表的語句，免密執(zhí)行shell腳本
touch nasen_insert.sql
#!/bin/sh source /etc/profile PATH=/usr/local/bin:$PATH echo 'task_start' runuser - postgres -g postgres -c "sh /data/test/insert_sql.sh" echo 'task_finish'
定時器配置
00 10 * * * /usr/bin/sh /data/test/insert_nasen.sh >>/data/test/insert_nasen.log
八、shell腳本執(zhí)行postgre的sql語句
touch drop01.sql
#刪除某個表SQL語句
DROP TABLE IF EXISTS public.test;
touch drop.sh
#!/bin/bash . /etc/profile . ~/.bash_profile export PATH=/usr/pgsql-12/bin:/usr/bin; #su postgres export PGPASSWORD=123456;psql -U postgres -d 數據庫名稱 < /data/test/drop01.sql;
touch nasen_drop.sh
#!/bin/bash source /etc/profile PATH=/usr/local/bin:$PATH echo 'task_start' runuser - postgres -g postgres -c "sh /data/test/drop.sh" echo 'task_finish'
定時器調用
00 9 * * * /usr/bin/sh /data/test/drop_nasen.sh >>/data/test/drop_nasen.log
九、導出導入整個數據庫
備份導出整個數據庫
pg_dump -h localhost -U postgres -d riskDataCheck -Fc -f /soft/backup/20220321/test-0321.dump
導入某個數據庫
pg_restore -h localhost -U postgres -d risk_agcloud_430000_1 /soft/backup/test-0321.dump
十、切換數據庫存儲路徑遷移data目錄
步驟1：創(chuàng)建新data目錄
sudo mkdir /home/data
sudo chown -R postgres:postgres data
sudo chmod 700 data
步驟2：關閉數據庫服務
systemctl stop postgresql-12
步驟3：執(zhí)行復制
cp -rf /var/lib/pgsql/12/data/* /home/data #這個路徑是默認的在線安裝postgresql12.11默認路徑
sudo chown -R postgres:postgres data #加這句是因為復制過來有的時候歸屬是ROOT用戶，應該是postgres用戶才對！
步驟4：修改服務配置文件
cd /usr/lib/systemd/system
vi postgresql-12.service #默認的在線安裝postgresql12.11服務名稱
Environment=PGDATA=/home/data
步驟5：重新加載服務配置
systemctl daemon-reload
步驟6：重啟數據庫服務
systemctl start postgresql-12
步驟7：查看變更狀態(tài)
systemctl status postgresql-12
十一、安裝PostGIS插件
步驟1：安裝postgis的依賴包
rpm -ivh https://mirrors.aliyun.com/epel/epel-release-latest-7.noarch.rpm
步驟2：安裝postgis
yum install postgis31_12.x86_64 #這個對版本有一些對應關系注意，這個命令對應的是12.X
步驟3：安裝完畢后切換為postgres用戶，開啟擴展初始化操作
// 開啟插件
# su postgres # psql // 開啟pgsql的插件 postgres=# create extension postgis; postgres=# create extension postgis_topology; postgres=# create extension fuzzystrmatch; postgres=# create extension address_standardizer; postgres=# create extension address_standardizer_data_us; postgres=# create extension postgis_tiger_geocoder; //查看版本，驗證安裝是否成功 postgres=# SELECT PostGIS_full_version();
步驟4：安裝pgRouting
yum install pgrouting_12 #針對12.X版本
步驟5：檢查插件是否完成
十二、安裝uuid-ossp插件
postgres=# create extension “uuid-ossp”;
ERROR: could not open extension control file “/opt/pgsql12.2/share/extension/uuid-ossp.control”: No such file or directory
步驟1：安裝uuid依賴包
[root@Location-01 ~]# yum -y install e2fsprogs-devel uuid uuid-devel libuuid-devel
## 步驟2：執(zhí)行編譯配置
[root@Location-01 ~]# cd /usr/local/src/postgresql-12.2/ [root@Location-01 postgresql-12.2]# pwd /usr/local/src/postgresql-12.2 [root@Location-01 postgresql-12.2]# ./configure --prefix=/opt/pgsql12.2 --with-uuid=ossp
步驟3：編譯安裝uuid-ossp
[root@Location-01 ~]# cd /usr/local/src/postgresql-12.2/contrib/uuid-ossp/ [root@Location-01 uuid-ossp]# pwd /usr/local/src/postgresql-12.2/contrib/uuid-ossp [root@Location-01 uuid-ossp]# make && make install
步驟4：檢測是否成功
postgres=# create extension "uuid-ossp"; CREATE EXTENSION
postgres=# select * from pg_available_extensions;
name | default_version | installed_version | comment
------------+-----------------+-------------------+-------------------------------------------------
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
plperl | 1.0 | | PL/Perl procedural language
plperlu | 1.0 | | PL/PerlU untrusted procedural language
plpython2u | 1.0 | | PL/Python2U untrusted procedural language
plpythonu | 1.0 | | PL/PythonU untrusted procedural language
uuid-ossp | 1.1 | | generate universally unique identifiers (UUIDs)
(6 rows)
總結
到此這篇關于PostgreSQL實時查看數據庫實例正在執(zhí)行的SQL語句的文章就介紹到這了,更多相關PostgreSQL查看正在執(zhí)行SQL語句 內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了postgreSQL 非count方法算記錄數操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL教程(二十)：PL/pgSQL過程語言,本文講解了、PL/pgSQL概述、PL/pgSQL的結構、聲明、基本語句、控制結構等內容,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了PostgreSQL批量update與oracle差異,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了淺談PostgreSQL 11 新特性之默認分區(qū)，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了postgresql coalesce函數數據轉換方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了postgresql 中的參數查看和修改方式，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
本文詳細講解了Postgresql刪除數據庫表中重復數據的幾種方法，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-10-10
本文介紹了如何查詢PostgreSQL中所有表的邏輯外鍵，并指導您如何先刪除再重新建立這些外鍵，本文通過示例代碼給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友一起看看吧2023-08-08
這篇文章主要介紹了postgresql查看表和索引的情況,判斷是否膨脹的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了深入理解PostgreSQL的MVCC并發(fā)處理方式,文中同時介紹了MVCC的缺點,需要的朋友可以參考下2014-07-07