How to take Backup and Restore a table with insert script in Postgresql and PGADMIN4
In this article, I'm going to explain how you can get the table data insert script. We can able to import that to another table. We faces 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 backup 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 backup that data and restore it to another table in another database.
In the below, Screenshot you can see that table's create 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 backup is in the first Postgres database, and it has two rows. You can see that in the below screenshot.
Right Click users → click Backup option
How to backup a table in postgres using pgadmin4
Specify Filename and Format in General tab. Remember tar and custom are recommended formats to take dumb and restore operations.
In Data/Objects tab Toggle Only data
In Options tab Select Use Column Inserts
After click Backup button, It generate the file in respective folder:
How to restore backup file in postgresql using pgadmin4
Now Navigate to the table where you want to Restore the Backup
In my Scenario, I navigated to postgres1 database → users table
Right click the table and Click Restore
After you need to select the backup file like below. If you can't able to see the file in that folder switch All files(*.*) in File explorer.
Before restoring the backup, the users table had zero rows.
After 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 get dump of the table in specific path.
You can use the below command to restore the data to table
Explanation for the command-line options:
-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 need to provide the host name or IP address after the -h parameter.
-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.
-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.
-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.
--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.
--column-inserts
Using this parameter in pg_dump will create an individual insert script for each row in the table.
-t pattern or --table=pattern
This parameter is used to dump the table with name matching patterns.
How to take multiple tables backup in PostgreSQL
-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 format
1. 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.
2. 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.
3. 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.
4. 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 plain format backup file generated by PostgreSQL:
change the file format to .txt after that you can open that file in notepad.
Summary:
I hope in this article people can get answers to questions like backup table postgres pgadmin, postgresql backup table data-only, query to create backup table in postgresql, pg_dump table data-only and restore table backup postgresql.