Re: Moving a database between servers

Quoting "Michael ." <phifli(at)yahoo(dot)com>:

> I am trying to move a postgresql database from one
> server to another. The original server is 7.1.3, and
> the new one is 7.3.4.
>
> I went on the old and used the command:
> pg_dumpall > dump
>
> On the new:
> psql -e < dump
>
> I get this for multiple functions. (Error follows
> surrounded by *****'s)
>
> CREATE FUNCTION "transaction_visit" (integer) RETURNS
> integer AS '
> select visit_id
> from (
> select *
> from visit_history as vh
> where sequence = (
> select max(sequence)
> from visit_history
> where vh.firm_id = firm_id
> and vh.patient_id =
> patient_id
> and vh.visit_id = visit_id
> )
> ) as v
> where v.firm_id = (select firm_id from
> current_transaction)
> and v.patient_id = $1
> and v.person_id = (select person_id from
> current_transaction)
> and v.inked is null
> ' LANGUAGE 'sql';
>
> ****ERROR: Relation "current_transaction" does not
> exist****
>
>
> The current_transaction is a TEMPORARY table created
> like this:
> CREATE FUNCTION "open_session" (integer,integer)
> RETURNS integer AS '
> create temporary table current_transaction as
> select $1 as firm_id,
> $2 as person_id,
> timeofday()::timestamp as
> transaction_timestamp,
> timeofday()::timestamp as
> history_timestamp;
> select 1;
> ' LANGUAGE 'sql';
>
>
>
>
>
> How do I get these functions to import correctly if it
> fails due to this "current_transaction" table not
> being found? (only due to it being a temporarily table
> created by a different command)

I had problem moving form 7.1.3 to 7.4 too. The easiest thing to do is to (if
you can) use 7.4's pg_dump. Just setup the TCP/IP connectivity if you don't
have it already and dump using a superuser account.

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com