Skip to content

Instantly share code, notes, and snippets.

Avatar
🏠
Working from home

Sebastian Webber sebastianwebber

🏠
Working from home
View GitHub Profile
@sebastianwebber
sebastianwebber / date_interval.sql
Created Sep 17, 2014
SQL Server function to return interval a date interval between 2 datetime fields
View date_interval.sql
IF EXISTS (SELECT *
FROM dbo.sysobjects
WHERE id = object_id (N'[dbo].[date_interval]')
AND OBJECTPROPERTY(id, N'IsTableFunction') = 1)
DROP FUNCTION [dbo].[date_interval]
GO
CREATE FUNCTION [dbo].[date_interval] ( @p_start datetime, @p_end datetime)
RETURNS @DATES TABLE ( generated_date datetime )
AS
@sebastianwebber
sebastianwebber / pg_repack helpers and bloat stuff views
Created Sep 30, 2014
Views to generate pg_repack command line and show bloat tables
View pg_repack helpers and bloat stuff views
-- the original
-- avaliable on: https://wiki.postgresql.org/wiki/Show_database_bloat
CREATE VIEW vw_bloat_objects AS
SELECT
current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
iname, /*ituples::bigint, ipages::bigint, iotta,*/
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
@sebastianwebber
sebastianwebber / jboss-domain.sh
Last active Aug 29, 2015
JBoss AS7+/EAP 6+ Control Script for DomainMode on EL6 platform
View jboss-domain.sh
#!/bin/bash
#
# chkconfig: 345 60 40
#
# JBoss AS7+/EAP 6+ Control Script for DomainMode on EL6 platform
#
# Developped by:
# Sebastian Webber - http://swebber.me
# Last update:
# 2014-10-30
@sebastianwebber
sebastianwebber / baixa_hq.sh
Last active Aug 29, 2015
script para download de gibis do hqonline.com.br
View baixa_hq.sh
#!/bin/bash
### http://blog.edwards-research.com/2010/01/quick-bash-trick-looping-through-output-lines/
SEPARADOR="|"
function formata_nro() {
printf "%0${1}d" ${2}
}
@sebastianwebber
sebastianwebber / gen_pgauth.sh
Created Dec 11, 2014
Generates pgauth's file
View gen_pgauth.sh
#!/bin/bash
export AUTH_FILE=/etc/pgbouncer/pgbouncer.auth
TMPFILE=$( mktemp )
trap 'rm -f "$TMPFILE"' EXIT
psql -Upostgres -p 9876 -qAtX -c 'SELECT $$"$$ || replace( usename, $$"$$, $$""$$) || $$" "$$ || replace( passwd, $$"$$, $$""$$ ) || $$"$$ from pg_shadow where passwd is not null order by 1' > "$TMPFILE"
if [[ ! -s "$TMPFILE" ]]
then
@sebastianwebber
sebastianwebber / make_pgbouncer_conn.sql
Created Dec 11, 2014
Generate connection script for pgbouncer
View make_pgbouncer_conn.sql
select datname || ' = port=9876 dbname=' || datname from pg_database
order by datname;
@sebastianwebber
sebastianwebber / top-10-tables_by-size.sql
Created Jan 2, 2015
Listando as 10 maiores tabelas no PostgreSQL
View top-10-tables_by-size.sql
WITH table_stats AS (
SELECT
schemaname,
tablename,
pg_relation_size(schemaname || '.'|| tablename) as table_size,
(pg_total_relation_size(schemaname || '.'|| tablename) - pg_relation_size(schemaname || '.'|| tablename)) as index_size,
pg_total_relation_size(schemaname || '.'|| tablename) as total_size
FROM
pg_tables
)
@sebastianwebber
sebastianwebber / apagar-banco.sh
Created Jan 29, 2015
Lab 3.1 - Curso de PostgreSQL - Administração
View apagar-banco.sh
dropdb -U postgres -d lab3
@sebastianwebber
sebastianwebber / pgsql
Created Mar 1, 2015
PostgreSQL LogRotate configuration file
View pgsql
/var/log/pgsql/pgsql.log {
missingok
rotate 7
nomail
sharedscripts
daily
dateext
create 0660 root root
postrotate
@sebastianwebber
sebastianwebber / wildfly.service -.ini
Last active Aug 29, 2015 — forked from marekjelen/gist:8568448
Wildfly Domain Controller Service for SystemD systems
View wildfly.service -.ini
[Unit]
Description=WildFly Domain Controller
After=network.target
[Service]
Type=simple
User=jboss
Group=jboss
ExecStart=/opt/wildfly-8.2.0.Final/bin/domain.sh -bmanagement=0.0.0.0