yujihamaguchi

プロフィール

生年月日

1975/5/1

所属会社

キャディ株式会社

現年収

850万円

希望する職種

プロジェクトマネージャー、バックエンドエンジニア

yujihamaguchi

aws ec2 describe-snapshots --owner {12桁のアカウントID} | jq '.Snapshots[].SnapshotId' > ./all_snapshot_ids.log
aws ec2 describe-images --owner {12桁のアカウントID} | jq '.Images[].BlockDeviceMappings[].Ebs.SnapshotId' | grep ^\" > ./linked_snapshot_ids.log
comm -23 <(sort ./all_snapshot_ids.log) <(sort ./linked_snapshot_ids.log)

yujihamaguchi

1. 以下のバッチファイルをexec.batという名前で保存する。

@echo off

SET PARALLEL_DEGREE=%1
SET TARGET_RB_SCRIPT_PATH=%2
SET ACCESS_URL=%3
SET USER_ID=%4
SET PASSWORD=%5

yujihamaguchi

---------------------------------------------------------------------------------------------
-- 以下を実行し、レポート表示に要した時間を取得する。
-- (Tableau Server データベースへの接続に関しては下記を参照)
-- http://onlinehelp.tableau.com/current/server/ja-jp/help.htm#adminview_postgres.htm%3FTocPath%3D%25E7%25AE%25A1%25E7%2590%2586%25E8%2580%2585%25E3%2582%25AC%25E3%2582%25A4%25E3%2583%2589%7C%25E3%2582%25B5%25E3%2583%25BC%25E3%2583%2590%25E3%2583%25BC%25E3%2581%25AE%25E6%2593%258D%25E4%25BD%259C%7C%25E3%2582%25AB%25E3%2582%25B9%25E3%2582%25BF%25E3%2583%25A0%25E7%25AE%25A1%25E7%2590%2586%25E3%2583%2593%25E3%2583%25A5%25E3%2583%25BC%25E3%2581%25AE%25E4%25BD%259C%25E6%2588%2590%7C_____0
---------------------------------------------------------------------------------------------

SELECT
  r2.name AS site_name
  ,r1.currentsheet AS report_name
  ,r1.vizql_session AS session_id

yujihamaguchi

REVOKE ALL ON DATABASE {db name} FROM {user id} CASCADE;
REVOKE ALL ON SCHEMA {schema name 1} FROM {user id} CASCADE;
REVOKE ALL ON SCHEMA {schema name 2} FROM {user id} CASCADE;
...

yujihamaguchi

while :; do {処理}; sleep 1; done

yujihamaguchi

select current_time, c.relname, l.database, l.transaction, l.pid, a.usename, l.mode, l.granted from pg_catalog.pg_locks l join pg_catalog.pg_class c on c.oid = l.relation join pg_catalog.pg_stat_activity a on a.procpid = l.pid;

set query_group to 'superuser';
select pg_terminate_backend(<pid>);
reset query_group;

yujihamaguchi

- gen.sh
``` bash
#!/bin/sh
#
# [Usage]
#   args
#    1. User ID
#    2. Password
#    3. Host
#    4. Database Name

yujihamaguchi

SELECT
  TO_CHAR(starttime + INTERVAL '9 hour', 'YYYY-MM-DD HH24:MI:SS') AS starttime
  ,TO_CHAR(endtime + INTERVAL '9 hour', 'YYYY-MM-DD HH24:MI:SS') AS endtime
  ,DATE_DIFF('sec', starttime, endtime) AS secs
  ,text
FROM
  svl_statementtext
WHERE
  sequence = 0
  AND xid IN (

yujihamaguchi

SELECT
  MAX(endtime) + INTERVAL '9 hour' AS last_query_time
FROM
  stl_query r1
WHERE
  EXISTS (SELECT *
    FROM
      stl_querytext r2
    WHERE
      r1.userid = r2.userid