In this article, we will see how to take backup of a table(s) in PostgreSQL. We can take the backup of single or multiple tables using pg_dump command with –table or -t option.
Syntax to take backup of table(s) in PostgreSQL:
Following syntax is used to take backup of table with table definition:
pg_dump -d databasename --table=yourTable --column-inserts > file_name.sql
Following syntax is used to take backup of table data only. But, not definition.
pg_dump --table=yourTable --data-only --column-inserts > file_name.sql
Examples:
1. Take backup of table ‘students’ of schema ‘public’ from database ‘r2schools’ with table definition.
pg_dump -d r2schools --table=public.students --column-inserts >students_withdef.sql;
2. Take backup of table ‘students’ of schema ‘public’ from database ‘r2schools’ without table definition.
pg_dump -d r2schools --table=public.students --data-only --column-inserts >students_withoutdef.sql;
3. If we want to take multiple tables backup with tables defintiion, use –table, –table as shown below syntax:
pg_dump -d r2schools --table=public.students --table=public.test --column-inserts >students_test_withdef.sql;
4. If we want to take multiple tables backup without tables defintiion, use –table, –table as shown below syntax:
pg_dump -d r2schools --table=public.students --table=public.test --data-only --column-inserts >students_test_withoutdef.sql;
