PostgreSQL basics

To login to prompt #psql -h myhost -d mydb -U myuser -W To create user psql#CREATE USER anoop WITH password 'tmppassword'; To create database  CREATE DATABASE db1 WITH OWNER anoop; List databases \l List all the tables \d Turn on timing \timing      


Know the size of a postgresql schema

Sometimes we want to know the current size of a certain schema in our Database. For  that create the following function. CREATE OR REPLACE FUNCTION pg_schema_getsize(text) returns bigint AS $$ SELECT sum(pg_relation_size(schemaname || '.' || tablename))::bigint FROM pg_tables WHERE schemaname = $1 $$ LANGUAGE sql; To execute this: select pg_size_pretty(pg_schema_getsize('backup_tables')); pg_size_pretty —————- 36 GB (1 … Continue reading Know the size of a postgresql schema

How to see active SQL queries and open connections in PostgreSQL/HAWQ

Login to the PostgreSQL command-line interface psql -U [username] [database_name] 2. Run the following query: SELECT datname,usename,procpid,client_addr,waiting,query_start,current_query FROM pg_stat_activity;