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

學(xué)無先后，達(dá)者為師

網(wǎng)站首頁 編程語言 正文

PostgreSQL實(shí)時(shí)查看數(shù)據(jù)庫實(shí)例正在執(zhí)行的SQL語句實(shí)例詳解_PostgreSQL

作者：技術(shù)很渣 ? 更新時(shí)間： 2023-03-03 編程語言

一、查詢當(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í)長(zhǎng)秒
query 查詢SQL語句

三、按查詢進(jìn)程，殺掉慢查詢釋放資源

SELECT pg_terminate_backend(PID);
SELECT pg_terminate_backend(6289);

四、擴(kuò)展query的顯示長(zhǎng)度能展示出全部語句

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òng)導(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ù)庫存儲(chǔ)路徑遷移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用戶才對(duì)！

步驟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è)對(duì)版本有一些對(duì)應(yīng)關(guān)系注意，這個(gè)命令對(duì)應(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   #針對(duì)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：檢測(cè)是否成功

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

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

欄目分類
最近更新