How to take backup and restore table in PostgreSQL
In this article, we are going to how to take backup and restore table in PostgreSQL. We use pg_dump to take backup of table wiht –table option and psql or pg_restore tools are used to restore the table using existing backup.
1. Syntax to backup table data:
pg_dump -Fc --data-only -h -p -U -W -d -t
> /path_and_filename_forbackup
2. Syntax to Restore table data:
Before we restore we need to connect to the database and truncate the table to clear all the data:
Where --data-only dumps only the data, not the schema -h specify the host you're connecting to -p specify the port you're connecting to -U specify the database user -W prompt for password -d the database you're exporting from -t the table you're exporting from.
Examples:
1. Take backup of table 'public.order_details' from the database 'northwind' to the backup file public_order_details.sql in the current working directory.
To execute below backup command, we should run using user postgres or user with executable permission on pg_dump tool.