How to take backup and restore table in PostgreSQL | PostgreSQL Tutorials and Solutions

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:

psql -h -p -U -W -d -t < /path_and_filename_ofbackupfile

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.

pg_dump -h localhost -d northwind --table=public.order_details >public_order_details.sql;

How to take backup and restore table in PostgreSQL

2. Restore table 'public.order_details' to the database 'northwind' from the backup file public_order_details.sql in the current working directory.

Before you restore you need to connect to the database and truncate the table to clear all the data:

northwind=# drop table public.order_details;
DROP TABLE

Now, restore the table using below command.

psql -h localhost -d northwind --table=public.order_details  < public_order_details.sql;

So in this article, we have seen How to take backup and restore table in PostgreSQL with syntax and examples.

Related Posts: