high availability table - just what those nines stand for ...

Availability %	Downtime per year	Downtime per month	Downtime per week	Downtime per day
90% ("one nine")	36.5 days	72 hours	16.8 hours	2.4 hours
99% ("two nines")	3.65 days	7.20 hours	1.68 hours	14.4 minutes
99.9% ("three nines")	8.76 hours	43.8 minutes	10.1 minutes	1.44 minutes
99.99% ("four nines")	52.56 minutes	4.38 minutes	1.01 minutes	8.66 seconds
99.999% ("five nines")	5.26 minutes	25.9 seconds	6.05 seconds	864.3 milliseconds
99.9999% ("six nines")	31.5 seconds	2.59 seconds	604.8 milliseconds	86.4 milliseconds
99.99999% ("seven nines")	3.15 seconds	262.97 milliseconds	60.48 milliseconds	8.64 milliseconds
99.999999% ("eight nines")	315.569 milliseconds	26.297 milliseconds	6.048 milliseconds	0.864 milliseconds
99.9999999% ("nine nines")	31.5569 milliseconds	2.6297 milliseconds	0.6048 milliseconds	0.0864 milliseconds

PostgreSQL how-to's

POSTGRESQL STARTER

1. INSTALLATION AND CONFIGURATION

1.1. Install the database server

 echo 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main' >> /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |   sudo apt-key add -

sudo apt-get update

apt-get install postgresql-9.4

1.1.1. add the repo

to add the repo and update the repositories in Ubuntu run:

 echo 'deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main' >> /etc/apt/sources.list.d/pgdg.list

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |   sudo apt-key add -

sudo apt-get update

apt-get install postgresql-9.4

1.1.2. install and verify

to add the repo and update the repositories in Ubuntu run:

apt-get install postgresql-9.4

su - postgres

/usr/lib/postgresql/9.4/bin/pg_ctl -V
pg_ctl (PostgreSQL) 9.4.8

sh /etc/init.d/postgresql status

1.2. Configuration files

Check the following sources:
http://www.davidpashley.com/articles/postgresql-user-administration/

# main configuration file , check with ps -ef
/etc/postgresql/9.4/main/postgresql.conf

# The host-based authentication is controlled by the pg_hba.conf file and defines which users can connect to which database and how they can connect to i
/etc/postgresql/9.4/main/pg_hba.conf

2. MAINTENANCE AND OPERATION

2.1. Basics

2.2. Start , stop , status

You can start , stop or check the status of the database server by issuing the following commands.

# your mileage might vary
sh /etc/init.d/postgresql status
sh /etc/init.d/postgresql stop
sh /etc/init.d/postgresql start

2.2.1. Change to the postgre user

You can access the postgre client from the postgre Linux user

 su - postgres
psql

2.2.2. Connect to a database

Connect to database. List tables. List views

-- list all the tables in the connected db
postgres=# \dt

-- list all the views in the connected db
postgres=# \dl

2.2.3. how-to run a sql script

#Run a SQL script (command line)
psql -f script.sql databaseName

2.3. Provisioning

Provisioning

2.3.1. Roles

2.3.1.1. How-to create a role in postresql

How-to create a role in postresql

CREATE ROLE role_name WITH LOGIN ENCRYPTED PASSWORD 'pwd' CREATEDB;

2.3.1.2. How-to create role with multiple privileges

How-to create a role in postresql

CREATE ROLE role_name_to_create WITH LOGIN ENCRYPTED PASSWORD  'pwd' CREATEDB CREATEROLE REPLICATION SUPERUSER;

2.3.1.3. How-to alter role in postgresql

How-to create a role in postresql

ALTER ROLE role_name_to_change CREATEROLE CREATEDB REPLICATION SUPERUSER;

2.3.1.4. How-to drop a role in postgresql

How-to drop a role in postgresql

DROP ROLE role_name_to_drop

2.3.2. Users

2.3.2.1. How-to create an user in postresql

Official docs @:
http://www.postgresql.org/docs/9.4/static/sql-createuser.html

CREATE USER user_name WITH PASSWORD 'pwd';

-- and verify by listing all the users
select * from pg_shadow;

2.3.2.2. how-to alter user in PostgreSQL

how-to change user password

ALTER USER colin WITH PASSWORD 'letmein';

2.3.2.3. how-to drop user in PostgreSQL

Just like creating users, there are two ways to remove users, using the command line or SQL. At the command line we would use:

 DROP USER user_to_drop ; 

2.3.3. Groups

Groups are entirely optional in postgresql.

2.3.3.1. how-to view groups in PostgreSQL

We can see group membership by viewing the pg_group system table. In this example I’ve added alice back into the sales group.

select * from pg_group ;

2.3.3.2. How-to create group in postresql

Groups are only used to simplify granting and revoking privileges for the db admin, and users do not need to be a member of any group.

CREATE GROUP group_to_add WITH USER existing_user ;

2.3.3.3. how-to add or remove users from groups

You can add or remove users from groups after they have been created using the ALTER GROUP command:

 ALTER GROUP groupname [ADD|DROP] USER username [, ...  ]

2.4. Db backups and restore

# Backup a database (command line)
pg_dump dbName > dbName.sql

# Backup all databases (command line)
pg_dumpall > pgbackup.sql

2.4.1. Priveledges

2.4.1.1. How-to Grant all priveledges to a role on a db

Grant all priveledges to a role on a db

GRANT ALL PRIVILEGES ON DATABASE db_name TO role_to_be_granted ;

2.4.2. how-to list the current settings

psql- c "SELECT current_setting('datestyle');";

2.5. how-to list the current settings

psql- c "SELECT current_setting('datestyle');";

3. DEVELOPMENT

3.1. Objects creation , DDL

3.1.1. How-to create database

How-to create database

-- CREATE DATABASE
DROP DATABASE IF EXISTS dbName ;
CREATE DATABASE dbName;

CREATE DATABASE utf8db WITH OWNER owner_role ENCODING 'UTF8';

3.1.2. how-to create table

how-to create table
Check the datatypes docs:
http://www.postgresql.org/docs/9.4/static/datatype.html

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

-- Add a primary key
ALTER TABLE TableName ADD PRIMARY KEY (id);

3.1.3. how-to create an index

how-to create an index

-- Create an INDEX
CREATE UNIQUE INDEX indexName ON TableName (columnNames);

-- Create a Function
CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integer 
 AS 'SELECT date_part(''month'', $1)::integer;'
LANGUAGE 'sql';

3.1.4. how-to create a function in PostgreSQL

how-to create a function

-- Create a Function
CREATE FUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNS text
AS
$$
 SELECT CASE
        WHEN $3 THEN UPPER($1 || ' ' || $2)
        ELSE LOWER($1 || ' ' || $2)
        END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

-- and call it 
SELECT concat_lower_or_upper('Hello', 'World', true);

3.2. DML

3.2.1. Pagination

Pagination , limit

-- The first N records
SELECT columns FROM table LIMIT 10;
-- Pagination
SELECT cols FROM table LIMIT 10 OFFSET 30;

3.2.2. Regular expressions

Regular expressions

  -- Search using a regular expression
SELECT column FROM table WHERE column ~ 'foo.*';

3.2.3. Prepared statements

Prepared statements

 -- Prepared Statements
PREPARE preparedInsert (int, varchar) AS
  INSERT INTO TableName (intColumn, charColumn) VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
DEALLOCATE preparedInsert;

how-to count the rows per table in netezza

nzsql -qtr -d GS_DB -q -c \

"SELECT SCHEMA|| '.' || TABLENAME FROM _V_TABLE \

WHERE 1=1 AND TABLENAME NOT LIKE '_T%' and TABLENAME NOT LIKE '_VT_%' " \

| sort | { while read -r t ; do nzsql -qtr -d GS_DB \

-c "SELECT count(*) || ' ' || '$t' from $t ;"; done ; } | sort -n

vim starter

VIM STARTER

1. NAVIGATION IN VIM

1.1. how-to navigate in visual mode in vim

Use the following keyboard shortcuts:
j - for going one row down
k - for going one row up
h - for hoing one char left
l - for going one char right

1.2. how-to search for string recursively within vim

Use the following command in vim:

:vimgrep /doBuild/ `find . -type f -name '*.pm' -o -name '*.pl'`

2. COMMAND LINE TIPS AND TRICKS IN VIM

2.1. how-to open multiple file of the same file type in bash with vim

Run the following command in bash:

vim -o `find . -name .include`

3. VIM CONFIGURATION

3.1. Configuration files

Check the following configuration files

# the main configuration file of vim

 ~/.vimrc

/~/.vim/after/syntax/perl/MojoliciousLite.vim

/~/.vim/syntax/epl.vim

/~/.vim/snippets/epl.snippets

/~/.vim/ftdetect/epl.vim

/~/.vim/.netrwhist

/~/.vim/colors/elflord.vim

LINUX , UNIX , BASH AND CYGWIN TIPS AND TRICKS

LINUX , UNIX , BASH AND CYGWIN TIPS AND TRICKS

This document contains linux unix bash cygwin related code snippets

1. FILE AND DIRS MANAGEMENT

This section contains tips and tricks on files and dirs management.

1.1. how-to search for string recursively in bash

Run the following command

for file in `find . \( -name '*.pm' -or -name '*.pl' \) -type f `; do grep -nHP boo $file ; done ;

1.2. how-to create a symlink

how-to create a symlink

# START === create symlink

export link_path=/vagrant

export target_path=/mnt/hgfs/vagrant

mkdir -p `dirname $link_path`

unlink $link_path

ln -s "$target_path" "$link_path"

ls -la $link_path;

# STOP === create symlink

1.3. how-to search and replace line feeds in a selection in excel

1. Select the cells.
2. Ctrl + H to present the find and replace dialog
3. For search: press the Alt num and from the Num keyboard type : 013 or 011
4. For replace: type the char to replace with
5. Replace all the occurrencies by : Alt + A

1.4. how-to search and replace strings in file contents recursively

In order to search and replace strings in the file and dir contents perform the copy paste the following code in your bash shell terminal.

# START -- how-to search and replace recursively

export dir=/var/company/3rdparty/docs/docx;

export to_search="emric"

export to_replace="t24"

#-- search and replace in file contents

find "$dir/" -type f -exec perl -pi -e "s#$to_search#$to_replace#g" {} \;

find "$dir/" -type f -name '*.bak' | xargs rm -f

# STOP  -- how-to rename files recursively

1.5. how-to search and replace strings in file and dirnames recursively

In order to search and replace strings in the file and dir paths perform the copy paste the following code in your bash shell terminal.

# START -- how-to search and replace recursively

export dir=/var/company/3rdparty/docs/docx;

export to_search="emric"

export to_replace="t24"

#-- search and replace in file names

find "$dir/" -type d |\

perl -nle '$o=$_;s#'"$to_search"'#'"$to_replace"'#g;$n=$_;`mkdir -p $n` ;'

find "$dir/" -type f |\

perl -nle '$o=$_;s#'"$to_search"'#'"$to_replace"'#g;$n=$_;rename($o,$n) unless -e $n ;'

2. PACKING AND UNPACKING

Bunch of commands for packing and unpacking on Linux

2.1. how-to unpack a *.tar.gz package

Run the followint commands in the bash shell:

export dir=<>

cd $dir

# upack !!!

gzip -dc *.tar.gz | tar xvf -

2.2. how-to unpack a tar package

how-to unpack a tar package

#how-to unpack tar file

tar xvf $file

3. LINUX HARDWARE

3.1. how-to check the type of processors on the System

how-to check the type of processors on the System

cat /proc/cpuinfo | less

4. LINUX NETWORKING

4.1. how-to check which ports are listening

how-to check which ports are listening + others netstta

# which processes are listening on my system

netstat --tcp --listening --programs

netstat --tcp

netstat --route

4.2. how-to implement public private key authentication - pkk

Read the comments

You migth wanto to:

export ssh_usesr=some_user_name

export ssh_server=some_server_dns_or_ip

4.2.1. how-to implement rsa public private key authentication

Start on the client , check the comments when to move on the server.

# START === how-to implement public private key ( pkk ) authentication

# create pub priv keys on server

# START copy

ssh-keygen -t rsa

# copy the rsa pub key to the ssh server

scp ~/.ssh/id_rsa.pub $ssh_user@$ssh_server:/home/$ssh_user/

# STOP copy 

# Hit enter twice

# START copy

cat id_rsa.pub >> ~/.ssh/authorized_keys

cat ~/.ssh/authorized_keys

chmod -v 0700 ~/.ssh

chmod -v 0600 ~/.ssh/authorized_keys

chmod -v 0600 ~/.ssh/id_rsa

chmod -v 0644 ~/.ssh/id_rsa.pub

find ~/.ssh -exec stat -c "%U:%G %a %n" {} \;

rm -fv ~/id_rsa.pub

# STOP COPY

4.2.2. how-to implement dsa public private key authentication

Start on the client , check the comments when to move on the server.

# START copy

ssh-keygen -t dsa

# STOP copy 

# Hit enter twice

# START copy

cat id_dsa.pub >> ~/.ssh/authorized_keys

cat ~/.ssh/authorized_keys

chmod -v 0700 ~/.ssh

chmod -v 0600 ~/.ssh/authorized_keys

chmod -v 0600 ~/.ssh/id_dsa

chmod -v 0644 ~/.ssh/id_dsa.pub

find ~/.ssh -exec stat -c "%U:%G %a %n" {} \;

rm -fv ~/id_dsa.pub

# STOP COPY

# STOP === how-to implement public private key authentication

5. BASH

5.1. how-to use while loop in bash

This is an example usage of the while loop in bash

find `pwd` | { while read -r file ; do echo "$file" ; done ; }

5.2. how-to use for loop in bash

This is an example usage of the while for in bash. Note the find command herewith searches for 2 types of files

for file in `find / -type f \( -name "*.pl" -or -name "*.pm" \) -exec file {} \; | grep text | perl -nle 'split /:/;print $_[0]' `; do grep -i --color -nH 'string_to_search'  $file ; done ;