Pavel Kerbel kerbelp

## redshift_currnet_connections.sql
SELECT recordtime,
       username,
       dbname,
       remotehost,
       remoteport
FROM stl_connection_log
WHERE event = 'initiating session'
AND   pid NOT IN (SELECT pid
                  FROM stl_connection_log
                  WHERE event = 'disconnecting session')

## redhisft_lock_tables.sql
SELECT table_id,
       last_update,
       lock_owner,
       lock_owner_pid,
       lock_status
FROM stv_locks;

## redshift_terminate_session.sql
select pg_terminate_backend('lock_owner_pid');

## redshift_cancel_query.sql
cancel 'pid';

## current_mtu_status.sh
ip link show eth0

## change_mtu.sh
sudo ip link set dev eth0 mtu 1500

## redshift_stl_load_errors_column_files.sql
SELECT TRUNC(starttime) AS day,
       colname,
       err_reason,
       filename,
       line_number
FROM stl_load_errors
WHERE colname = 'err'
GROUP BY day,
         colname,
         err_reason,

## redshfit_delete_old_data.sql
DELETE FROM schema.table
WHERE ts < (SELECT sysdate - 90);

## redshift_unload_yesterday.sql
UNLOAD ('SELECT "ts", "column_a", "column_b"
				 FROM schema.table
         WHERE ts >= (SELECT DATE_TRUNC(\'day\', (SELECT sysdate - 1)))
             AND ts < (SELECT DATE_TRUNC(\'day\', (SELECT sysdate)))'
       )
TO 's3://bucket/prefix'
GZIP
ESCAPE;

## redshift_vacuum_full.sql
VACUUM FULL schema.table;
	SELECT recordtime,
	username,
	dbname,
	remotehost,
	remoteport
	FROM stl_connection_log
	WHERE event = 'initiating session'
	AND pid NOT IN (SELECT pid
	FROM stl_connection_log
	WHERE event = 'disconnecting session')
	SELECT table_id,
	last_update,
	lock_owner,
	lock_owner_pid,
	lock_status
	FROM stv_locks;
	SELECT TRUNC(starttime) AS day,
	colname,
	err_reason,
	filename,
	line_number
	FROM stl_load_errors
	WHERE colname = 'err'
	GROUP BY day,
	colname,
	err_reason,
	UNLOAD ('SELECT "ts", "column_a", "column_b"
	FROM schema.table
	WHERE ts >= (SELECT DATE_TRUNC(\'day\', (SELECT sysdate - 1)))
	AND ts < (SELECT DATE_TRUNC(\'day\', (SELECT sysdate)))'
	)
	TO 's3://bucket/prefix'
	GZIP
	ESCAPE;