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.
- Dump a PostgreSQL Database Without the Data Dumping a PostgreSQL database without the data is easy. Issue...
- Export PostgreSQL Table Data To Excel CSV File From PSQL CLI When working on a project where I was required to...
- Restore One Table with psql from a pg_dump PostgreSQL Backup Backing up and restoring PostgreSQL is fairly easy using pg_dump...
- Delete All Data In A PostgreSQL Table Using PGAdmin Open PGAdmin and connect to the database where you would...
- Delete All Rows from a PostgreSQL Table Use the truncate command from the CLI or via PGAdmin...
Tags: database, pg_dump, postgres, PostgreSQL, schema, sql, table
Entries (RSS)
You can use this to restore the data file
Be sure to drop the database first:
[Reply]
How to exclude multiple tables during an export
[Reply]
Thanks for the additions pactel75.
[Reply]
To create the empty DB from Rails before restoring:
[Reply]
alex Reply:
April 14th, 2009 at 6:35 PM
Thanks for the command addition.
[Reply]
Link to dump the schema only:
http://www.question-defense.com/2009/03/01/dump-a-postgresql-database-without-the-data/
[Reply]
alex Reply:
April 14th, 2009 at 6:36 PM
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.
[Reply]
Радует, что блог постоянно развивается. Такие посты только прибавляют вам популярности.
[Reply]
alex Reply:
December 12th, 2009 at 9:57 PM
Hello Василий,
Thanks for the nice comments.
Thanks.
alex
[Reply]
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
[Reply]
alex Reply:
January 11th, 2010 at 10:33 AM
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
[Reply]
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
[Reply]
alex Reply:
January 28th, 2010 at 11:22 PM
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
[Reply]