469,270 Members | 1,026 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,270 developers. It's quick & easy.

Dump/Restore ordering problem?


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

Nov 12 '05 #1
2 2287
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2
"D. Dante Lorenso" <da***@lorenso.com> writes:
Is there something I need to do to make the
dependency ordering work smarter during a dump/restore?


This is fixed in CVS tip's pg_dump. You could probably get away with
using the development pg_dump with a 7.4 server --- I don't think we've
introduced any incompatibilities yet. If you're running something older
than 7.4, though, you'll have to keep hacking the dump order.

pg_restore has some options for fixing the order at reload time, which
would be your best bet if the schema is large.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Andrei Ivanov | last post: by
7 posts views Thread by Cyril VELTER | last post: by
6 posts views Thread by Egyd Csaba | last post: by
5 posts views Thread by Chris Stankevitz | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.