How to back up one table in a postgres database?

Question

Is there a way to back up one table in a database using postgres? And How? Does this also work with the pg_dump command?

Answer 1

Use --table to tell pg_dump which table to back up:

 pg_dump --host localhost --port 5432 --username postgres --format plain --ignore-version --verbose --file "<abstract_file_path>" --table public.tablename dbname 

Answer 2

If you are on Ubuntu,

• Login to your postgres user sudo su postgres
• pg_dump -d <database_name> -t <table_name> > file.sql

Make sure you run the command in which the postgres user has write permissions (example: /tmp )

Edit

If you want to reset .sql on another computer, you may need to skip the owner information stored in the .sql file.

You can use pg_dump --no-owner -d <database_name> -t <table_name> > file.sql

Answer 3

pg_dump -h localhost -p 5432 -U postgres -d mydb -t my_table> backup.sql

You can back up one table, but I would suggest backing up the entire database and then restoring any table you need. It is always good to have a backup of the entire database.

9 ways to use pg_dump

Answer 4

If you prefer a graphical user interface, you can use pgAdmin III (Linux / Windows / OS X). Just right-click on the table of your choice, then select "backup". It will create the pg_dump command for you.

Answer 5

In addition to Frank Heiken's answer, if you want to use INSERT instead of copy from stdin , you should specify the --inserts flag

pg_dump --host localhost --port 5432 --username postgres --format plain --verbose --file "<abstract_file_path>" --table public.tablename --inserts dbname

Please note that I have the --ignore-version flag --ignore-version because it is deprecated.