Thread: migration still a problem

Thanks for helping. I made some notes below.
I'm new to mailing lists and am not sure of the correct protocols so I appreciate any suggestions on how to respond.

At 04:27 PM 4/11/2002 -0700, you wrote:
Jourdan,

> I am trying to restore a database from a gzipped file: i believe that
> backups were done as complete files (not partial) under Postgresql
> 7.0.3. Pg_restore does not recognize the ungzipped file
> filename.psql. The command, psql -d database -f filename.psql,
> restores it partially, but with numerous errors and the database is
> mostly empty. As I understand, this command restores the file to an
> existing database, so I had to create one with the original filename.
> But I don't know if the newly created database must have the exact
> same permissions, ownership, etc. as the original.
> I am told to install the earlier version of Postgresql to restore,
> but that seems to be complicated. Even if that works, how can the
> restored database be migrated to a newer version of Postgresql?
> Does anybody out there know about this kind of thing?
> Thanks for any help.

Well, there's two possibilities:
1. The backup file was damaged somehow. Opening it in a text editor
with "word wrap" turned on can be enough to screw up the file (I once
had a client do this -- they even opened it in MS Word, which wrecked
the file compeletely).

I don't think this happened, as I only gunzipped the file.


2. Your errors have something to do with users & permissions. Do you
have any idea whether the file was created with pg_dump or pg_dumpall?
This might be it. k
Don't know. Doesn't the psql extention mean something, since pg_restore does not recognize it?

I dont know what needs to be done here. I have the original username & password and one of the first errors when rebuilding is related to username. I am not clear on just how one is to set up a Postgressql superuser or what needs to be done to create the base database. I have no problem creating the database, but perhaps I need to do it as the username with password as originally used.
I do have pgAdmin2 installed on W2K. The database is on a FreeBSD machine
Then, also, I am told that I need the plpgsql language installed. This was done successfully on the current installation of Postgresql 7.2.
first 2 errors: parser: parse error at or near üsername"
Relation "category_category_id_seq" does not exist
Then there is a ton of ":linenumber: invalid command \N errors and a number or "ERROR: parser: parse error at or near "n" (n = various numbers)
I have an error file with all the errors.

3. You have some of the issues with pg_dump which have been resolved
between version 7.0.3 and now.

Take a look at what the *specific* errors are that you are getting when
you try to restore using psql. This will give you a much better idea
what's going wrong.

-Josh Berkus

Jourdan,

> 2. Your errors have something to do with users & permissions. Do you
> have any idea whether the file was created with pg_dump or
> pg_dumpall?
> This might be it. k
> Don't know. Doesn't the psql extention mean something, since
> pg_restore does not recognize it?

No.  Extensions are completely optional on a UNIX system, and tell you
nothing about the file.

> I dont know what needs to be done here. I have the original username
> & password and one of the first errors when rebuilding is related to
> username. I am not clear on just how one is to set up a Postgressql
> superuser or what needs to be done to create the base database. I
> have no problem creating the database, but perhaps I need to do it as
> the username with password as originally used.

Next step:  First, copy the original file in case your text editor
causes problems.  Second, copy the, say, first 30 lines of the file and
post them with your next reply.  That should be enough to tell if it's
a pg_dump or pg_dumpall file.

-Josh

At 11:25 AM 4/14/2002 -0700, you wrote:

>Next step:  First, copy the original file in case your text editor
>causes problems.  Second, copy the, say, first 30 lines of the file and
>post them with your next reply.  That should be enough to tell if it's
>a pg_dump or pg_dumpall file.
>
>-Josh

Here are the first 25 lines:

Username: Password:
\connect - med
CREATE SEQUENCE "category_category_id_seq" start 60 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"category_category_id_seq"');
CREATE SEQUENCE "contact_contact_id_seq" start 576 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"contact_contact_id_seq"');
CREATE SEQUENCE "currency_rates_currency_rate" start 21 increment 1
maxvalue 2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"currency_rates_currency_rate"');
CREATE SEQUENCE "customer_customer_id_seq" start 49 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"customer_customer_id_seq"');
CREATE SEQUENCE "flow_flow_id_seq" start 5 increment 1 maxvalue 2147483647
minvalue 1  cache 1 ;
SELECT nextval ('"flow_flow_id_seq"');
CREATE SEQUENCE "news_news_id_seq" start 1 increment 1 maxvalue 2147483647
minvalue 1  cache 1 ;
SELECT nextval ('"news_news_id_seq"');
CREATE SEQUENCE "order_status_order_status_id" start 7 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"order_status_order_status_id"');
CREATE SEQUENCE "order_types_order_type_id_se" start 3 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"order_types_order_type_id_se"');
CREATE SEQUENCE "orders_order_id_seq" start 4 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"orders_order_id_seq"');
CREATE SEQUENCE "pages_pages_id_seq" start 26 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"pages_pages_id_seq"');
CREATE SEQUENCE "price_types_price_type_id_se" start 5 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
SELECT nextval ('"price_types_price_type_id_se"');

Philip Jourdan