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.