Postgres Migration

The server is ec2 with c5.xlarge The data in the row is exceeding 1 gb size So is the reason for this error ‘Invalid size memory allocation ‘ How to fix ? Is any configuration required to change ? Solution …

I am facing one issue. I have 3 IP. 1,2 are master and slave and 3 for pgpool. My application direct hit to 3 ,and 3 distribute the connection between master and slave. But in my web application have one …

How to Clear Cache in Linux? Every Linux System has three options to clear cache without interrupting any processes or services. 1. Clear PageCache only. # sync; echo 1 > /proc/sys/vm/drop_caches 2. Clear dentries and inodes. # sync; echo 2 > /proc/sys/vm/drop_caches …

1.MORE I/O POSTGRESQL KILLING ALL IDLE CONNECTION FOR MONITORING PURPOSES: This following script is used for killing all idle connection and you can use it as a monitoring puposes like how many idle query is running for a hour and …

A common issue with databases: The guy at floor 2 did some work on the database and then rashly left for lunch because the lady on floor 1 is already waiting for him. Unfortunately he did not close his transaction …

Active: query is running and session is hitting every threshold time, Idle in transaction: query is completed,and waiting for commit, session is hitting every threshold period. Idle in transaction (aborted): last or some statement was aborted due to network or syntax error (consider …

This script is mostly used for will display  what are the postgresql query is running more than one minutes and it will display query with PID using this PID you can kill what query is taken more CPU utilization and …

Definition: VACUUM reclaims storage occupied by dead tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to …

1.Script for vacuum,analyze and Reindexing  the database cat /home/script/vaccum_analyze_reindex.sh #!/bin/bash PGHOME=/opt/PostgreSQL/9.3/bin LOG=/backups/logs/igr-vac-logs LOGFILE=vacuum_analyze_`date +”%Y-%m-%d-%T”`.log REINDEXLOG=reindex_`date +”%Y-%m-%d-%T”`.log export PGOPTIONS=’-c vacuum_cost_delay=10 -c maintenance_work_mem=5GB’ export PGPASSWORD=postgres $PGHOME/vacuumdb -d db_name -j 5 -p 5432 -z >> $LOG/$LOGFILE 2>> $LOG/$LOGFILE $PGHOME/reindexdb -d db_name -p 5432 …

The REINDEX command rebuilds one or more indices, replacing the previous version of the index. REINDEX can be used in many scenarios, including the following (from Postgres documentation): An index has become corrupted, and no longer contains valid data. Although in theory …

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index …

Explain plan is mainly used to how Postgres will execute given a query and also Understanding this tells you how you can optimize your database with indexes to improve performance Most commonly, explain is run on SELECT statements. However, you …

This script is useful for what are the query is running more than ‘x’ minutes(your input values) and that query status,you can list out the query as per your timing and query status bash-4.1$ cat bash-4.1$ HOSTNAME=`hostname` PSQL=”/opt/PostgreSQL/9.3/bin/psql” PORT=5432 HOST=”localhost” …

1.DDL-Data Definition Language, which is used to create and modify the structure of database objects in database. It’s a auto commit. automatically saved when you create,alter,drop,truncate,comment,rename commands issued ALTER – alters the structure of the existing database CREATE – to create database …