Thread: Dumping a table from one database and adding it to another
I have read the documentation respecting backups but I cannot seem to find any mention of the specific case that I wish performed. I have a pair of tables in a production database that I wish to dump and then restore to a new, different database. I can, and probably will, recreate the tables and column layouts in the new database. Is there a way to load the data dumped from a single table in one database into a new, possibly differently named, table in a different database, using PG utilities? -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
On 12/02/2010 12:49 PM, James B. Byrne wrote: > I have read the documentation respecting backups but I cannot seem > to find any mention of the specific case that I wish performed. > > I have a pair of tables in a production database that I wish to dump > and then restore to a new, different database. I can, and probably > will, recreate the tables and column layouts in the new database. > > Is there a way to load the data dumped from a single table in one > database into a new, possibly differently named, table in a > different database, using PG utilities? > > pg_dump -U postgres -a -t cell_per -f cell_per.sql production From the head of the file,see inline comment ***: -- -- PostgreSQL database dump -- SET statement_timeout = 0; 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; -- -- Data for Name: cell_per; Type: TABLE DATA; Schema: public; Owner: postgres -- *** Change above to match new table or leave alone. COPY cell_per (line_id, category, cell_per, type, ts_insert, ts_update, user_insert, user_update, plant_type, season, short_category) FROM stdin; -- Adrian Klaver adrian.klaver@gmail.com
On 12/02/2010 12:49 PM, James B. Byrne wrote: > I have read the documentation respecting backups but I cannot seem > to find any mention of the specific case that I wish performed. > > I have a pair of tables in a production database that I wish to dump > and then restore to a new, different database. I can, and probably > will, recreate the tables and column layouts in the new database. > > Is there a way to load the data dumped from a single table in one > database into a new, possibly differently named, table in a > different database, using PG utilities? > > In my previous post comment should have read, change below :< -- Adrian Klaver adrian.klaver@gmail.com
On Thu, December 2, 2010 15:57, Adrian Klaver wrote: > a >> different database, using PG utilities? >> >> > > pg_dump -U postgres -a -t cell_per -f cell_per.sql production > followed by: /usr/bin/psql -f cell_per.sql production works wonderfully. Thank you. -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3