Search This Blog

2016-05-26

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

2016-05-23

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

# 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
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;

2016-05-20

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

2016-05-12

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


2016-05-11

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 ;


Labels

perl (41) Cheat Sheet (25) how-to (24) windows (14) sql server 2008 (13) linux (12) oracle (12) sql (12) Unix (11) cmd windows batch (10) mssql (10) cmd (9) script (9) textpad (9) netezza (8) sql server 2005 (8) cygwin (7) meta data mssql (7) metadata (7) bash (6) code generation (6) Informatica (5) cheatsheet (5) energy (5) tsql (5) utilities (5) excel (4) future (4) generic (4) git cheat sheet (4) html (4) perl modules (4) programs (4) settings (4) sh (4) shortcuts (4) поуки (4) принципи (4) Focus Fusion (3) Solaris (3) cool programs (3) development (3) economy (3) example (3) freeware (3) fusion (3) logging (3) morphus (3) mssql 2005 (3) nuclear (3) nz (3) parse (3) python (3) sftp (3) sofware development (3) source (3) sqlplus (3) table (3) vim (3) .Net (2) C# (2) China (2) GUI (2) Google (2) GoogleCL (2) Solaris Unix (2) architecture (2) ascii (2) awk (2) batch (2) cas (2) chrome extensions (2) code2html (2) columns (2) configuration (2) conversion (2) duplicates (2) excel shortcuts (2) export (2) file (2) free programs (2) informatica sql repository (2) linux cheat sheet (2) mssql 2008 (2) mysql (2) next big future (2) nsis (2) nz netezza cheat sheet (2) nzsql (2) ora (2) prediction (2) publish (2) release management (2) report (2) security (2) single-click (2) sqlserver 2005 (2) sqlserver 2008 (2) src (2) ssh (2) template (2) tools (2) vba (2) video (2) xlt (2) xml (2) youtube videos (2) *nix (1) .vimrc (1) .virmrc vim settings configs (1) BSD license (1) Bulgaria (1) Dallas (1) Database role (1) Dense plasma focus (1) Deployment (1) ERP (1) ExcelToHtml (1) GD (1) GDP (1) HP-UX (1) Hosting (1) IDEA (1) INC (1) IT general (1) ITIL management bullshit-management (1) IZarc (1) Java Web Start (1) JavaScript anchor html jquery (1) Khan Academy (1) LINUX UNIX BASH AND CYGWIN TIPS AND TRICKS (1) Linux Unix rpm cpio build install configure (1) Linux git source build .configure make (1) ListBox (1) MIT HYDROGEN VIRUS (1) OO (1) Obama (1) PowerShell (1) Run-time (1) SDL (1) SIWA (1) SOX (1) Scala (1) Services (1) Stacks (1) SubSonic (1) TED (1) abstractions (1) ansible hosts linux bash (1) ansible linux deployment how-to (1) ansible yum pip python (1) apache (1) apache 2.2 (1) application life cycle (1) architecture input output (1) archive (1) arguments (1) avatar (1) aws cheat sheet cli (1) aws cli (1) aws cli amazon cheat sheet (1) aws elb (1) backup (1) bash Linux open-ssh ssh ssh_server ssh_client public-private key authentication (1) bash perl search and replace (1) bash stub (1) bin (1) biofuels (1) biology (1) books (1) browser (1) bubblesort (1) bugs (1) build (1) byte (1) cas_sql_dev (1) chennai (1) chrome (1) class (1) claut (1) cmdow (1) code generation sqlserver (1) command (1) command line (1) conf (1) confluence (1) console (1) convert (1) cool programs windows free freeware (1) copy paste (1) copy-paste (1) csv (1) ctags (1) current local time (1) cygwin X11 port-forwarding mintty xclock Linux Unix X (1) cygwin bash how-to tips_n_tricks (1) cygwin conf how-to (1) data (1) data types (1) db2 cheat sheet (1) db2 starter ibm bash Linux (1) debt (1) diagram (1) dictionaries (1) digital (1) disk (1) disk space (1) documentation (1) dos (1) dubai (1) e-cars (1) electric cars (1) electricity (1) emulate (1) errors (1) exponents (1) export workflow (1) extract (1) fast export (1) fexp (1) file extension (1) file permissions (1) findtag (1) firewall (1) for loop (1) freaky (1) functions (1) fusion research (1) german (1) git gitlab issues handling system (1) google cli (1) google code (1) google command line interface (1) gpg (1) ha (1) head (1) helsinki (1) history (1) hop or flop (1) host-independant (1) how-to Windows cmd time date datetime (1) ibm db2 cognos installation example db deployment provisioning (1) ideas (1) image (1) informatica oracle sql (1) informatica repo sql workflows sessions file source dir (1) informatica source files etl (1) install (1) isg-pub issue-tracker architecture (1) it management best practices (1) java (1) jump to (1) keyboard shortcuts (1) ksh (1) level (1) linkedin (1) linux bash ansible hosts (1) linux bash commands (1) linux bash how-to shell expansion (1) linux bash shell grep xargs (1) linux bash tips and t ricks (1) linux bash unix cygwin cheatsheet (1) linux bash user accounts password (1) linux bash xargs space (1) linux cheat-sheet (1) linux cheatsheet cheat-sheet revised how-to (1) linux how-to non-root vim (1) linux ssh hosts parallel subshell bash oneliner (1) london (1) make (1) me (1) metacolumn (1) metadata functions (1) metaphonre (1) method (1) model (1) movie (1) multithreaded (1) mysql cheat sheet (1) mysql how-to table datatypes (1) n900 (1) nano (1) neteza (1) netezza bash linux nps (1) netezza nps (1) netezza nps nzsql (1) netezza nz Linux bash (1) netezza nz bash linux (1) netezza nz nzsql sql (1) netezza nzsql database db sizes (1) non-password (1) nord pol (1) nps backup nzsql schema (1) number formatting (1) nz db size (1) nz table count rows (1) nzsql date timestamp compare bigint to_date to_char now (1) on-lier (1) one-liners (1) one-to-many (1) oneliners (1) open (1) open source (1) openrowset (1) openssl (1) oracle PL/SQL (1) oracle Perl perl (1) oracle installation usability (1) oracle number formatting format-model ora-sql oracle (1) oracle templates create table (1) oracle trigger generic autoincrement (1) oracle vbox virtual box cheat sheet (1) oracle virtual box cheat sheet (1) outlook (1) parser (1) password (1) paths (1) perl @INC compile-time run-time (1) perl disk usage administration Linux Unix (1) perl modules configuration management (1) permissions (1) php (1) picasa (1) platform (1) postgreSQL how-to (1) powerShell cmd cygwin mintty.exe terminal (1) ppm (1) predictions (1) prices (1) principles (1) productivity (1) project (1) prompt (1) proxy account (1) public private key (1) publishing (1) putty (1) qt (1) read file (1) registry (1) relationship (1) repository (1) rm (1) scala ScalaFmt (1) scp (1) scripts (1) scsi (1) search and replace (1) sed (1) sendEmail (1) sh stub (1) shortcuts Windows sql developer Oracle (1) sidebar (1) silicon (1) smells (1) smtp (1) software development (1) software procurement (1) sofware (1) sort (1) sql script (1) sql_dev (1) sqlcmd (1) sqlite (1) sqlite3 (1) sshd (1) sshd cygwin (1) stackoverflow (1) stored procedure (1) stub (1) stupidity (1) subroutines (1) svn (1) sysinternals (1) system design (1) tail (1) tar (1) temp table (1) templates (1) teradata (1) terminal (1) test (1) testing (1) theory (1) thorium (1) time (1) tip (1) title (1) tmux .tmux.conf configuration (1) tmux efficiency bash (1) tool (1) ui code prototyping tips and tricks (1) umask Linux Unix bash file permissions chmod (1) url (1) urls (1) user (1) utility (1) utils (1) vb (1) vbox virtual box cheat sheet (1) vim perl regex bash search for string (1) vim recursively hacks (1) vim starter (1) vim-cheat-sheet vim cheat-sheet (1) vimeo (1) visual stuio (1) warsaw (1) wiki (1) wikipedia (1) window (1) windows 7 (1) windows 8 (1) windows programs (1) windows reinstall (1) windows utility batch perl space Windows::Clipboard (1) wisdoms (1) workflow (1) worth-reading (1) wrapper (1) xp_cmdshell (1) xslt (1) youtube (1)

Blog Archive

Translate with Google Translate

My Blog List