SonarQube setup

SonarQube (formerly Sonar) is an open source platform for continuous inspection of code quality.

Step1: download sonar and extract zip file
Step2: run StartSonar(which can be found in bin directory)
Step3: open browser and enter localhost:9000, then you should able to see sonar home page.
Step4: open your project pom.xml path in cmd and enter mvn sonar:sonar
Step5:after mvn sonar:sonar you can see project sonar issues in web(ie localhost:9000)

Default login:
user:admin
pwd:admin

Note: Rules has to be uploaded(or created) before runing mvn sonar:sonar.Rules has to uploaded in Quality profile.

postgreSQL Queries

PostgreSQL Queries syntax:

Data types:
character varying(n), varchar(n) --> variable-length with limit
character(n), char(n) --> fixed-length
text --> variable unlimited length
integer or int --> this type is for integer, and also we have few variants for bigger and smaller integers(ie smallint,bigint)
timestamp --> time and date
time --> only time(ie HH:MM:SS)
date --> only date

PostgreSQL queries syntaxs are mostly like oracle
insert Query:
Syntax1: to insert values into paticular columns
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
Syntax2:to insert into all columns
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

select Query:
SELECT column1, column2, columnN FROM table_name;

update Query:
UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

delete Query:
DELETE FROM table_name
WHERE [condition];

alter Query:
Few Examples:
ALTER TABLE EMP ADD COLUMN address varchar(30);
ALTER TABLE EMP DROP COLUMN address;
ALTER TABLE distributors
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);


drop Query:
DROP TABLE table_name;

like and ilike:
SELECT FROM table_name
WHERE column LIKE 'XXXX';

ILIKE is to ignorecase
SELECT FROM table_name
WHERE column ILIKE 'XXXX';

Wildcards % and _ can be used in search string.

limit clause:
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

order by clause:
SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

group by clause:
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

having clause:
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN


also see below
what is postgreSQL
Why postgresSQL
History of PostgreSQL
postgreSQL - PSQL MATA Commands
postgreSQL what is schema?
postgreSQL Connecting to a Database
postgreSQL Create and access Database
How to create user in postgreSQL?
postgreSQL Grant/Revoke Privileges
postgreSQL Tablespaces and datafiles
postgreSQL How to start server and stop server?
postgreSQL perfomance
postgreSQL How to create cluster?
postgreSQL Queries
postgreSQL routine maintenance and proactive-maintanace.
postgresql-logging

postgresql logging

below properties can be updated in postgresql.conf file

Where To Log

• log_destination-Valid values are combinations of stderr, csvlog, syslog, and eventlog, depending on platform.
• logging_collector-Enables advanced logging features. csvlogrequires logging_collector.
• log_directory-Directory where log files are written. Requires logging_collector.
• log_filename-Format of log file name (e.g. enterprisedb-%Y-%M-%d-%h%m%s.log). Allows regular log rotation. Requires logging_collector.
• log_file_mode(default 0600) -On Unix systems this parameter sets the permissions for log files when logging_collectoris enabled.
• log_rotation_age-Automatically rotate logs after this much time. Requires logging_collector.
• log_rotation_size-Automatically rotate logs when they get this big. Requires logging_collector.

When To Log

• client_min_messages(default NOTICE) -Messages of this severity level or above are sent to the client.
• log_min_messages(default WARNING) -Messages of this severity level or above are sent to the server.
• log_min_error_statement(default ERROR) -When a message of this severity or higher is written to the server log, the statement that caused it is logged along with it.
• log_min_duration_statement(default -1, disabled) -When a statement runs for at least this long, it is written to the server log, with its duration.

What To Log

• log_connections(default off) -Log successful connections to the server log.
• log_disconnections(default off) -Log some information each time a session disconnects, including the duration of the session.
• log_error_verbosity(default “default”) -Can also select “terse” or “verbose”.
• log_duration(default off) -Log duration of each statement.
• log_line_prefix-Additional details to log with each line.
• log_statement(default none) -Legal values are none, ddl, mod (DDL and all other data-modifying statements), or all.
• log_temp_files(default -1) -Log temporary files of this size or larger, in kilobytes.
• log_checkpoints(default off) -Causes checkpoints and restartpointsto be logged in the server log.

also see below

what is postgreSQL

Why postgresSQL

History of PostgreSQL

postgreSQL - PSQL MATA Commands

postgreSQL what is schema?

postgreSQL Connecting to a Database

postgreSQL Create and access Database

How to create user in postgreSQL?

postgreSQL Grant/Revoke Privileges

postgreSQL Tablespaces and datafiles

postgreSQL How to start server and stop server?

postgreSQL perfomance

postgreSQL How to create cluster?

postgreSQL Queries

postgreSQL routine maintenance and proactive-maintanace.

postgresql-logging

postgreSQL routine maintenance and proactive-maintanance

1)Analyze:

syntax: analyze <table-name>
==>Which will updates statistics (system_tables) (or) That autovaccum will update after configured time,Then only we can see updated statistics.

2)Analyze verbose:
syntax:analyze verbose <table-name>
==> shows number of pages and Number of live records(usually deleted rows wont get cleaned up immediately once we call Analyze then only dead rows will cleaned up).

3)vacuum:
Sysntax: Vacuum <table-name>
This will removes dead rows. Main use of vacuum is delete dead row which will increase performance.
How to view hidden(deleted) fields of all tables.
Ex: select c+id,* from emp;
output:
c+id      id
(0,1)      1
(0,2)      2
-----
(0,255) 255
(1,1)   256
Note: If we remove dead rows from  table  we can achieve "good performance".

4)Vacuum full:
vacuum full will gives returns disk space to disk.
Note: In PostgerSQL each row will have trasaction Id:
How to view transcation id of each row of table?
Answer: select xmin,c+id,* from emp;

5)Vacuum freeze:
Under one transaction we can inserts 4 milion.If we insert above 4 milions in this case 4 milion are inserted then very first one will replaced with last row(4 milion +1).
To over come above issue vacuum freeze will be run by autovacuum for every 2 million records.

6)Vacuum analyze:
7)Explain:
It will give only plan time.This won't execute query explain select * from emp;
cost=0.00 .... 1.10 rows=10 width=4
show cpu-tuple-cost;
show seq-page-cost;

8)Explain analyze:
This will execute query.Explain analyze will give planning time and execution time.

9)Autovacuum:
PostgreSQL has an optional but highly recommended feature called autovacuum, whose purpose is to automate the execution of VACUUM and ANALYZE commands.


Data loading:
=========

EDB loader is using to import CSV data.
copy from------>import
copy To--------->Export

Examlpe:
1)copy emp(id,name) to /temp/emp.csv csv Header;
2)copy id-test from 'temp/emp.csv' csv Header;
==>csv is in binary format since this will be 95% faster than insert.

also see below

what is postgreSQL

Why postgresSQL

History of PostgreSQL

postgreSQL - PSQL MATA Commands

postgreSQL what is schema?

postgreSQL Connecting to a Database

postgreSQL Create and access Database

How to create user in postgreSQL?

postgreSQL Grant/Revoke Privileges

postgreSQL Tablespaces and datafiles

postgreSQL How to start server and stop server?

postgreSQL perfomance

postgreSQL How to create cluster?

postgreSQL Queries

postgreSQL routine maintenance and proactive-maintanace.

postgresql-logging

postgreSQL How to create cluster?

PostgreSQL Cluster is nothing but another instance of Database.

Syntax:

initDB -D <direcoty>

Example:

step 1: create directory for table.

    mkdir mynewtable-space

step 2: initDB -D /mynewtable-space

Note: now mynewtable-space folder will have all executable(like pg_ctl) and newly created config files for now cluster. here we can change port for new cluster and other configuration.

also see below

what is postgreSQL

Why postgresSQL

History of PostgreSQL

postgreSQL - PSQL MATA Commands

postgreSQL what is schema?

postgreSQL Connecting to a Database

postgreSQL Create and access Database

How to create user in postgreSQL?

postgreSQL Grant/Revoke Privileges

postgreSQL Tablespaces and datafiles

postgreSQL How to start server and stop server?

postgreSQL perfomance

postgreSQL How to create cluster?

postgreSQL Queries

postgreSQL routine maintenance and proactive-maintanace.

postgresql-logging

How to increase Query Performance

How to get fastness on query? How to get results quickly?

By inceasing work_mem we can acheive fastness on query results

show work_mem;

set work_mem to 1000MB

Increasing work mem to specific user

Alter user <user> set work_mem to '1000MB'

How to increase read performance?

By making below changes in config file we can achieve better read performance

fsynce=false

increase work_mem

increase shared_bufferes

create indexs

How to increase write performance?

By making below changes in config file we can achieve better write performance

decrease checkpoint_timeout

increase shared_buffers

increase wal_buffers

increase work_mem

increase maintenance_work_mem

also see below

what is postgreSQL

Why postgresSQL

History of PostgreSQL

postgreSQL - PSQL MATA Commands

postgreSQL what is schema?

postgreSQL Connecting to a Database

postgreSQL Create and access Database

How to create user in postgreSQL?

postgreSQL Grant/Revoke Privileges

postgreSQL Tablespaces and datafiles

postgreSQL How to start server and stop server?

postgreSQL perfomance

postgreSQL How to create cluster?

postgreSQL Queries

postgreSQL routine maintenance and proactive-maintanace.

postgresql-logging

postgreSQL How to start server and stop server?

How to stop postgreSQL?

./pg_ctl -D ../data stop

How to force stop postgreSQL?
./pg_ctl -D ../data stop -m f --->force stop

How to start postgreSQL?
./pg_ctl -D ../data start

How to restart postgreSQL?
./pg_ctl -D ../data restart

Note: In above samples pg_ctl is a executable which can be found under bin directory. -D stands for directory and ../data is data directory.


Security :
pg_hdb.cfg configuration file will have all clients IP address non listed clients cannot access.

pg_hba.conf can be find under data directory not under bin directory.another config file postgres.cfg where will do postgreSQL configurations.

How to load configuration file without restart with query?
Answer: select pg_reload_conf();

How to rotate db logs?
Answer: select pg_rotate_logfile();

How to view log file location?
Answer: select pg_ls_dir('pg_log');

How to view system tables?
Answer: \d$

also see below

what is postgreSQL

Why postgresSQL

History of PostgreSQL

postgreSQL - PSQL MATA Commands

postgreSQL what is schema?

postgreSQL Connecting to a Database

postgreSQL Create and access Database

How to create user in postgreSQL?

postgreSQL Grant/Revoke Privileges

postgreSQL Tablespaces and datafiles

postgreSQL How to start server and stop server?

postgreSQL perfomance

postgreSQL How to create cluster?

postgreSQL Queries

postgreSQL routine maintenance and proactive-maintanace.

postgresql-logging