ycku

• 使用ssh跳版機(Bastion server)連線到 remote host

ssh -J bastion_user@bastion_host remote_user@remote_host

• 使用ssh跳版機執行 scp

scp -oProxyJump=bastion_user@bastion_host remote_user@remote_host:/tmp/test.txt .

ycku

• 把指定來源的 repository 檔案都抓回來

wget -r -np --no-check-certificate http://fedora.cs.nctu.edu.tw/epel/8/Everything/x86_64/

ycku

sudo mokutil --disable-validation

ycku

REINDEX 會影響線上作業, 在有限的作業時間內, 儘可能進行更多的 REINDEX

• 在 DEADLINE 之前能做多少算多少, DEADLINE 之後就放棄
• 此例以 INDEX SIZE 為最基本的排序, 大的先做
• 後續可以再加上使用統計的條件, 更精確地 REINDEX 需要重建的索引
• scripts.list 每行一個 shell 指令
• maintenance.sh 可用於其他排程控制
• SQL 語句條件記得要修改

$ psql --dbname=pgbench -t -f reindex_by_size_desc.sql > scripts.list

ycku

                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=1.43..6360.93 rows=1 width=60) (actual time=19.573..273.880 rows=15 loops=1)
   ->  Nested Loop Left Join  (cost=0.99..2521.89 rows=1 width=24) (actual time=9.920..134.981 rows=15 loops=1)
         ->  Index Scan using dtapb001_pk on dtapb001 a  (cost=0.56..19.74 rows=1 width=18) (actual time=0.025..0.135 rows=15 loops=1)
               Index Cond: ((emp_id = 'JXXXXXXXXX'::bpchar) AND (vrfy_wkym <= '202009'::numeric) AND (vrfy_wkym >= '201908'::numeric))
               Filter: (substr((emp_org_id)::text, 2, 1) = '3'::text)
               Rows Removed by Filter: 30
         ->  Index Scan using pg060_idx2 on pg060 s  (cost=0.43..2502.14 rows=1 width=24) (actual time=8.987..8.987 rows=0 loops=15)

ycku

                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=107446.22..266934.22 rows=1 width=60) (actual time=561.213..14329.521 rows=15 loops=1)
   ->  Nested Loop Left Join  (cost=107445.79..139804.60 rows=1 width=24) (actual time=558.920..7446.517 rows=15 loops=1)
         ->  Index Scan using dtapb001_pk on dtapb001 a  (cost=0.56..19.74 rows=1 width=18) (actual time=0.209..1.322 rows=15 loops=1)
               Index Cond: ((emp_id = 'JXXXXXXXXX'::bpchar) AND (vrfy_wkym <= '202009'::numeric) AND (vrfy_wkym >= '201908'::numeric))
               Filter: (substr((emp_org_id)::text, 2, 1) = '3'::text)
               Rows Removed by Filter: 30
         ->  Bitmap Heap Scan on pg060 s  (cost=107445.23..139784.85 rows=1 width=24) (actual time=496.333..496.333 rows=0 loops=15)

ycku

$target_dir = "d:\temp"
# WHERE for safe test
# Process the whole directories by removing WHERE clause
$target_list = Get-ChildItem $target_dir | WHERE { $_.name -eq "00123456" }

foreach ($userdir in $target_list) {
	$userdir.Fullname
	$ACL = Get-ACL $userdir.Fullname
	# Disable inheritance
	$ACL.SetAccessRuleProtection($True, $False)

ycku

-- password is saved in ~/.pgpass
-- Connect to your pgbouncer and then get "SHOW CLIENTS"
-- SELECT * FROM pgbouncer.connect;
-- SELECT * FROM pgbouncer.clients;

CREATE EXTENSION IF NOT EXISTS dblink;
CREATE SCHEMA IF NOT EXISTS pgbouncer;

DROP VIEW pgbouncer.connect;
CREATE VIEW pgbouncer.connect AS SELECT dblink_connect('pgbouncer','host=localhost port=5432 user=pgbouncer dbname=pgbouncer');

ycku

Date	Scale	Size (MB)
2020/01/01	0	7.77
2020/02/01	10	157.00
2020/03/01	50	756.00
2020/04/01	100	1503.00
2020/04/02	100	1507.00
2020/04/03	100	1510.00
2020/04/04	100	1515.00

ycku

DECLARE @vmname  VARCHAR(20)
set @vmname='VMXX-001'
delete FROM [vdi7s_composer].[dbo].[SVI_VM_NAME] where NAME=@vmname
delete FROM [vdi7s_composer].[dbo].[SVI_COMPUTER_NAME] where NAME=@vmname
delete  FROM [vdi7s_composer].[dbo].[SVI_TASK_STATE] where SIM_CLONE_ID=(select id FROM [vdi7s_composer].[dbo].[SVI_SIM_CLONE] where VM_NAME=@vmname)
delete 
  FROM [vdi7s_composer].[dbo].[SVI_SC_PDISK_INFO] where PARENT_ID=(select id FROM [vdi7s_composer].[dbo].[SVI_SIM_CLONE] where VM_NAME=@vmname)
delete 
  FROM [vdi7s_composer].[dbo].[SVI_SC_BASE_DISK_KEYS] where PARENT_ID=(select id FROM [vdi7s_composer].[dbo].[SVI_SIM_CLONE] where VM_NAME=@vmname)
delete