307

Is there a way to create a backup of a single table within a database using postgres? And how? Does this also work with the pg_dump command?

Improve this question

8 Answers 8

Reset to default
411

Use --table to tell pg_dump what table it has to backup:

pg_dump --host localhost --port 5432 --username postgres --format plain --verbose --file "<abstract_file_path>" --table public.tablename dbname
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

And how to restore that Backup file. (.backup), I used pg_restore --host localhost --port 5432 --username postgres --dbname "anydb" --table public.tablename -Ft --verbose "/path/filename.backup" but it came out: pg_restore: [tar archiver] corrupt tar header found in --
psql -U username -d database -1 -f your_dump.sql
what did you mean: abstract_file_path?
I'm not sure why this answer was up voted, people click +1 without inspecting things themselves or trying. The problem with this answer, that makes it entirely useless, is that the solution/ commands given do not export sequences created on the table (primary key), and so the import fails. I am not aware of a single production PG database anywhere that does not use primary keys with sequences. It is half an answer, fails to thoroughly answer the question and requires additional work to export/import a single table.
@user10664542 Surely there must be some production PG databases using uuids rather than sequences for the primary key.
|
126

If you are on Ubuntu,

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

Make sure that you are executing the command where the postgres user have write permissions (Example: /tmp)

Edit

If you want to dump the .sql in another computer, you may need to consider skipping the owner information getting saved into the .sql file.

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

Improve this answer

7 Comments

What if you want all the tables, without having to specify each's name?
Just remove the -t option! pg_dump -d <database_name> > file.sql
I don't know why but -d option is not valid for psql9.3 and given command doesn't work for me. Working one is pg_dump -U username db_name -t table_name > fileName.
pg_dump -t <table_name> <database_name> > file.sql
@user10664542: Please read the manual, especially this section: "For this purpose, “table” includes views, materialized views, sequences, and foreign tables".
|
55

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

You can take the backup of a single table but I would suggest to take the backup of whole database and then restore whichever table you need. It is always good to have backup of whole database.

9 ways to use pg_dump

Improve this answer

2 Comments

I'm not sure why this answer was up voted, people click +1 without inspecting things themselves or trying. The problem with this answer, that makes it entirely useless, is that the solution/ commands given do not export sequences created on the table (primary key), and so the import fails. I am not aware of a single production PG database anywhere that does not use primary keys with sequences. It is half an answer, fails to thoroughly answer the question and requires additional work to export/import a single table.
as noted above, I have a strong suspicion user10664542, without any SO postgres answers to his name, is out of his depth here. while the sequence does bring additional complications, enough upvotes are on the similar answers that folk probably got them working even if they have had to adjust the sequence after the fact. had I had to restore my dump, I would look for ways to update the sequence's last id from a max(id) on my table. this is the answer that worked for and I am confident, looking at the generated sql, that I could have restored it.
13

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

enter image description here

enter image description here

enter image description here

Improve this answer

Comments

9

you can use this command

pg_dump --table=yourTable --data-only --column-inserts yourDataBase > file.sql

you should change yourTable, yourDataBase to your case

Improve this answer

1 Comment

The --data-only flag is what I was missing
6

As an addition to Frank Heiken's answer, if you wish to use INSERT statements instead of copy from stdin, then 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

Notice that I left out the --ignore-version flag, because it is deprecated.

Improve this answer

1 Comment

Although the --inserts flag is documented to yield "slow" restorations intended for compatibility with non-PostgreSQL databases, it has the added benefit of allowing partial imports in case of any invalid/corrupt data in the backup and seems to handle NULL values better. Also worth considering --column-inserts to mitigate risk of changes to column order.
5

Here is how I do it.

pg_dump -h localhost -U postgres -p 5432 -t table database  > path/to/store/name.sql

You can edit this sql file and remove the table creation commands and setting of other fields if you don't want that to happen and later run the below command to insert the data in the other database.

 psql -h localhost -U postgres -p 5432 database < path/to/store/name.sql
Improve this answer

Comments

2

Use the following command to get the compressed version of the table dump :

pg_dump -h localhost -p 5432 -U <username> -d <dbname> -t <tablename> -Fc -f backup.out
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.