日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學無先后，達者為師

網站首頁 編程語言 正文

PostgreSQL實時查看數據庫實例正在執行的SQL語句實例詳解_PostgreSQL

作者：技術很渣 ? 更新時間： 2023-03-03 編程語言

一、查詢當前正在執行所有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用戶執行導入某個表的語句，免密執行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腳本執行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：創建新data目錄

sudo mkdir /home/data
sudo chown -R postgres:postgres data
sudo chmod 700 data

步驟2：關閉數據庫服務

systemctl stop postgresql-12

步驟3：執行復制

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：查看變更狀態

 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：執行編譯配置

[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)

總結

原文鏈接：https://blog.csdn.net/nasen512/article/details/125769140

欄目分類
最近更新