Postgres Pro
Downloads
Home   >   mailing lists

Thread: cannot restore db

cannot restore db

From
"P. Jourdan"
Date:
I have installed pg 7.2.1_1, initialized & setup databases, installed plpgsql, set up users & superusers - everything seems to be ok.... but I cannot restore the backup of a database that was created in pg 7.0.3 .
I do not know how the original was created - pg_dump or pg_dumpall.
These are a few of the first lines of the dump.file:

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

After I run pgsql -d med -f  kitchen16022001.psql 2> err
this is the beginning of the err file:

psql:kitchen16022001.psql:4: ERROR:  parser: parse error at or near "username"
psql:kitchen16022001.psql:5: ERROR:  Relation "category_category_id_seq" does not exist
psql:kitchen16022001.psql:75: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'category_pkey' for table 'category'
psql:kitchen16022001.psql:80: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'country_pkey' for table 'country'
psql:kitchen16022001.psql:86: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'currency_pkey' for table 'currency'
psql:kitchen16022001.psql:95: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'currency_rates_pkey' for table 'currency_rates'
psql:kitchen16022001.psql:109: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'flow_pkey' for table 'flow'
psql:kitchen16022001.psql:116: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'news_pkey' for table 'news'
...
psql:kitchen16022001.psql:172: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'trademark_pkey' for table 'trademark'
psql:kitchen16022001.psql:177: ERROR:  CREATE TABLE: column "type_of_alcohol_licence_id" named in key does not exist
psql:kitchen16022001.psql:182: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'type_of_business_pkey' for table 'type_of_business'
psql:kitchen16022001.psql:187: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'type_of_business_entity_pkey' for table 'type_of_business_entity'
psql:kitchen16022001.psql:193: NOTICE:  CREATE TABLE / PRIMARY KEY will create .....
...
psql:kitchen16022001.psql:383: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 'newusers_pkey' for table 'newusers'
psql:kitchen16022001.psql:385: ERROR:  stat failed on file '/usr/local/pgsql/lib/plpgsql.so': No such file or directory
psql:kitchen16022001.psql:386: ERROR:  Language plpgsql already exists
psql:kitchen16022001.psql:1032: ERROR:  Relation "type_of_alcohol_license" does not exist
psql:kitchen16022001.psql:1038: invalid command \.
psql:kitchen16022001.psql:1039: ERROR:  parser: parse error at or near "1"
psql:kitchen16022001.psql:1050: invalid command \.
psql:kitchen16022001.psql:1051: ERROR:  parser: parse error at or near "1"
psql:kitchen16022001.psql:1057: invalid command \.
psql:kitchen16022001.psql:1058: ERROR:  parser: parse error at or near "1"
psql:kitchen16022001.psql:1066: invalid command \.
psql:kitchen16022001.psql:1067: ERROR:  parser: parse error at or near "0"
psql:kitchen16022001.psql:1127: invalid command \.
psql:kitchen16022001.psql:1128: ERROR:  parser: parse error at or near "1"
psql:kitchen16022001.psql:2906: invalid command \.
psql:kitchen16022001.psql:2907: ERROR:  parser: parse error at or near "1638"
psql:kitchen16022001.psql:2910: invalid command \N
psql:kitchen16022001.psql:2911: invalid command \N
psql:kitchen16022001.psql:2915: invalid command \N ....

Would installing the old version of pg make it possible to restore the database?
Could the restored database then be migrated to the newer version of pg?

Sorry to be so ignorant, but I am quite new to FreeBSD & Postgrsql.
Can someone help, please?
Philip jourdan

Pl/Pgsql function troubles: FOLLOW UP

From
Jeff Post
Date:
here is Code that I got working but I still have some problems with:
(I am asking for help, so if you wish to skip to my question read the last
sentance)

CREATE or replace FUNCTION list_of_membership(integer,CHAR) RETURNS TEXT AS '
DECLARE
   membership_rec record;
   membership text := NULL;
   count integer := 0;
   sqlstr1 text := ''select name from org_details where person_id = '';
   sqlstr2 text := '' and type = '';
   sqlstr3 text := '' order by name;'';
BEGIN
   FOR membership_rec IN EXECUTE sqlstr1 || $1 || sqlstr2 ||  $2 || sqlstr3
LOOP
      count := count + 1;
      IF count = 1 THEN
        membership := membership_rec.name;
      ELSE
        membership := membership || '', '' || membership_rec.name;
      END IF;
   END LOOP;
   RETURN membership;
END;
' LANGUAGE 'plpgsql';

the problems are such:
after the above is "compiled" and I try to do a select from it:

Attempt number 1:
=# SELECT list_of_membership(1,department);
ERROR:  Attribute 'department' not found

This is my ideal methodfor calling this function. As you can see it has a
problem with the second variable pass. The problem (after much head scratching)
is determined to be that it thinks I am trying to pass it a defined type. when
I am supposed to be passing it a string.

Attempt number 2:
=# SELECT list_of_membership(1,'department');
NOTICE:  Error occurred while executing PL/pgSQL function list_of_membership
NOTICE:  line 10 at for over execute statement
ERROR:  Attribute 'department' not found

Well this is esentually the same problem as above. But now in the internal
SELECT sql.

Attempt number 3:
=# SELECT list_of_membership(1,'\'department\'');
 list_of_membership
---------------------
 Madison, Technology
(1 row)

YEA!!!!

But MAN is that an UGLY call (especialy since I will be building this into perl
scripts.) the '\' and \'' are going to cause me some trouble.

Does anyone have any suggestions as to how I can make this look more like
attempt number 1 or 2 ?

THANKS!
Jeff Post

Re: Pl/Pgsql function troubles: FOLLOW UP

From
Oliver Elphick
Date:
On Tue, 2002-04-16 at 22:58, Jeff Post wrote:
> here is Code that I got working but I still have some problems with:
> (I am asking for help, so if you wish to skip to my question read the last
> sentance)
>
> CREATE or replace FUNCTION list_of_membership(integer,CHAR) RETURNS TEXT AS '
...
>    FOR membership_rec IN EXECUTE sqlstr1 || $1 || sqlstr2 ||  $2 || sqlstr3
                                                                ^^
quote_literal($2)

Think what the command would look like if you ran it directly.  A
literal string must be quoted.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "But seek ye first the kingdom of God, and his
      righteousness; and all these things shall be added
      unto you."     Matthew 6:33
Attachment

Re: cannot restore db

From
"Josh Berkus"
Date:
Jourdan,

> I have installed pg 7.2.1_1, initialized & setup databases, installed
> plpgsql, set up users & superusers - everything seems to be ok....
> but I cannot restore the backup of a database that was created in pg
> 7.0.3 .
> I do not know how the original was created - pg_dump or pg_dumpall.
> These are a few of the first lines of the dump.file:
>
> Username: Password:
> \connect - med

This looks like a pg_dumpall file.  Are you just restoring one
database, or the whole server?

-Josh Berkus


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: Pl/Pgsql function troubles: FOLLOW UP

From
"Joshua b. Jore"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Consider this:

DROP FUNCTION list_of_membership(INTEGER,TEXT);
CREATE FUNCTION list_of_membership(INTEGER,TEXT) RETURNS TEXT AS '
DECLARE
    membership_rec RECORD;
    membership TEXT;
BEGIN
    FOR membership_rec IN EXECUTE
        ''SELECT name FROM org_details WHERE person_id = ''::text
        || CAST($1 AS TEXT)
        || '' AND type = ''::text
        || quote_literal($2)
        || '' ORDER BY name;'';
    LOOP
        IF membership IS NULL THEN
            membership := membership_rec.name;
        ELSE
            membership := membership
                || '', ''::text
                || membership_rec.name;
        END IF;
    END LOOP;
    RETURN membership;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);


And here is how you want to be calling the function.
SELECT list_of_membership(1,'department');

Joshua b. Jore
http://www.greentechnologist.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (OpenBSD)
Comment: For info see http://www.gnupg.org

iD8DBQE8vWoHfexLsowstzcRAp26AJ4jqyb9Mk3VpPvpq3WMrhab4G2ceACg8ibr
R+ljghBHsTa5k9JFFuf6bNA=
=uUP3
-----END PGP SIGNATURE-----

Re: cannot restore db

From
"Josh Berkus"
Date:
Phillip,

> >This looks like a pg_dumpall file.  Are you just restoring one
> >database, or the whole server?
> I am trying to restore only the database which was on a virtual
> server on some IP. I believe that PG was on a shared server while the
> site was on another. What I have is the copy of the site that I
> periodically made as local backup.

Could be tough, then.  The way the file you have is supposed to be used
is to restore an entire database server from a fresh initdb, using
trusted access from the local machine.  As such, trying to restore only
one database on a shared server could be challenging.

How large is the file?  If you could send it to me, I could tinker
around and see if there's an easy way for you to restore what you need.
 If it's over 2mb, though, we'd have to do it by FTP.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: cannot restore db

From
"Josh Berkus"
Date:
Phillip,

It's not just you.  I'm going to have to file a bug report.

-Josh Berkus