Below is the command to use when you would like to dump only the data from a postgreSQL table. There are other options to use to dump the schema, multiple tables, exclude certain tables, or backup the entire database but these command line switches will dump only the data from one postgreSQL table.
In the below example replace table_name with the name of the table you are backing up, file_name with the name of the output file (optionally add a path to the file name), and database_name with the name of the database where the table is located.
- [root@server data]#pg_dump -a -t table_name -f file_name database_name
If you have not added a path to the command then a file by the name provided will be added to the directory you are in. I always like to add .sql to the end of the file name though it is not required. The structure of the file will look something like the below.
- --
- -- PostgreSQL database dump
- --
- SET client_encoding = 'UTF8';
- SET standard_conforming_strings = off;
- SET check_function_bodies = false;
- SET client_min_messages = warning;
- SET escape_string_warning = off;
- SET search_path = public, pg_catalog;
- --
- -- Name: table_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
- --
- SELECT pg_catalog.setval('table_id_seq', 2623316, true);
- --
- -- Data for Name: table; Type: TABLE DATA; Schema: public; Owner: postgres
- --
- COPY batteries (id, userid, "timestamp", percentage, time_remaining, devid) FROM stdin;
- 1 2 2008-05-19 23:08:55+00 86 4094 4
- 2 2 2008-05-19 23:09:10+00 86 4094 4
- 3 2 2008-05-19 23:09:25+00 86 4094 4
- 4 2 2008-05-19 23:09:39+00 86 4094 4
- 5 2 2008-05-19 23:09:54+00 86 4094 4
- 6 2 2008-05-19 23:10:09+00 86 4094 4
You can restore the data to a table using the psql command from the CLI.
You can use this to restore the data file
Be sure to drop the database first:
How to exclude multiple tables during an export
Thanks for the additions pactel75.
To create the empty DB from Rails before restoring:
Thanks for the command addition.
Link to dump the schema only:
http://www.question-defense.com/2009/03/01/dump-a-postgresql-database-without-the-data/
Thanks for the link. I think I will add a plugin to WordPress that will allow us to link related articles whenever we want in a clean format.
Радует, что блог постоянно развивается. Такие посты только прибавляют вам популярности.
Hello Василий,
Thanks for the nice comments.
Thanks.
alex
My requirement is to run a batch file daily which takes incremental back up of the database. I need to know how do I import an output of a select statement into a csv file from psql. Please help
Hello Debashish,
The article above describes exactly what you are trying to do. You just need to add the switch, if available, for incremental backups.
Thanks.
alex
I had to add the ‘-U user_name’ flag to get the original command at the top of this blog post to work:
sudo ./pg_dump -a -U postgres -t mgmt_cmds -f file_name.sql database_name
Hello Chirag,
You are correct if you are not working from the “postgres” user as the user you are logged in with will be assumed if you do not use the -U switch. If you “su” to the postgres user though the “-U” flag should not be needed depending on the postgres configuration. Also if you want to dump schema only then instead of the “-a” switch use the “-s” or “–schema-only” switch as we talked about earlier. Hope this helped.
Thanks.
alex