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