dexalex84’s gists

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 / 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 / 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 / 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 / gist:4dff2796c7a520c2fee4a3f2938e84b4

Created November 19, 2016 15:03

PostgreSQL Show Indexes and IsUnique Flag in tables

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 / 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 / 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 / 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 / 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

Aleksei Burenin dexalex84