Monthly Archives: June 2010

PostgreSQL: Granting permission on any schema and its tables

$ more grant_schema_table.sh #Name           grant_schmea_table.sh #Author         Deepak Murthy #Created        20100113 #Version        1.0 #************************************************************************* #Description #This script is for granting permission on any schema and its … Continue reading

Posted in Uncategorized | Leave a comment

PostgreSQL: Granting owner on all tables in a schema

$more grant_owner.sh # execute: ./grant_owner.sh <schema> <username> <database name> <port number> # example: ./grant_owner.sh sandbox_fanadmin dmurthy fimdwh 5432 echo “grant usage on schema $1 to $2; set search_path=$1; \d” |psql -U gpadmin -p $4 -t -A -d $3|grep $1|cut -d”|” … Continue reading

Posted in Uncategorized | Leave a comment

Postgresql – Revoking user permission on tables and views

#example 1 #  revoke_schema_access.sh pnp role_pnp_readonly pnd select 5432 #example 2 #  revoke_schema_access.sh pnp role_pnp_readwrite pnd select,insert,update,delete 5432 echo “revoke create,usage on schema $1 from $2; set search_path=$1; \d” |psql -U gpadmin -p $5 -t -A -d $3|grep $1|cut -d”|” … Continue reading

Posted in Uncategorized | Leave a comment

Pgpool Installation

#– make sure both Postgres databaes have the same data. (pg_dumpall) #—————————————————————————— #–***Installation*** — Make sure that PGDATA is properly set ex: PGDATA=/mnt/data/pns/pgsql8.3.3 EXPORT PGDATA — Make sure the PATH has exact postgres binary information ex: PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/pgsql/8.3.3/bin/ EXPORT PATH ./configure … Continue reading

Posted in Uncategorized | Leave a comment

PostgreSQL Installation

## Postgres Installation steps ## Make sure Yum-y install readline-devel Yum install zlib-devel and gcc packages are installed already Login as Postgres $ cd /home/postgres/postgresql-8.3.3 $ ./configure –prefix=”/usr/postgres/8.3.3″ $ gmake # $ gmake check  — If required run this $ … Continue reading

Posted in Uncategorized | 2 Comments

Port Forwarding and connecting to PostgreSQL DB using Windows Pgadmin

I.                  Create a port forwarding connection to the DB server A.               Open a terminal window (CYGWIN – tool; windows only) 1.                  Click on CYGWIN icon 2.                  If no icon, install a)                  Search for CYGWIN using goggle b)                  Find install page … Continue reading

Posted in Uncategorized | Leave a comment

Postgresql: DETERMINING SIZE OF DATABASE, SCHEMA, TABLES, AND GEOMETRY

How to determine the size of a database on disk SELECT pg_size_pretty(pg_database_size(‘somedatabase’)) As fulldbsize; How to determine the size of a database table on disk NOTE: There are two functions in PostgreSQL – pg_relation_size and pg_total_relation_size. The pg_relation_size just measures … Continue reading

Posted in Uncategorized | Leave a comment