Skip to content

Instantly share code, notes, and snippets.

View dexalex84's full-sized avatar

Aleksei Burenin dexalex84

1 follower · 1 following
View GitHub Profile
@dexalex84
dexalex84 / gist:bc23a775fca338d64f123170fc74eed9
Created November 19, 2016 08:35
PostgreSQL Find Table Size
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
@dexalex84
dexalex84 / gist:fc72a276e23a9bd48172683cba8ef75e
Created November 19, 2016 08:37
PostgreSQL Size of waste in tables - to know how much to vacuum
SELECT
tablename as table_name,
ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS table_bloat,
CASE WHEN relpages < otta THEN '0' ELSE pg_size_pretty((bs*(sml.relpages-otta)::bigint)::bigint) END AS table_waste,
iname as index_name,
ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages/iotta::numeric END,1) AS index_bloat,
CASE WHEN ipages < iotta THEN '0' ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) END AS index_waste
FROM
(
SELECT
@dexalex84
dexalex84 / gist:967047723de18e78bb8e12c189388b30
Created November 19, 2016 08:40
PostgreSQL Set some tables to unlogged mode
do
$$
declare var_sql character varying(8000);
begin
for var_sql in
select
'alter table '|| t1.table_schema||'."'||t1.table_name||'" set unlogged'
@dexalex84
dexalex84 / gist:0d68e31050a4cfae5776b615ed954c2f
Created November 19, 2016 14:52
PostgreSQL Count of rows in tables in one schema by simple count and store in temp table
;
do
$$
DECLARE var_table_name character varying(1000);
var_cnt bigint;
begin
drop table if exists temp_tables;-- ( character varying(1000), bigint);
create temp table if not exists temp_tables (table_name character varying(1000), cnt bigint);
truncate table tt_tables;
@dexalex84
dexalex84 / gist:4dff2796c7a520c2fee4a3f2938e84b4
Created November 19, 2016 15:03
PostgreSQL Show Indexes and IsUnique Flag in tables
select
n.nspname TableSchema,
c.relname as TableName,
a.relname as IndexName,
b.indisunique IsUnique
from
pg_class a
@dexalex84
dexalex84 / gist:4684eed68fd464a79651bb5a18d114dd
Created November 19, 2016 16:09
PostgreSQL drop specific indexes on one specific schema
;
do
$$
DECLARE var_table_name character varying(1000);
var_table_schema character varying(1000);
var_index_name character varying(1000);
var_query character varying(1000);
var_is_debug boolean = false;
begin
@dexalex84
dexalex84 / gist:3c0985ade3a51c3cd6ece2753a3b16f2
Created February 2, 2017 08:47
From ETL to Streaming ( PG 9.6 + Bottlewater Extension and Kafka Produser + Apache Kafka Server ) Part 1.
These instructions shows installation steps for bottlewater-pg and apache kafka by www.confluent.io on CentOS 7
used links
https://github.com/confluentinc/bottledwater-pg
https://www.confluent.io/
everything were installed on clean CentOS 7.0
After sucsessfully finishing all steps you can populate changes from PG to Apache Kafka server
1)
@dexalex84
dexalex84 / gist:7e33aadbffac5046b1df19b3d7d56608
Last active March 30, 2017 17:57
Regular expressions example
This text:
ETZzz Hostel 2 звезд
Номер бронирования: 1343489307 Пин-код: 4776 Отдых
5 639 руб. THB 3 420
ЗАЕЗД
11
ФЕВ 2017
суббота
ОТЪЕЗД
@dexalex84
dexalex84 / gist:6f0120b4d77684253c96905a6098b10b
Created March 31, 2017 11:12
Make root user in postgres to log in and be a superuser
alter role root with SUPERUSER;
alter role root with login;
@dexalex84
dexalex84 / gist:334bdd189dd923a63e80c45300fb30be
Last active August 8, 2022 04:41
Pentaho BA CE 7.0 Install with PostgreSQL repo DB on CentOS
Pentaho BA 7.0 Install with PostgreSQL repo DB
INSTALL POSTGRES
https://wiki.postgresql.org/wiki/YUM_Installation
1) find
/etc/yum.repos.d/CentOS-Base.repo
vi /etc/yum.repos.d/CentOS-Base.repo
remove
add