Backup and Restore Postgresql database and tables

$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.sqlNote: 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

--

--

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
Himanshu Pratap

Himanshu Pratap

System Administrator and Full stack web developer.