pg_restore does not restore all tables

This is a multi-part message in MIME format.

------_=_NextPart_001_01CA72A6.137EE339
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

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

------_=_NextPart_001_01CA72A6.137EE339
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>

</style>

</head>

<body lang=3DFR link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

Hello,<o:p></o:p>

<o:p> </o:p>

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 !<o:p></o:p>

<o:p> </o:p>

Best regards,<o:p></o:p>

<o:p> </o:p>

Baubak Gandomi<o:p></o:p>

</div>

</body>

</html>

------_=_NextPart_001_01CA72A6.137EE339--

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.gandomi [at] castsoftware.com> wrote:
> Hello,
>
>
>
> I have a problem restoring a database. I have made a dump of a schema, wi=
th
> 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=92t even know how to debug this problem=A0!
>
>
>
> Best regards,
>
>
>
> Baubak Gandomi

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Hi

The logs that you attach do show an error:

pg_restore: [programme d'archivage (db)] could not execute query: ERROR: s=
chema
"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=E9ation de TABLE keysana
pg_restore: cr=E9ation de TABLE objobj
pg_restore: cr=E9ation de VIEW csv_java_files
pg_restore: cr=E9ation de VIEW csv_java_properties
pg_restore: cr=E9ation de VIEW csv_javadoc
pg_restore: cr=E9ation de TABLE keygrp
pg_restore: cr=E9ation de VIEW csv_library_objects
....

Hope that helps.

--
Ian.

2009/12/2 Baubak Gandomi <b.gandomi [at] castsoftware.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=3DHOST1 --port=3D2278 --usern=
ame=3Dpostgre --schema=3DmySchema --format=3Dcustom --verbose --file=3DmyFi=
le.backup postgres
> Command line for the restore: pg_restore --host=3DHOST1 --port=3D2278 --d=
bname=3Dpostgres
> --username=3Dpostgre --format=3Dcustom --verbose =A0myFile.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=E9ation de TABLE objobj
> pg_restore: cr=E9ation de VIEW csv_java_files
> pg_restore: cr=E9ation de VIEW csv_java_properties
> pg_restore: cr=E9ation de VIEW csv_javadoc
> pg_restore: cr=E9ation de TABLE keygrp
> ----- snip ------
>
> The table is restored successfully, but none of the objects that follow t=
he table "objobj" are restored (roughly 400 tables and 80 views). I.e it se=
ems 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
> =A0SELECT keysana.idkey AS object_id, substr(keysana.keynamana::text, myS=
chema.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
> =A0 FROM mySchema.keypar, mySchema.objobj, mySchema.objpro, mySchema.keys=
ana
> =A0WHERE keysana.keyclass =3D 5170 AND keysana.idkey =3D objpro.idobj AND=
objpro.prop =3D 0 AND objobj.idobj1 =3D keysana.idkey AND objobj.prop =3D =
2 AND keypar.idkey =3D objobj.idobj2 AND keypar.idparent =3D objpro.idpro;
>
> My first thought was a dependency problem, but I am able to manually crea=
te 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 s=
imply 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_du=
mp and restore.
>
> Best regards:
>
> Baubak
>
>
>
> -----Original Message-----
> From: Ian Lea [mailto:ian.lea [at] gmail.com]
> Sent: mardi 1 d=E9cembre 2009 18:31
> To: Baubak Gandomi
> Cc: pgsql-admin [at] postgresql.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.gandomi [at] castsoftware.com> wrote:
>> Hello,
>>
>>
>>
>> I have a problem restoring a database. I have made a dump of a schema, w=
ith
>> 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=A0!
>>
>>
>>
>> Best regards,
>>
>>
>>
>> Baubak Gandomi
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

pg_restore does not restore all tables

Re: pg_restore does not restore all tables

Re: pg_restore does not restore all tables