PostgreSQL實(shí)時(shí)查看數(shù)據(jù)庫實(shí)例正在執(zhí)行的SQL語句實(shí)例詳解
更新時(shí)間：2023年01月13日 10:02:23 作者：技術(shù)很渣
在任何數(shù)據(jù)庫中,分析和優(yōu)化SQL的執(zhí)行,最重要的工作就是執(zhí)行計(jì)劃的解讀,而說到執(zhí)行計(jì)劃得先了解postgresql的查詢執(zhí)行過程,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL實(shí)時(shí)查看數(shù)據(jù)庫實(shí)例正在執(zhí)行的SQL語句的相關(guān)資料,需要的朋友可以參考下
一、查詢當(dāng)前正在執(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 | 數(shù)據(jù)庫查詢進(jìn)程ID |
| query_stay | 查詢時(shí)長秒 |
| query | 查詢SQL語句 |
三、按查詢進(jìn)程，殺掉慢查詢釋放資源
SELECT pg_terminate_backend(PID); SELECT pg_terminate_backend(6289);
四、擴(kuò)展query的顯示長度能展示出全部語句
vi /var/lib/pgsql/12/data/postgresql.conf
修改track_activity_query_size的值重啟數(shù)據(jù)庫服務(wù)
五、殺掉某個(gè)數(shù)據(jù)庫的所有數(shù)據(jù)庫連接
SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activity WHERE datname='數(shù)據(jù)庫名稱' AND pid<>pg_backend_pid();
六、導(dǎo)出數(shù)據(jù)庫某個(gè)表
pg_dump -t 某個(gè)表名 "host=192.168.16.31 port=18921 user=postgres password=數(shù)據(jù)庫密碼 dbname=數(shù)據(jù)庫" -f /app/zjfbeifen/1.sql
七、shell腳本自動導(dǎo)入按表
touch insert_sql.sh
#!/bin/bash . /etc/profile . ~/.bash_profile export PATH=/usr/pgsql-12/bin:/usr/bin; export PGPASSWORD=數(shù)據(jù)庫密碼;psql -U postgres -d 數(shù)據(jù)庫名稱 < /mnt/nas/bak-06/1.sql;
指定postgres用戶執(zhí)行導(dǎo)入某個(gè)表的語句，免密執(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'
定時(shí)器配置
00 10 * * * /usr/bin/sh /data/test/insert_nasen.sh >>/data/test/insert_nasen.log
八、shell腳本執(zhí)行postgre的sql語句
touch drop01.sql
#刪除某個(gè)表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 數(shù)據(jù)庫名稱 < /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'
定時(shí)器調(diào)用
00 9 * * * /usr/bin/sh /data/test/drop_nasen.sh >>/data/test/drop_nasen.log
九、導(dǎo)出導(dǎo)入整個(gè)數(shù)據(jù)庫
備份導(dǎo)出整個(gè)數(shù)據(jù)庫
pg_dump -h localhost -U postgres -d riskDataCheck -Fc -f /soft/backup/20220321/test-0321.dump
導(dǎo)入某個(gè)數(shù)據(jù)庫
pg_restore -h localhost -U postgres -d risk_agcloud_430000_1 /soft/backup/test-0321.dump
十、切換數(shù)據(jù)庫存儲路徑遷移data目錄
步驟1：創(chuàng)建新data目錄
sudo mkdir /home/data
sudo chown -R postgres:postgres data
sudo chmod 700 data
步驟2：關(guān)閉數(shù)據(jù)庫服務(wù)
systemctl stop postgresql-12
步驟3：執(zhí)行復(fù)制
cp -rf /var/lib/pgsql/12/data/* /home/data #這個(gè)路徑是默認(rèn)的在線安裝postgresql12.11默認(rèn)路徑
sudo chown -R postgres:postgres data #加這句是因?yàn)閺?fù)制過來有的時(shí)候歸屬是ROOT用戶，應(yīng)該是postgres用戶才對！
步驟4：修改服務(wù)配置文件
cd /usr/lib/systemd/system
vi postgresql-12.service #默認(rèn)的在線安裝postgresql12.11服務(wù)名稱
Environment=PGDATA=/home/data
步驟5：重新加載服務(wù)配置
systemctl daemon-reload
步驟6：重啟數(shù)據(jù)庫服務(wù)
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 #這個(gè)對版本有一些對應(yīng)關(guān)系注意，這個(gè)命令對應(yīng)的是12.X
步驟3：安裝完畢后切換為postgres用戶，開啟擴(kuò)展初始化操作
// 開啟插件
# 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; //查看版本，驗(yàn)證安裝是否成功 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)
總結(jié)
到此這篇關(guān)于PostgreSQL實(shí)時(shí)查看數(shù)據(jù)庫實(shí)例正在執(zhí)行的SQL語句的文章就介紹到這了,更多相關(guān)PostgreSQL查看正在執(zhí)行SQL語句 內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
- Postgresql使用update語句的方法示例
- Postgresql 查看SQL語句執(zhí)行效率的操作
- postgresql SQL語句變量的使用說明
- Postgresql的pl/pgql使用操作--將多條執(zhí)行語句作為一個(gè)事務(wù)
- postgresql 賦權(quán)語句 grant的正確使用說明
- PostgreSQL標(biāo)準(zhǔn)建表語句分享
- postgresql 導(dǎo)出建表語句的命令操作
- postgresql 實(shí)現(xiàn)sql多行語句合并一行
- Postgresql ALTER語句常用操作小結(jié)
- Postgresql開啟SQL執(zhí)行語句收集過程
相關(guān)文章
這篇文章主要介紹了PostgreSQL忘記postgres賬號的密碼的解決方法,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了PgSQL條件語句與循環(huán)語句,pgSQL中有兩種條件語句分別為if與case語句，每種語句通過示例代碼給大家介紹的非常詳細(xì)，需要的朋友可以參考下2022-07-07
這篇文章主要給大家介紹了關(guān)于PostgreSQL操作json/jsonb的相關(guān)資料,PostgreSQL提供了兩種存儲JSON數(shù)據(jù)的類型:json和jsonb; jsonb是json的二進(jìn)制形式,文中介紹的非常詳細(xì),需要的朋友可以參考下2023-09-09
PostgreSQL數(shù)據(jù)庫如何定期清理歸檔(pg_wal)日志
在?般的?產(chǎn)環(huán)境中,數(shù)據(jù)庫都需要開啟歸檔模式,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL數(shù)據(jù)庫如何定期清理歸檔(pg_wal)日志的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-05-05
CentOS 7下安裝PostgreSQL 9.6的教程分享
PostgreSQL在我心目中的地位要遠(yuǎn)遠(yuǎn)高于MySQL，雖然流行對比MySQL低很對，但是功能性一致走在MySQL的前面。下面這篇文章主要介紹了CentOS 7下安裝PostgreSQL數(shù)據(jù)庫的方法,需要的朋友可以參考借鑒，一起來看看吧。2017-02-02
這篇文章主要為大家介紹了PostgreSQL聚合函數(shù)的分組排序使用示例，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進(jìn)步早日升職加薪2022-04-04
CentOS PostgreSQL 12 主從復(fù)制(主從切換)操作
這篇文章主要介紹了CentOS PostgreSQL 12 主從復(fù)制(主從切換)操作，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
使用psql操作PostgreSQL數(shù)據(jù)庫命令詳解
這篇文章主要為大家介紹了使用psql操作PostgreSQL數(shù)據(jù)庫命令詳解,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-08-08
在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù)的方法
這篇文章主要介紹了在PostgreSQL中使用ltree處理層次結(jié)構(gòu)數(shù)據(jù),本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-03-03
關(guān)于PostgreSQL 行排序的實(shí)例解析
這篇文章主要介紹了關(guān)于PostgreSQL 行排序的實(shí)例解析，具有很好的參考價(jià)值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01