Skip site navigation (1) Skip section navigation (2)

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: (view raw, whole thread or download thread mbox)
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 --username=postgres --single-transaction 

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 

; 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.


-- 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: 

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 

SET default_tablespace = '';

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

    ADD CONSTRAINT contact_id_unique PRIMARY KEY (id);

-- PostgreSQL database dump complete


pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group