Create BackUp and Restore Postgres Sql through dump command

using pg_dump, you can backup a local database and restore it on a remote database at the same time,using a single command
For the impatient, here is the quick snippet of how backup and restore postgres database using pg_dump and psql:

Backup:  $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}
Restore: $ psql -U {user-name} -d {desintation_db}-f {dumpfilename.sql}

How to Backup Postgres DataBase  

 1. Backup a single postgres database

This example will backup erp database that belongs to user vivek, to the file mydb.sql  

$ pg_dump -U vivek erp -f mydb.sql

It prompts for password, after authentication mydb.sql got created with create table, alter table and copy commands for all the tables in the erp database. Following is a partial output of mydb.sql showing the dump information of employee_details table.

--
-- Name: employee_details; Type: TABLE; Schema: public; Owner: vivek; Tablespace:
--

CREATE TABLE employee_details (
employee_name character varying(100),
emp_id integer NOT NULL,
designation character varying(50),
comments text
);

ALTER TABLE public.employee_details OWNER TO vivek;

--
-- Data for Name: employee_details; Type: TABLE DATA; Schema: public; Owner:vivek--
COPY employee_details (employee_name, emp_id, designation, comments) FROM stdin;vivek 1001 trainer mithilesh 1002 author abhishek 1003 reader
\.
--
-- Name: employee_details_pkey; Type: CONSTRAINT; Schema: public; Owner: vivek; Tablespace:
--
ALTER TABLE ONLY employee_details

ADD CONSTRAINT employee_details_pkey PRIMARY KEY (emp_id);

2. Backup all postgres databases

 To backup all databases, list out all the available databases as shown below. 

Login as postgres / psql user:

$ su postgres

Verify the backup:
Verify whether all the databases are backed up,

$ grep "^[\]connect" all.sql
\connect article
\connect backup
\connect erp
\connect vivek

3. Backup a specific postgres table

$ pg_dump --table products -U vivek article -f onlytable.sql

To backup a specific table, use the –table TABLENAME option in the pg_dump command. If there are same table names in different schema then use the –schema SCHEMANAME option

How To Restore Postgres Database

1. Restore a postgres database

$ psql -U erp -d erp_devel -f mydb.sql

This restores the dumped database to the erp_devel database.

Restore error messages
While restoring, there may be following errors and warning, which can be ignored.

psql:mydb.sql:13: ERROR:  must be owner of schema public
psql:mydb.sql:34: ERROR:  must be member of role "vivek"
psql:mydb.sql:59: WARNING:  no privileges could be revoked
psql:mydb.sql:60: WARNING:  no privileges could be revoked
psql:mydb.sql:61: WARNING:  no privileges were granted
psql:mydb.sql:62: WARNING:  no privileges were granted

 2. Backup a local postgres database and restore to remote server using single command:

$ pg_dump dbname | psql -h hostname dbname

The above dumps the local database, and extracts it at the given hostname.

 3. Restore all the postgres database

$ su postgres
$ psql -f alldb.sql

 4. Restore a single postgres tableThe following psql command installs the product table in the vivek database.

$ psql -f producttable.sql vivek