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

J

James

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

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.

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.

S

Sri Harsha Kappala

If you are on Ubuntu,

Login to your postgres user sudo su postgres pg_dump -d -t > 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

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

V

Vedran Šego

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

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.

F

Franck Dernoncourt

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.

https://i.stack.imgur.com/lu5jH.png

https://i.stack.imgur.com/FjVUk.png

https://i.stack.imgur.com/W21oi.png

佚

佚名

you can use this command

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

you should change yourTable, yourDataBase to your case

u

user3207874

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.

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.

u

user1188867

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

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

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US