How to Rebuild Primary key constraints or Indexes in Postgresql 8.4 +

1)  Verified the two tables relations and check the data on those table. 
    If you required take table dump backup of these two tables.

test=# create table table_primary(id int primary key,name text);
NOTICE:  CREATE TABLE / PRIMARY KEY 

test=# create table table_foreign(id int references table_primary(id),location varchar);
CREATE TABLE

test=# \d table_primary
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "table_primary_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# \d table_foreign 
       Table "public.table_foreign"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 id       | integer           | 
 location | character varying | 
Foreign-key constraints:
    "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# insert into table_primary values (1,'RC');
INSERT 0 1
test=# insert into table_primary values (2,'RK');
INSERT 0 1
test=# insert into table_primary values (3,'Balu');
INSERT 0 1
test=# insert into table_primary values (4,'mahi');
INSERT 0 1
test=# insert into table_foreign values (1,'HYD');
INSERT 0 1
test=# insert into table_foreign values (2,'PUNE');
INSERT 0 1
test=# insert into table_foreign values (3,'PUNE');
INSERT 0 1
test=# insert into table_foreign values (4,'PUNE');
INSERT 0 1
test=# select * from table_primary ;
 id | name 
----+------
  1 | RC
  2 | RK
  3 | Balu
  4 | mahi
(4 rows)

test=# select * from table_foreign ;
 id | location 
----+----------
  1 | HYD
  2 | PUNE
  3 | PUNE
  4 | PUNE

2) Add new unique index before deleting existing primary key constraint,once it is done queries will start using new index.

test=# create unique index CONCURRENTLY unique_table_primary_id  on table_primary(id);
CREATE INDEX

test=# \d public.table_primary
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "table_primary_pkey" PRIMARY KEY, btree (id)
    "unique_table_primary_id" UNIQUE, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)


3)  Drop  existing primary key constraint,it will also dependent foreign key constraints also.
And then add the new primary key constraint and foreign key constraints those tables.

test=# begin ;
BEGIN
test=# alter table table_primary  drop constraint table_primary_pkey  CASCADE;  alter table table_foreign add  FOREIGN KEY (id) REFERENCES table_primary(id);
NOTICE:  drop cascades to constraint table_foreign_id_fkey on table table_foreign
ALTER TABLE
ALTER TABLE
test=# commit ;
COMMIT

5) verify and  make sure that you have the table definitions and contents of data in table,what you have before the operation.

test=# \d table_primary 
 Table "public.table_primary"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
 name   | text    | 
Indexes:
    "unique_table_primary_id" UNIQUE, btree (id)
Referenced by:
    TABLE "table_foreign" CONSTRAINT "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# \d public.table_foreign
       Table "public.table_foreign"
  Column  |       Type        | Modifiers 
----------+-------------------+-----------
 id       | integer           | 
 location | character varying | 
Foreign-key constraints:
    "table_foreign_id_fkey" FOREIGN KEY (id) REFERENCES table_primary(id)

test=# select * from table_primary ;
 id | name 
----+------
  1 | RC
  2 | RK
  3 | Balu
  4 | mahi
(4 rows)

test=# select * from table_foreign ;
 id | location 
----+----------
  1 | HYD
  2 | PUNE
  3 | PUNE
  4 | PUNE
(4 rows)

6) Now verify the tables primary and foreign key rules are working or not?

test=# insert into table_foreign values (10,'id');

ERROR:  insert or update on table "table_foreign" violates foreign key constraint "table_foreign_id_fkey"

DETAIL:  Key (id)=(10) is not present in table "table_primary".

test=# insert into table_primary values (1,'RC');

ERROR:  duplicate key value violates unique constraint "unique_table_primary_id"

DETAIL:  Key (id)=(1) already exists.

test=# insert into table_primary values (null,'RC');

ERROR:  null value in column "id" violates not-null constraint

7)  How to Add new column to existing composite Primary key : 


i) master table is having  composite PK with two columns.

postgres=# \d+ master1
                           Table "public.master1"
    Column    |  Type   | Modifiers | Storage  | Stats target | Description
--------------+---------+-----------+----------+--------------+-------------
 id           | integer | not null  | plain    |              |
 name         | text    | not null  | extended |              |
 created_date | date    |           | plain    |              |
 loc          | text    |           | extended |              |
Indexes:
    "master1_pkey" PRIMARY KEY, btree (id, name)
Child tables: child100,
              child200
Has OIDs: no

2)  drop existing Pk and  add new index as PK.

postgres=# Begin;

 create unique index CONCURRENTLY on master1 (id,name,created_date);
alter table table_primary  drop constraint table_primary_pkey  CASCADE;

alter table master1  add CONSTRAINT PK_my_table PRIMARY KEY USING INDEX master1_id_name_created_date_idx;

commit;

NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "master1_id_name_created_date_idx" to "pk_my_table"

ALTER TABLE

4) Verify the Table 

postgres=# \d+ master1 

                           Table "public.master1"

    Column    |  Type   | Modifiers | Storage  | Stats target | Description 

--------------+---------+-----------+----------+--------------+-------------

 id           | integer | not null  | plain    |              | 

 name         | text    | not null  | extended |              | 

 created_date | date    | not null  | plain    |              | 

 loc          | text    |           | extended |              | 

Indexes:

    "pk_my_table" PRIMARY KEY, btree (id, name, created_date)

Child tables: child100,

              child200

Has OIDs: no

How to get all databse schemas from Postgresql cluster

1)  Configure the dblink contrib module to the cluster.

 And create the extension on all database.

Postgres=# create extension dblink ;
CREATE EXTENSION

DB1=# create extension dblink ;
CREATE EXTENSION

DB2=# create extension dblink ;
CREATE EXTENSION

2)  Create the table to store ,output of your function.
postgres=# create table all_db_schemas(databasename name,schemaname name);
CREATE TABLE


3) Create the function on Postgres database.

create or replace function get_all_db_schemas() returns text as $$
declare
r record;
r1 record;
dbname1 name;
schemaname1 name;
done text default 'successes';
stmt1 text;
stmt3 text;
begin
for r in select datname from pg_stat_database
loop
dbname1 :=r.datname;
CONTINUE WHEN dbname1='template0';
stmt1 :='select * from dblink('
|| ''''
|| 'dbname='
|| dbname1
|| ''''
|| ','
|| ''''
|| 'select distinct schemaname from pg_statio_user_tables'
|| ''''
|| ') as t1(schemaname name)'
|| ';';
for r1 in execute stmt1
loop
schemaname1 :=r1.schemaname;
stmt3 := 'insert into all_db_schemas(databasename,schemaname) values ('
|| ''''
||dbname1
|| ''''
|| ','
|| ''''
|| schemaname1
|| ''''
|| ')'
|| ';' ;
execute stmt3;
end loop;
end loop;
return done;
end;
$$ language plpgsql;


4) Execute the function to get to collect  all the schemas from all the databases on cluster.

postgres=# select get_all_db_schemas();
 get_all_db_schemas
--------------------
 successes
(1 row)

5) Check the data got inserted into table or not?
postgres=# select * from all_db_schemas;
 databasename | schemaname
--------------+------------
 postgres     | public
 postgres     | s1
 db1            | public
 db2            | public
(4 rows)