How to create a backup of a single table in a postgres database?

Question

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?

score 363 · Accepted Answer · edited Jun 22 '20 at 06:30

363

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

edited Jun 22 '20 at 06:30

James

1,394
2
21
31

answered Sep 10 '10 at 07:39

Frank Heikens

117,544
24
142
135

20

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 --` – Anderson Dorregaray Linares Dec 05 '16 at 05:06
30

`psql -U username -d database -1 -f your_dump.sql` – rnaud Feb 03 '17 at 19:38
6

what did you mean: abstract_file_path? – bandungeuy Sep 22 '18 at 21:56
10

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 Dec 04 '19 at 17:56
1

with a slightly different sudo twist to this I ended up with a permission error on the file dump (logical enough since user postgres does not have write access on my directories) and I had an unknown flag error for `--ignore-version`. Prashant Kumar's answer, which is simpler, worked for me. – JL Peyret Dec 28 '19 at 20:37
1

If you want to use `pg_restore`, you need to add the flag `-c`, `-d`, or `-t`. As explained in the [Postgres Docs](https://www.postgresql.org/docs/11/app-pgdump.html), `--format plain` (the default, short form `-p`), does not work with pg_restore. E.g. add `-Fc` to pg_dump and `-Fc` to pg_restore to specify the format of the dump. – Alex Mar 12 '20 at 06:33
it works pretty well for PG11, but take care of the propper `pg_dump` app. In my Centos 7 Server I found it at `/usr/pgsql-11/bin` directory – Juan Salvador Aug 17 '20 at 19:07
@bandungeuy `abstract_file_path` meaning the path on your local system where you'd like the output to go. If you omit this, it goes to standard output. – Eric LeBlanc Sep 29 '20 at 14:50
3

@user10664542 Surely there must be some production PG databases using uuids rather than sequences for the primary key. – Peter Gerdes Jul 15 '22 at 05:18
@rnaud suggestion above is fine for restoring, but I found I had to remove the `-1` from that command to get it working. – clemep Feb 23 '23 at 19:42
i'm not sure why @user10664542 comment was upvoted. While it may be pointing out a problem it offers no ideas on solutions. As it stands it is purely negative and not super helpful. Worse, it assumes an authoritative tone. If the user has expertise, than posting a negative comment without concrete ideas for improvement is counterproductive. Maybe the issue is that we need to export the whole database instead of just a table if we want the sequences as well? Maybe it isn't an issue, particularly if you don't use sequences for keys (I don't)? – David Mar 08 '23 at 17:13
@David Pointing out "this answer has a bunch of problems" is absolutely useful. Fixing those problems would be even more useful, but it's a useful caution to readers coming across this later. – Richard Rast Jun 02 '23 at 12:30
@David: Since the introduction of identity columns, all (or at least most) issues with sequences are gone. That was version 10, back in 2017. – Frank Heikens Jun 02 '23 at 14:28

Sri Harsha Kappala · Answer 2 · 2015-07-16T08:59:42.050

116

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 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

edited Jul 16 '15 at 08:59

answered Jul 13 '15 at 05:37

Sri Harsha Kappala

3,339
26
23
30

1

What if you want all the tables, without having to specify each's name? – shekeine Jul 17 '15 at 13:13
3

Just remove the -t option! `pg_dump -d > file.sql` – Sri Harsha Kappala Jul 17 '15 at 13:14
4

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**. – serkan kucukbay Nov 15 '16 at 10:54
`pg_dump -t > file.sql` – Sri Jul 22 '17 at 22:28
@user10664542: Please read the manual, especially this section: "For this purpose, “table” includes views, materialized views, sequences, and foreign tables". – Frank Heikens Dec 04 '19 at 20:35
user Postgres does not have the right to write to ubuntu HD with error of permission denied – user938363 Nov 17 '21 at 18:59
for multiples tables we need -t table -t table – anvd Dec 06 '22 at 16:07

score 55 · Answer 3 · edited Jul 16 '17 at 11:37

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

edited Jul 16 '17 at 11:37

Vedran Šego

3,553
3
27
40

answered Oct 14 '14 at 17:10

Prashant Kumar

678
6
7

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 Dec 04 '19 at 17:57
3

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. – JL Peyret Dec 28 '19 at 20:41

score 11 · Answer 4 · answered Mar 31 '16 at 00:52

11

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.

answered Mar 31 '16 at 00:52

Franck Dernoncourt

77,520
72
342
501

score 8 · Answer 5 · answered Sep 03 '20 at 14:22

8

you can use this command

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

you should change yourTable, yourDataBase to your case

answered Sep 03 '20 at 14:22

1

The --data-only flag is what I was missing – faniva Feb 01 '23 at 14:45

score 3 · Answer 6 · answered May 24 '19 at 11:27

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.

score 2 · Answer 7 · edited Jun 21 '23 at 09:37

2

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

edited Jun 21 '23 at 09:37

Vaulstein

20,055
8
52
73

answered Nov 10 '22 at 11:29

MUGABA

751
6
7

score 0 · Answer 8 · answered Jun 27 '22 at 11:49

0

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

answered Jun 27 '22 at 11:49

user1188867

3,726
5
43
69

How to create a backup of a single table in a postgres database?

8 Answers8

Linked