LIST OUT ALL (FORIEN KEY,PRIMARY KEY, UNIQUE KEY) CONSTRAINTS WITH REFERENCED TABLE & FIELD :

Here is the simple utility query for list out all the constraint( CHECK, UNIQUE, PRIMARY & FOREIGN KEY) their name, table name,column name from where constraint created and references table name with column name.

from this query you can also get hierarchical list of all tables

SELECT tc.constraint_name,
tc.constraint_type,
tc.table_name,
kcu.column_name,
tc.is_deferrable,
tc.initially_deferred,
rc.match_option AS match_type,

rc.update_rule AS on_update,
rc.delete_rule AS on_delete,
ccu.table_name AS references_table,
ccu.column_name AS references_field
FROM information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu
ON rc.unique_constraint_catalog = ccu.constraint_catalog
AND rc.unique_constraint_schema = ccu.constraint_schema
AND rc.unique_constraint_name = ccu.constraint_name

WHERE lower(tc.constraint_type) in ('foreign key')

here you can comment WHERE condition to list out all constraint, else it ll list only FOREIGN KEY constraint.

By running above utility you can get foreign key hierarchy in postgresql.

================================================

Below query will return the referencing table and field information for the parent table test_master.

Get all child table for the given parent table

SELECT *
FROM information_schema.table_constraints tc
right JOIN information_schema.constraint_column_usage ccu
ON tc.constraint_catalog = ccu.constraint_catalog
AND tc.constraint_schema = ccu.constraint_schema
AND tc.constraint_name = ccu.constraint_name
and ccu.table_name in ('test_master')
WHERE lower(tc.constraint_type) in ('foreign key');

To find all child tables that belong to a parent table you can use above query, change the table (test_master) .

Passwordless postgresql login

We can log in postgres without promoting/enter password through psql.

syntax:

sudo -u user_name psql db_name

i.e
sudo -u postgres psql postgres

postgres -- database name, if you omit this by default it will log in with postgres database only

may forget the password simply enter into psql mode simply by above command and alter user with new password by

alter user user_name with password 'new_password'


note:

use sudo for log in if you face any issue by log in throuogh

su postgres
psql

FATAL: bogus data in lock file in postgresql

some times after postgres server installed in your system you may get error when start up your database server like FATAL : Postgrersql server didn't start see log file for more info "/usr/local/pgsql/pg_log/startup.log", in log file if you could see error like
FATAL: bogus data in lock file "/tmp/.s.PGSQL.5432.lock": ", its simply because of fake lock file,
just remove the lock file from the location, start the server now it will work.

note: give full permission to /tmp folder ( chmod -R 777 /tmp).

postmaster is running but psql not running it shows
command not found

Check if psql starts with a fully quallified path:

find / -name psq
/opt/PostgreSQL/8.4/bin/psql -- ENTER

now it may solve ur issue's  

FATAL: could not create shared memory segment:

ERROR

Starting PostgreSQL 8.3 database server: mainThe PostgreSQL server failed to start. Please check the log output: 2010-09-29 11:49:05 UTC LOG: could not load root certificate file "root.crt": no SSL error reported 2010-09-29 11:49:05 UTC DETAIL: Will not verify client certificates. 2010-09-29 11:49:05 UTC FATAL: could not create shared memory segment: Cannot allocate memory 2010-09-29 11:49:05 UTC DETAIL: Failed system call was shmget(key=5433001, size=29368320, 03600). 2010-09-29 11:49:05 UTC HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 29368320 bytes), reduce PostgreSQL's shared_buffers parameter (currently 3072) and/or its max_connections parameter (currently 103). The PostgreSQL documentation contains more information about shared memory configuration. failed!

SOLUTION

Here in error itself clearly specified the solution what we have to do..
just reduce the shared buffer ( shared memory ) size which value defined in RESOURCE USAGE part of the postgresql.conf file

in unix system path is /etc/postgresql/8.3/main/postgresql.conf

#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 24MB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB

--------------------------------------------------------------------------------
once edit this value restart the server..