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