Postgres Plus Hot-standby Streaming replication

PPAS

Postgres Plus Advanced Server (PPAS) 9.2

Installation, initial configuration,  Hot-standby Streaming replication

Step 1:-- unzip the installation archive file.

Step 2:-- Make the installer directory your current directory

Note 1:-- while installation when asked for either an oracle or postgresql installation select oracle will give you all of the oracle performance and scability features not found in community postgreSQL.

Note 2:-- Enter the password for the database super user. Be sure remember this you will need the password   later.

Install the PPAS on both the primary and secondary server.

On primary server

Installation  directory :/opt/PostgresPlus/9.2AS

Data Directory : /opt/PostgrePlus/9.2AS/data

WAL Directory : /opt/PostgresPlus/9.2AS/data/pg_xlog

Database  port :5444

Database  Superuser : enterprisedb

Operating System Account: enterprisedb

Database  Service :ppas-9.2

Pg_bouncer: 6432

On secondary server

Installation directory: /opt/PostgresPlus/9.2AS

Data Directory: /opt/PostgrePlus/9.2AS/data

WAL Directory: /opt/PostgresPlus/9.2AS/data/pg_xlog

Database  port :5445

Database  Superuser : enterprisedb

Operating System Account: eEnterprise

Database  Service :ppas-9.2

Pg_bouncer: 6432

Step 3 :--Make changes in the primary server

open the Postgresql.conf configuration file and modify it

#vim  /opt/PostgrePlus/9.2AS/data/postgresql.conf

 listen_addresses = '*'

wal_level = hot_standby

max_wal_senders = 1

wal_keep_segments = 5

rchive_mode = on

archive_command = 'cp %p "/opt/archive/%f"'

step 4: Create an archive directory under the opt

step 5:--change the pg_hba.conf file

#vim pg_hba.conf

host     replication     repuser             Secondary-Server-IP/32        trust

step 6:-- make changes in the stand by server

change in the postgresql.conf  file

#vim  /opt/PostgrePlus/9.2AS/data/postgresql.conf

hot_standby = on

step 7:--copy recovery.conf.sample  as recovery.conf

#cp  /opt/PostgresPlus/9.2AS/share/recovery.conf.sample  /opt/PostgresPlus/9.2AS/data/recovery.conf

#vim /opt/PostgresPlus/9.2AS/data/recovery.conf

standby_mode = on

primary_conninfo = 'host=Primary-Server-IP  port=5444 user=repuser password=admin'

restore_command = 'cp /opt/archive/%f %p'

step 8:-- Create an archive directory in the opt.

step 9:-- Create a replication user as repuser and password

connecting  to the database

#cd  /opt/PostgresPlus/9.2AS/bin

#./psql  -U (username)  -d  (database name)

edb=#create user repuser with replication password  ’**password**’;

(creating a user in the database)

edb=#select  pg_start_backup(‘base backup’)

Meanwhile we need copy all the content of dats directory from primary to secondary .

#cd  /opt/PostgresPlus/9.2AS/data

#rsync –av  --exclude pg_xlog  --exclude  postgresql.conf  --exclude postmaster.pid    .   Primary-Server-IP:/opt/PostgresPlus/9.2AS/data

Edb=#select  pg_stop_backup();

Step 10:--  connect to the database and run the following command to see the current wal  location(on the primary server)

edb=#SELECT  pg_current_xlog_location();(to see the current wal location)

edb=#select * from pg_stat_replication;(to check the status of replication)

step 11:-- connect to the database and u can run (on the standby server)

edb=#SELECT  pg_last_xlog_receive_location();

edb=#SELECT pg_last_xlog_replay_location();

RDBMS and NoSQL

In the market various type of Database options are available like RDBMS, NoSQL, Big Data, Database Appliance, etc. developers can get very confused with all the choice. They do not understand why they should consider a newer, alternative database when RDBMSs have been around for 25+ years. However, many big enterprises are already using alternative databases and are saving money, innovating more quickly, and completing projects.

Relational Database Management System (RDBMS)

RDBMS  is a relational database. It is the standard language for relational database management systems.Data is stored in the form of rows and columns in RDBMS. The relations among tables are also stored in the form of the table.  SQL (Structured query Language) is a programming language used to perform tasks such as update data on a database, or to retrieve data from a database. Some common relational database management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, etc.

Features Of RDBMS

1. SQL databases are table based databases
2. Data store in rows and columns
3. Each row contains a unique instance of data for the categories defined by the columns.
4. Provide facility primary key, to uniquely identify the rows

Limitations for SQL database

Scalability: Users have to scale relational database on powerful servers that are expensive and difficult to handle. To scale relational database it has to be distributed on to multiple servers. Handling tables across different servers is difficult .

Complexity: In SQL server’s data has to fit into tables anyhow. If your data doesn’t fit into tables, then you need to design your database structure that will be complex and again difficult to handle.

NoSQL

 NoSQL commonly referred to as “Not Only SQL”.  With NoSQL, unstructured, schema less data can be stored in multiple collections and nodes and it does not require fixed table sachems, it supports limited join queries, and we scale it horizontally.

Benefits of NoSQL

highly and easily scalable

Relational database or RDBMS databases are vertically Scalable When load increase on RDBMS database then we scale database by increasing server hardware power ,need to by expensive and bigger servers and NoSQL databases are designed to expand horizontally and in Horizontal scaling means that you scale by adding more machines into your pool of resources.

Maintaining NoSQL Servers is Less Expensive

Maintaining high-end RDBMS systems is expensive and need trained manpower for database management but NoSQL databases require less management. it support many Features like automatic repair, easier data distribution, and simpler data models make administration and tuning requirements lesser in NoSQL.

Lesser Server Cost and open-Source

NoSQL databases are cheap and open source. NoSql database implementation is easy and typically uses cheap servers to manage the exploding data and transaction while RDBMS databases are expensive and it uses big servers and storage systems. So the storing and processing data cost per gigabyte in the case of NoSQL can be many times lesser than the cost of RDBMS.

No Schema or Fixed Data model

NoSQL database is schema less so Data can be inserted in a NoSQL database without any predefined schema. So the format or data model can be changed any time, without application disruption.and change management is a big headache in SQL.

Support Integrated Caching

NoSQL database support caching in system memory so it increase data output performance and SQL database where this has to be done using separate infrastructure.

Limitations & disadvantage of NoSQL

1. NoSQL database is Open Source and Open Source at its greatest strength but at the same time its greatest weakness because there are not many defined standards for NoSQL databases, so no two NoSQL databases are equal
2. No Stored Procedures in mongodb (NoSql database).
3. GUI mode tools to access the database is not flexibly available in market
4. too difficult for finding nosql experts because it is latest technology and NoSQL developer are in learning mode

Conclusion

RDBMS and NoSQL both databases are great in data management and both are used to keep data storage and retrieval optimized and smooth. It’s hard to say which technology is better so developer take decision according requirement and situations.

source: http://www.loginradius.com/