How to take Backup and Restore a Table in PostgreSQL

Introduction

In this article, I will explain how you can get the table data insert script. We can able to import that to another table. We face this Scenario while moving the data to multiple environment databases. Because we have some table data, we need to have similar table data in all environments. That is where we need these backup tables and restore processes. And we can also take the data insert scripts like the below screenshot.

My Configuration

• PSQL ( PostgreSQL ) 15.3.
• PGADMIN4 Version 7.1.

Here I'm explaining how we can back up a table and restore it to another table using two methods.

First Method

Through the GUI using PGADMIN4, it's an easy process for everyone to take a backup of a table and easily restore it to any table they want.

I have two databases; one is named Postgres. In that database, I created one table named "users". That has three columns named user_name, password, and phone_number. It has two rows of data. I need to back up that data and restore it to another table in another database. 

In the below, Screenshot you can see that table's created script.

In this example, the second table has an extra column named "age", but we can still restore the data. It's in another database named Postgres1.  

The table I need to back up is in the first Postgres database, and it has two rows. You can see that in the below screenshot.

Right Click users → click the Backup option

How to back up a table in PostgreSQL using pgadmin4?

Specify Filename and Format in the General tab. Remember, tar and custom are recommended formats to take dumb and restore operations.

In the Data / Objects tab, Toggle Only data

In the Options tab, Select Use Column Inserts 

After clicking the Backup button, It generates the file in the respective folder.

How to restore a backup file in PostgreSQL using pgadmin4?

Now Navigate to the table where you want to Restore the Backup.

In my Scenario, I navigated to the postgres1 database → users table.

Right-click the table and Click Restore.

After, you need to select the backup file like the one below. If you can't able to see the file in that folder, switch All files(*.*) in File Explorer.

Before restoring the backup, the user's table had zero rows.

After the restore was completed, the data was restored in that table

Second Method

How to take backup and restore tables in PostgreSQL using the CLI?

You can use the below command to dump the table in a specific path.

pg_dump --file "C:\\Users\\Sri\\Documents\\DbBackups\\user_table_backup" -h "localhost" -p "5432" -U "postgres"  --verbose -F p --data-only --column-inserts  --table "public.users" "postgres"
pg_dump --file "Your Filename" -h "Your Hostname" -p "Your Port" -U "username" --verbose -F p --data-only --column-inserts  --table "Your table name" "Your DB name"

You can use the below command to restore the data to the table.

pg_restore --host "localhost" --port "5432" -U "postgres" --dbname "postgre1" --data-only --verbose --schema "public" --table "users" "C:\\Users\\Sri\\Documents\\DbBackups\\user_table_backup"
pg_restore --host "Your Hostname" --port "Your Port" -U "Your Username" --dbname "Your Db Name" --data-only --verbose --schema "Your Schema" --table "Your Table Name" "Your Backup File Name with Path"

The explanation for the command-line options

1. -h or --host: You can specify the server or machine name where PostgreSQL is installed. It allows you to connect to a remote PostgreSQL server from the command line. You must provide the hostname or IP address after the -h parameter.
2. -p or port: The port parameter is used to specify the port number on which the PostgreSQL server is listening for incoming connections. The default port number for PostgreSQL is 5432.
3. -U or username: The username parameter is used to specify the username for the database connection. You can specify the username using the -U or --username options.
4. -v or --verbose: The --verbose option is used in PostgreSQL command-line utilities to enable verbose output. Additional data about the execution process, including the SQL queries being performed, informational messages, and any errors or warnings encountered, is provided in the verbose output. It may be useful for debugging, comprehending the utility's operations, or getting more context while the operation is being carried out.
5. --data-only: Using this parameter in pg_dump, it selects all the table data in that backup file. The data inside the file looks like just data fetched using a select query.
6. --column-inserts: Using this parameter in pg_dump will create an individual insert script for each row in the table.
7. -t pattern or --table=pattern: This parameter is used to dump the table with name-matching patterns.

How to take multiple tables backups in PostgreSQL?

pg_dump --file "Your Filename" -h "Your Hostname" -p "Your Port" -U "username" --verbose -F p --data-only --column-inserts 
--table "Your table name" --table "Your table name2" --table "Your table name3" "Your DB name"

• -f or --file: This parameter is used to send the output to a specific file. Here you can define a path with a file name.
• -F or --format: Using this parameter, you can define the format of the backup file. PostgreSQL offers different types of file formats 
• p or plain: This is the plain-text format. It creates an editable, plain-text SQL script that humans can read and write. The database structure can be recreated, and the data inserted using SQL instructions.
• c or custom: This format is compressed by default, specifically for PostgreSQL. It is a suitable format for pg_restore. It includes both the database schema and the data in binary format. It provides a more efficient and faster backup and restore process compared to the plain-text format.
• d or directory: This format creates a directory structure with separate files for each database object (tables, indexes, etc.). It also includes a separate file for the data. This format is useful for incremental backups or when you need to selectively restore specific objects.
• t or tar: The tar format is compatible with the directory format. A valid directory format archive can be created by extracting a tar-format archive. Compression is not supported by the tar format.
• --schema: This parameter is used to specify the particular schema for backup or restoring in PostgreSQL.

Extra Tip

How to open a plain format backup file generated by PostgreSQL?

Change the file format to .txt. After that, you can open that file in Notepad.