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

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>

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store