Backup and Restore Postgresql database and tables

1. Take backup of whole database

$pg_dump -U username -d database_name > backup.sql

2. Take backup of single table from postgresql database, along with create table command. syntax:
$pg_dump -h hostname -U username -d dbname -t table_name > backup.sql

$pg_dump -h 10.10.192.8 -U myuser -d mydb -t public.student > backup1.sql

3. Take backup of single table from postgresql database, only insert queries

$pg_dump -h 10.10.192.8 -U myuser -d mydb -a -t public.student > backup2.sql

4. take backup of multiple table insert only queries

$pg_dump — column-insets -a -t table1 -t table2 -t table3 > backup3.sql
Note: you can use wildcard to select table name with -t parameter

5. To restore database backup file

$psql -U username -d dbname -h hostname < backup.sql
or
$psql -h 10.10.192.9 -U myuser2 -d mydb2 -f backup2.sql

6. Copy a table data from one database to another database

SYNTAX:
pg_dump -U <username> -h <source ip> -a -t <table name> <source db name> | psql -U <target username> -h <target ip> <target db>
Note : At password prompt , first provide the password of destination db user, then source db user.
Eg.
pg_dump -U myuser1 -a -t table1 db1 | psql -U myuser2 -h 192.168.3.4 db2