Re: Dump/Restore ordering problem?

Dear D. Dante Lorenso ,

pg_dump the schema  alone and the data alone in two different  files
with commands
 pg_dump -R -s -F p -f  my_schema.sql -U <username> <dbname>       <----
for Schema
pg_dump --disable-triggers -U <username> -a -d -b -D -Fc Z 9
my_data.tar.gz  <dbname>    <--for data

Now restructure your schema file such that functions are created first .

Hope this helps
Regards ,
Vishal Kashyap

>
> First I created a function that selected the next available pin
> code from a table of pre-defined pin codes:
>
>    CREATE FUNCTION "public"."get_next_pin_code" () RETURNS varchar AS'
>    DECLARE
>        my_pin_code VARCHAR;
>    BEGIN
>        ...
>        /* this is the pincode we just fetched */
>        RETURN (my_pin_code);
>    END;
>    'LANGUAGE 'plpgsql';
>
> Then I created a table that used that function to set a default value:
>
>    CREATE TABLE "public"."account" (
>        "acct_id" BIGSERIAL,
>        ...,
>       "acct_pin_code" VARCHAR(16) NOT NULL DEFAULT get_next_pin_code()
>    ) WITH OIDS;
>
> But, now when I pg_dump and pg_restore this database to another server,
> there seems to be a problem with the ordering of the dump in that the
> account table is not recreated because the function get_next_pin_code()
> is not yet defined.  It seems like the function is not being created
> until AFTER the table is created and this causes an ordering problem.
>
> To dump and restore I've been doing this:
>
>    pg_dump -U dbuser -Ft dbname | pg_restore -c -Ft | psql -U dbuser
> -h db.otherdbhost.com dbname
>
> I've been able to work around this by creating a TRIGGER that sets the
> default value instead of defining it in the table definition, but that
> just seems like a hack.  Is there something I need to do to make the
> dependency ordering work smarter during a dump/restore?  Or is this the
> right way to do it?
>
> Dante
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
>