Cannot restore table using pg_restore

From: Bill Todd <pg(at)dbginc(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Cannot restore table using pg_restore
Date: 2008-11-25 17:46:48
Message-ID: 492C3A08.5090704@dbginc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am new to PostgreSQL and am running 8.3.3 on Windows XP Pro SP3. I
have dumped a database using the following command.

pg_dump --format=custom --username=postgres
--file=c:\pgdb\dumps\app.backup app

I am trying to restore a table named contact in the contact schema. I
have tried two methods. The first is the command

pg_restore --dbname=app --format=custom --schema=contact
--table=contact.contact --username=postgres --single-transaction
contact.backup

Note that I renamed the dump file from app.backup to contact.backup.
This command runs without any error messages but does not restore the table.

I also used the -l option to create a list file then deleted all of the
items I do not want to restore leaving the following in the file
contact.list.

;
; Archive created at Fri Nov 21 19:40:17 2008
; dbname: app
; TOC Entries: 139
; Compression: -1
; Dump Version: 1.10-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 8.3.3
; Dumped by pg_dump version: 8.3.3
;
;
; Selected TOC Entries:
;
1591; 1259 25326 TABLE contact contact postgres
1592; 1259 25334 VIEW contact company postgres
1919; 0 25326 TABLE DATA contact contact postgres
1890; 2606 25333 CONSTRAINT contact contact_id_unique postgres

I have used the list file above with the following command.

pg_restore -L contact.list contact.backup > result.txt

This also runs without error but does not restore anything. The contents
of the result.txt file are at the end of this message with only the data
in the copy command removed. I must be doing something wrong but after
three hours I cannot see what I am missing.

Bill

--
-- PostgreSQL database dump
--

SET client_encoding = 'WIN1252';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = contact, pg_catalog;

--
-- Data for Name: contact; Type: TABLE DATA; Schema: contact; Owner:
postgres
--

COPY contact (id, last_name, first_name, company, work_phone, extension,
home_phone, honorific, title, address_1, address_2, city, state, zip,
country, fax, spouse, type, keyword, speed_dial, email, url, note) FROM
stdin;
\.

SET default_tablespace = '';

--
-- Name: contact_id_unique; Type: CONSTRAINT; Schema: contact; Owner:
postgres; Tablespace:
--

ALTER TABLE ONLY contact
ADD CONSTRAINT contact_id_unique PRIMARY KEY (id);

--
-- PostgreSQL database dump complete
--

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Csaba Együd 2008-11-25 18:14:47 Separate Sessions?? (View data <-> Query tool)
Previous Message V S P 2008-11-25 14:51:40 Re: pg_get_serial_sequence Strangeness/Unreliable?