Backup and Restore Postgresql database and tables
1 min readJan 6, 2021
- 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.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