cross database triggers in postgresql

now we can write Trigger in local database based on trigger event(insert/update/delete) trigger function may execute local/remote database. as everyone knows how to write trigger function execute on local/same database. let us see how can we write trigger function execute on remote database server.

steps
1) create trigger function
2) create trigger
3) execute sql statement for firing trigger event

1) create trigger function

1) Creating trigger function would execute on remote db server table

CREATE OR REPLACE FUNCTION fn_remote_insert() RETURNS TRIGGER
AS
$BODY$
DECLARE
-- declare anyvariable over here
BEGIN
PERFORM
DBLINK_EXEC('dbname=remoteDB host=190.10.1.5','INSERT INTO remoteTbl VALUES('||new.eid||','''||new.ename||''')');
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql'

2) Create trigger on local db server table

CREATE TRIGGER tr_remote_insert
BEFORE INSERT ON table1
FOR EACH ROW EXECUTE PROCEDURE fn_remote_insert()

3) Firing trigger event by insert

INSERT INTO table1 VALUES (986,'employeeName');

after above insert statement it will fire the trigger " tr_remote_insert " and trigger execute procedure, the procedure would insert new row in remote table.

by this way we can also  synchronize two PostgreSQL database server....

Querying cross database

for querying cross database should install dblilnk which is available under postgresql-contrib-xxx package.

installation on unix/debain/boss

$ apt-get install postgresql-contrib-8.3

$ su postgres

$ psql dbName < /usr/share/postgresql/8.3/contrib/dblink.sql

now you could be able to querying from different database or server

1) querying from other database on same server

SELECT *

FROM dblink('dbname=test1','select id,name from emp')

AS emp( id int,ename varchar);

here

• host --- specify the remote server ip
  
• dbname -- specify the database name
  
• port -- specify port no other the default (5432)
  
• username -- specify the username (if you did not specify this, dblink will connect
  
• password -- specify the password ( with current username & password)

since we are using querying on same server we omit others'

also we need to specify output structure of the table like AS emp( id int,ename varchar); with out this we may get error like

" ERROR: a column definition list is required for functions returning "record" "

2) Querying other database from remote server

SELECT *

FROM dblink('dbname=test1 host=192.10.1.5','select id from students')

AS stu(id int);

here we ip address of remote host using 'host' parameter.

3) Bulk insert into local table from remote server

SELECT t1.*

INTO test2

FROM dblink('dbname=saas-spms', 'SELECT * FROM test1)

AS t1(emp_no integer, degree varchar);

here table test2 should be new table, if already exist our database it shows error like : relation "test2" already found.

4) querying into remote server
If we want local table insert/update into remote postgres server
we can try like,

for insert :

select

dblink_exec('dbname=test1 host=192.10.1.5','insert into emp(id,ename) values(8,''employeeName'')')

for delete :

select

dblink_exec('dbname=test1 host=192.10.1.5','delete from emp where id=8')

here
dblink --> connect with remote server to execute the query which is return row (i.e Select )
dblink_exec --> connect to remote server and execute query which is not return row.

Display size of all database in postgresql

pg_database_size(dbname) function used to return size of given database in byte format.we can use pg_size_pretty() function to convert byte into human readable format. i.e.

SELECT pg_size_pretty(pg_database_size('dbname'))

now if you would like display the size of all available database in postgres server


create or replace function pg_function_alldb_size() returns void as $$
declare
pg_cursor_dbsize cursor for select datname from pg_database;
dbname pg_database.datname%type;
size varchar(50);
begin
open pg_cursor_dbsize;
loop
fetch next from pg_cursor_dbsize into dbname;
exit when not found;
select * into size from pg_size_pretty(pg_database_size(dbname));
raise notice 'db name is % and size is % ',dbname,size;
end loop;
return ;
end;
$$ language plpgsql

• pg_cursor_dbsize is cursor which stores the all database name from pg_database table
• pg_size_pretty(pg_database_size(dbname)) it returns size of the database to the size variable.
  
• raise notice - used to display the output.
  

how to backup & restore cross databse PostgreSQL server

backup and restore database from two different server
PostgreSql provide excellent feature like cross database restore, i.e backup database from locale DB and restore to local/remote another DB at same time.

syntax:

for backup & restore in local / same server

pg_dump -d SourceDBName -U userName | psql TargetDBName
for backup from local server & restore to remote server
pg_dump -d SourceDBName -U userName | psql -h RemoteServerIP TargetDBName

note :

• pipe ( | ) symbol used here for direct the output of one command to another.
• -h option used for specify the remote server ip

Cross Table reference in PostgreSQL.
backup and restore the specific table

as same like database we can also backup specific table from local database and restore in to another database either remote/local server.
first we would see how to backup and restore specific database table
for backup:
pg_dump -d DBName -t TableName -U UserName -f TableBackupFileName.sql ( or )pg_dump -d DBName -t TableName -U UserName > targetpath/TableBackupFileName.sql

note:

• -t option used to specify the tablename to backup
• -f option used for specify the target file name where backup would store

for restore
psql -f TableBackupFileName.sql DBName

backup & restore specific table from two different server
now we would see how to backup table from locale server and restore into remote server database

pg_dump -d DBName -t TableName -U UserName | psql -h RemoteServerIP TargetDBName

here TargetDBName is the target database to restore the table. It could be

1. You can create TargetDB while restore.
2. you can restore table already existing TargetDB without any schema definition.
3. you can restore to existing DB with schema definition *

suppose you are trying to restore table to database TargetDBName which is already exist and it might have schema( * ) may through constraint violation error because table may have lot of constraint ( foreign key,check, etc..) . To avoid such error use -c option like..
pg_dump -d DBName -t TableName -U UserName -c | psql -h RemoteServerIP TargetDBName

note :
-c option useful for many reason but here what it do before restore to TargetDB is

• Drop the table with constraint.
• Create table without constraint.
• Insert values into table ( from backup file).
• Create constraint and index etc...

If you are using PostgreSQL 9+, then you would get this error while running the above statement.

ERROR :
/usr/lib/postgresql/9.1/bin/pg_dump: invalid option -- 'd'
Try "pg_dump --help" for more information.



SOLUTION : 
remove the -d option from the  statement ans try,
pg_dump DBName -t TableName -U UserName -c | psql -h RemoteServerIP TargetDBName