How to Backup a Single Table in PostgreSQL Quickly
To backup a single table in PostgreSQL, use the
pg_dump tool with the -t option followed by the table name. For example, pg_dump -U username -d database_name -t table_name > table_backup.sql creates a backup file of that table only.Syntax
The basic syntax to backup a single table in PostgreSQL uses the pg_dump command-line tool with the -t option to specify the table.
pg_dump: PostgreSQL utility to dump database content.-U username: Connect as this database user.-d database_name: The database to connect to.-t table_name: The specific table to backup.> output_file.sql: Redirects output to a file.
bash
pg_dump -U username -d database_name -t table_name > table_backup.sql
Example
This example backs up the table named employees from the database companydb using user postgres. The backup is saved to employees_backup.sql.
bash
pg_dump -U postgres -d companydb -t employees > employees_backup.sql
Common Pitfalls
Common mistakes when backing up a single table include:
- Not specifying the correct table name with
-t, which may cause the entire database to be dumped. - Omitting the database name or user, leading to connection errors.
- Forgetting to redirect output to a file, which prints the dump to the terminal.
- Not having proper permissions to access the database or table.
Always verify the table name and connection details before running the command.
bash
Wrong: pg_dump -U postgres -d companydb > backup.sql Right: pg_dump -U postgres -d companydb -t employees > employees_backup.sql
Quick Reference
| Option | Description |
|---|---|
| -U username | Specify the database user to connect as |
| -d database_name | Specify the database to connect to |
| -t table_name | Specify the single table to backup |
| > output_file.sql | Redirect output to a file to save the backup |
Key Takeaways
Use
pg_dump -t table_name to backup a single table in PostgreSQL.Always specify the database name and user to avoid connection errors.
Redirect the output to a file to save the backup instead of printing to the terminal.
Verify you have the right permissions to access the database and table.
Check the table name carefully to avoid backing up the wrong data.