Re: pg_restore does not restore all tables

Hi

The logs that you attach do show an error:

pg_restore: [programme d'archivage (db)] could not execute query: ERROR: schema
"bga_3122" already exists
Command was:
CREATE SCHEMA bga_3122;

so maybe you need to run the restore with --clean which will drop
database objects before recreating them, although I'm not sure that
will cause the schema to be dropped.

Are you sure that you attached the correct logs? The dump is to
bga_3103__c_cmd_00.backup but the restore is from
bga_3122__c_cmd.backup. And I can't see the csv_java_files stuff you
mention in the restore log

...
pg_restore: création de TABLE keysana
pg_restore: création de TABLE objobj
pg_restore: création de VIEW csv_java_files
pg_restore: création de VIEW csv_java_properties
pg_restore: création de VIEW csv_javadoc
pg_restore: création de TABLE keygrp
pg_restore: création de VIEW csv_library_objects
...

Hope that helps.

--
Ian.

2009/12/2 Baubak Gandomi <b(dot)gandomi(at)castsoftware(dot)com>:
> Thanks, I didn't even know where to start:
>
> The Postgre Version : 8.4.1
> The host OS is : MS Windows 2003 SP2
> The postgre vesrion on the client side is : 8.4.1
> The client OS is : Windows XP
>
> Command line for the backup: pg_dump --host=HOST1 --port=2278 --username=postgre --schema=mySchema --format=custom --verbose --file=myFile.backup postgres
> Command line for the restore: pg_restore --host=HOST1 --port=2278 --dbname=postgres
> --username=postgre --format=custom --verbose  myFile.backup
>
> I have digged in a little bit more and have dicovered the followiing. In the log of the restore process:
>
> ----- snip ------
> pg_restore: création de TABLE objobj
> pg_restore: création de VIEW csv_java_files
> pg_restore: création de VIEW csv_java_properties
> pg_restore: création de VIEW csv_javadoc
> pg_restore: création de TABLE keygrp
> ----- snip ------
>
> The table is restored successfully, but none of the objects that follow the table "objobj" are restored (roughly 400 tables and 80 views). I.e it seems that the view "csv_java_files" causes the problem.
>
> If we look at the code we can see that it looks like:
>
> CREATE OR REPLACE VIEW mySchema.csv_java_files AS
>  SELECT keysana.idkey AS object_id, substr(keysana.keynamana::text, mySchema.instr(keysana.keynamana, '\'::character varying, (-1)) + 1) AS object_name, keysana.keynamana AS object_path, keypar.idkey AS package_id, objpro.idpro AS application_id
>   FROM mySchema.keypar, mySchema.objobj, mySchema.objpro, mySchema.keysana
>  WHERE keysana.keyclass = 5170 AND keysana.idkey = objpro.idobj AND objpro.prop = 0 AND objobj.idobj1 = keysana.idkey AND objobj.prop = 2 AND keypar.idkey = objobj.idobj2 AND keypar.idparent = objpro.idpro;
>
> My first thought was a dependency problem, but I am able to manually create the view.
>
> This causes me to wonder about the following:
> - Why does the pg_restore fail to restore at just this view?
> - If there is an error why isn't there a corresponding message? The log simply continues as if it were still restoring objects.
>
> I hope this gives you more to go on. I have included the log of the pg_dump and restore.
>
> Best regards:
>
> Baubak
>
>
>
> -----Original Message-----
> From: Ian Lea [mailto:ian(dot)lea(at)gmail(dot)com]
> Sent: mardi 1 décembre 2009 18:31
> To: Baubak Gandomi
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] pg_restore does not restore all tables
>
> You could start by saying what version of postgres, on what OS, and
> posting your backup and restore commands with any output, error or
> otherwise, that they produce.
>
>
>
> --
> Ian.
>
>
> On Tue, Dec 1, 2009 at 4:48 PM, Baubak Gandomi
> <b(dot)gandomi(at)castsoftware(dot)com> wrote:
>> Hello,
>>
>>
>>
>> I have a problem restoring a database. I have made a dump of a schema, with
>> no error messages. When I restore my dump I do not get any error messages
>> either. But when I look into my schema I see that I have less tables than
>> before. I don't even know how to debug this problem !
>>
>>
>>
>> Best regards,
>>
>>
>>
>> Baubak Gandomi
>