Backup a table in postgresql

 SELECT * INTO abc_bk FROM abc;

Open Source Big Data Databases

Cassandra

CouchDB

FlockDB

HBase

Hibari

MongoDB

Neo4j

OrientDB

Riak

Terrstore

Postgresql: Get all tables count from DB

 SELECT schemaname,relname,n_live_tup 

  FROM pg_stat_user_tables 

  ORDER BY n_live_tup DESC;

Postgresql: Get all primary keys from all the tables in DB

SELECT 

kcu.table_schema, kcu.table_name, tco.constraint_name, kcu.ordinal_position AS position, kcu.column_name AS key_column

FROM information_schema.table_constraints tco

JOIN information_schema.key_column_usage kcu 

    ON kcu.constraint_name = tco.constraint_name

    AND kcu.constraint_schema = tco.constraint_schema

    AND kcu.constraint_name = tco.constraint_name

WHERE tco.constraint_type = 'PRIMARY KEY'

ORDER BY 

kcu.table_schema, kcu.table_name, position;

Columns

table_schema - PK schema name

table_name - PK table name

constraint_name - PK constraint name

position - index of column in table (1, 2, ...). 2 or higher means key is composite (contains more than one column)

key_column - PK column name

Postgresql: Find the 'N' th occurrences in a string

 Sample Table:

create table filter_table (filter_type varchar(500));

Insert Query:

insert into filter_table (filter_type) values ('1111111 > 22222 > 3333333> 4444444> 123456789 > abcdefghi > 987654321 > zyxwvuts > 0000000 >');

Query to find the occurrences:

select substring(filter_type from '^(([^ ]* ){2})')

from filter_table

Postgresql: Important Queries

 --To get the list of tables in Postresql:

SELECT * FROM information_schema.tables WHERE table_name = 'tablename'

--To get the list of columns in the Postgresql tables:

SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tablename';

SDLC

SDLC is a process followed for a software project, within a software organization. It consists of a detailed plan describing how to develop, maintain, replace and alter or enhance specific software. The life cycle defines a methodology for improving the quality of software and the overall development process.

The following figure is a graphical representation of the various stages of a typical SDLC.

A typical Software Development Life Cycle consists of the following stages −

Stage 1: Planning and Requirement Analysis

Requirement analysis is the most important and fundamental stage in SDLC. It is performed by the senior members of the team with inputs from the customer, the sales department, market surveys and domain experts in the industry. This information is then used to plan the basic project approach and to conduct product feasibility study in the economical, operational and technical areas.

Planning for the quality assurance requirements and identification of the risks associated with the project is also done in the planning stage. The outcome of the technical feasibility study is to define the various technical approaches that can be followed to implement the project successfully with minimum risks.

Stage 2: Defining Requirements

Once the requirement analysis is done the next step is to clearly define and document the product requirements and get them approved from the customer or the market analysts. This is done through an SRS (Software Requirement Specification) document which consists of all the product requirements to be designed and developed during the project life cycle.

Stage 3: Designing the Product Architecture

SRS is the reference for product architects to come out with the best architecture for the product to be developed. Based on the requirements specified in SRS, usually more than one design approach for the product architecture is proposed and documented in a DDS - Design Document Specification.

This DDS is reviewed by all the important stakeholders and based on various parameters as risk assessment, product robustness, design modularity, budget and time constraints, the best design approach is selected for the product.

A design approach clearly defines all the architectural modules of the product along with its communication and data flow representation with the external and third party modules (if any). The internal design of all the modules of the proposed architecture should be clearly defined with the minutest of the details in DDS.

Stage 4: Building or Developing the Product

In this stage of SDLC the actual development starts and the product is built. The programming code is generated as per DDS during this stage. If the design is performed in a detailed and organized manner, code generation can be accomplished without much hassle.

Developers must follow the coding guidelines defined by their organization and programming tools like compilers, interpreters, debuggers, etc. are used to generate the code. Different high level programming languages such as C, C++, Pascal, Java and PHP are used for coding. The programming language is chosen with respect to the type of software being developed.

Stage 5: Testing the Product

This stage is usually a subset of all the stages as in the modern SDLC models, the testing activities are mostly involved in all the stages of SDLC. However, this stage refers to the testing only stage of the product where product defects are reported, tracked, fixed and retested, until the product reaches the quality standards defined in the SRS.

Stage 6: Deployment in the Market and Maintenance

Once the product is tested and ready to be deployed it is released formally in the appropriate market. Sometimes product deployment happens in stages as per the business strategy of that organization. The product may first be released in a limited segment and tested in the real business environment (UAT- User acceptance testing).

Then based on the feedback, the product may be released as it is or with suggested enhancements in the targeting market segment. After the product is released in the market, its maintenance is done for the existing customer base.

SDLC Models

There are various software development life cycle models defined and designed which are followed during the software development process. These models are also referred as Software Development Process Models". Each process model follows a Series of steps unique to its type to ensure success in the process of software development.

Following are the most important and popular SDLC models followed in the industry 

Waterfall Model

Iterative Model

Spiral Model

V-Model

Big Bang Model

Other related methodologies are Agile Model, RAD Model, Rapid Application Development and Prototyping Models.