ERROR #06: Unable to restore database

2009-08-27
2013-04-23
  • While following the instructions for "Windows Installation" in the
    section "Loading the once:radix Tables into the Database," I getting
    the above mentioned error when I attempt:

    Right-click then select File > Restore (or type Ctrl-E). Select a
    database backup from the list, then click Restore.

    My PostgreSQL logs show the following:

    2009-08-27 08:39:17 PDTSTATEMENT:  create database onceradix with
    encoding = UTF8;
    2009-08-27 08:40:18 PDTERROR:  schema "mail" does not exist at character 36
    2009-08-27 08:40:18 PDTSTATEMENT:  SELECT mail.message."primary" FROM
    mail.message WHERE
    mail.message."status"<>1 ORDER BY mail.message."senddate"
    2009-08-27 08:42:53 PDTERROR:  schema "security" does not exist at character 40
    2009-08-27 08:42:53 PDTSTATEMENT:  SELECT s."username" AS "username"
    FROM "security"."usersecurity" s, "security"."loginhistory" h WHERE
    h."fkusersecurity" = s."primary" AND h."s
    essionkey"='null'
    2009-08-27 08:45:18 PDTERROR:  schema "mail" does not exist at character 36
    2009-08-27 08:45:18 PDTSTATEMENT:  SELECT mail.message."primary" FROM
    mail.message WHERE mail.message."status"<>1 ORDER BY
    mail.message."senddate"
    2009-08-27 08:46:42 PDTERROR:  schema "security" does not exist at character 40
    2009-08-27 08:46:42 PDTSTATEMENT:  SELECT s."username" AS "username"
    FROM "security"."usersecurity" s, "security"."loginhistory" h WHERE
    h."fkusersecurity" = s."primary" AND h."s
    essionkey"='dbrestoreff52107c537fd8eff074044a549bc0fe'
    2009-08-27 08:46:42 PDTLOG:  unexpected EOF on client connection

    Does anyone have suggestions?

    --
    Regards,
    Richard Broersma Jr.

     
    • I adjust my Postgesql.conf file to display a bit more detail.  Next I logged is using the dbrestore and attempted the restore, acknowledged the error,  then logged out, and finally closed the browser tab.

      Here are the log messages that are generated:

      2009-08-27 10:04:27 PDT,[unknown],[unknown]LOG:  connection received: host=127.0.0.1 port=4750
      2009-08-27 10:04:27 PDT,onceradix,postgresLOG:  connection authorized: user=postgres database=onceradix
      2009-08-27 10:04:27 PDT,onceradix,postgresLOG:  execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
      2009-08-27 10:04:33 PDT,onceradix,postgresLOG:  disconnection: session time: 0:00:06.125 user=postgres database=onceradix host=127.0.0.1 port=4750
      2009-08-27 10:04:33 PDT,[unknown],[unknown]LOG:  connection received: host=127.0.0.1 port=4751
      2009-08-27 10:04:33 PDT,onceradix,postgresLOG:  connection authorized: user=postgres database=onceradix
      2009-08-27 10:04:33 PDT,onceradix,postgresLOG:  execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED

      It seems that for some reason there is a connection left hanging after I exist.  Also, nothing much else seems to be happening at the postgresql end.  Does anyone know why the db schema is not restoring?

       
    • Adam Peters
      Adam Peters
      2009-08-28

      In the Once:Radix Windows installation guide, did you complete step 3 of the PostgreSQL section?  If the pg_hba.conf file was not modified properly, it could cause an authentication failure between Once:Radix and PostgreSQL.

       
    • Adam Peters
      Adam Peters
      2009-08-28

      In the Once:Radix Windows installation guide, did you complete step 3 of the PostgreSQL section?  If the pg_hba.conf file was not modified properly, it could cause an authentication failure between Once:Radix and PostgreSQL.

       
      • It could be, but I don't think that it is.  Out of all of the software packages used by once radix I am most confident in my PostgreSQL foo.  I previously posted some postgres logs that show that the postgres account to the onceradix database is being accepted.

        2009-08-27 10:04:27 PDT,onceradix,postgresLOG: connection authorized: user=postgres database=onceradix
        2009-08-27 10:04:27 PDT,onceradix,postgresLOG: execute <unnamed>: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
        2009-08-27 10:04:33 PDT,onceradix,postgresLOG: disconnection: session time: 0:00:06.125

         
    • onceradix
      onceradix
      2009-08-29

      What operating system and what hardware are you running?

       
      • I evaluating the software on:

        IBM ThinkCenter
        Pentium 4 CPU 2.8 GHz
        1 GB RAM

        Windows XP Version 2002 SP 2

        After I've familiarized myself once:radix, it will be installed on a more powerful linux server.

         
    • Adam Peters
      Adam Peters
      2009-08-29

      You could try manually restoring the database through pgAdminIII.  If it fails again, at least you have isolated the problem to the backup file that comes with the Once:Radix download.  It may have gotten corrupted somehow.

       
    • That's a good idea.  But in my case, I'm attempting to complete fresh (my first ever) install of once:radix.  So I assume that the step to called "restore" is really meant to create all of the base tables, view, functions, and stored Procedures that are part of the once:radix framework if these do not already exist.

      On the other hand, are you saying that there is an initial schema dump file somewhere is the once:radix directory hierarchy that I can directly install using pgadmin?  If there is I'll check when I get back to the office on Monday.

       
    • Adam Peters
      Adam Peters
      2009-08-30

      Yes, after you unzip the Once:Radix file, the initial dump file is in <path to onceradix-1.5.0>\onceradix\backup\auto-onceradix-2009-08-25-15-43.backup

      I believe that is the backup file used by the dbrestore process to setup the initial/base application of Once:Radix.  Let me know how it works out.  Good luck.

       
    • Okay, I'll give it a try and report back on Monday.  Thanks for the suggestion.

       
    • Jamie
      Jamie
      2009-08-31

      It appears that the database path does not match the setting in configuration.dat.
      If Postgresql is installed to a different path than (c:\program files\onceradix\postgres\)
      the configuration.dat file will need to be modified to the correct location of Postgresql.

      The section that you need to check is called [Database backup/restore]

      If you make any changes to configuration.dat tomcat will need to be restarted.

      If you are still having a issue with restoring the database through once:radix, this can also be done through pgAdmin.
      Step to do this are

      1. Load pgAdmin
      2. Double Click on PostgreSQL Database Server 8.4 connection
      3. Right click on onceradix database
      4. Click Restore
      5. Click on ...
      6  Browse to "c:\program files\onceradix\Tomcat 6\webapps\once\backup
      7. Select auto-onceradix-2009-08-25-15-43.backup
      8. Click OK

       
    • I've verified that configuration.dat correctly shows the path to the postgresql bin folder.   After restoring using pgAdmin, the following error messages were returned:

      pg_restore: [archiver (db)] Error while PROCESSING TOC: (It says that the role 'once' doesn't exist. Did I need to create this role manually as part of the create database step?)
      pg_restore: [archiver (db)] Error from TOC entry 6; 2615 50478 SCHEMA admin once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 7; 2615 50479 SCHEMA contacts once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 8; 2615 50480 SCHEMA mail once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 9; 2615 50482 SCHEMA security once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 404; 2612 50483 PROCEDURAL LANGUAGE plpgsql once
      pg_restore: [archiver (db)] could not execute query: ERROR:  language "plpgsql" already exists
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1591; 1259 50484 SEQUENCE license_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1592; 1259 50486 TABLE license once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1593; 1259 50494 SEQUENCE seqnumber_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1594; 1259 50496 TABLE seqnumber once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1595; 1259 50503 SEQUENCE address_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1596; 1259 50505 TABLE address once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1597; 1259 50512 SEQUENCE communication_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1598; 1259 50514 TABLE communication once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1599; 1259 50521 SEQUENCE levelstructure_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1600; 1259 50523 TABLE levelstructure once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1601; 1259 50530 SEQUENCE organisation_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1602; 1259 50532 TABLE organisation once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1603; 1259 50539 SEQUENCE organisationallevel_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1604; 1259 50541 TABLE organisationallevel once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1605; 1259 50548 SEQUENCE person_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1606; 1259 50550 TABLE person once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1607; 1259 50558 SEQUENCE personmember_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1608; 1259 50560 TABLE personmember once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1609; 1259 50567 TABLE attachment once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1610; 1259 50574 SEQUENCE attachment_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1611; 1259 50576 TABLE header once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1612; 1259 50583 SEQUENCE header_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1613; 1259 50585 TABLE message once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1614; 1259 50593 SEQUENCE message_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1615; 1259 50595 SEQUENCE group_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1616; 1259 50597 TABLE group once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1617; 1259 50604 SEQUENCE personmembergroup_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1618; 1259 50606 TABLE personmembergroup once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1619; 1259 50613 SEQUENCE securityblock_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1620; 1259 50615 TABLE securityblock once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1621; 1259 50622 SEQUENCE usersecurity_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1622; 1259 50624 TABLE usersecurity once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1623; 1259 50632 VIEW login once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1624; 1259 50637 SEQUENCE logtable_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1625; 1259 50639 TABLE logtable once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1626; 1259 50646 SEQUENCE dateformat_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1627; 1259 50648 TABLE dateformat once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1628; 1259 50655 SEQUENCE ipfilter_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1629; 1259 50657 TABLE ipfilter once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1630; 1259 50664 SEQUENCE loginhistory_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1631; 1259 50666 TABLE loginhistory once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1632; 1259 50674 SEQUENCE loginlocation_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1633; 1259 50676 TABLE loginlocation once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1634; 1259 50683 SEQUENCE securitytable_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1635; 1259 50685 TABLE securitytable once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1636; 1259 50692 SEQUENCE timeformat_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1637; 1259 50694 TABLE timeformat once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1638; 1259 50701 SEQUENCE timezone_primary_seq once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 1639; 1259 50703 TABLE timezone once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] Error from TOC entry 2065; 0 0 ACL public once
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exist
      pg_restore: [archiver (db)] could not execute query: ERROR:  role "once" does not exis

       
    • Adam Peters
      Adam Peters
      2009-08-31

      I tried deleting my database and following the steps you just did.  I too received those error messages, but PostgreSQL just ignored them and created all the schemas as appropriate anyhow.  Did it create the schemas in database "onceradix" on your machine as well?

       
    • Yes it looks like it did create everything.  However, all of the tables are now owned by postgres and from a security point of view, it is bad practice to make the postgres the owner of every table and to use the postgres account for ordinary application tasks.  It would be better if a once role were used for all of these tasks and as the owners of these tables.

      Well other than this, I will continue on with the windows installation documentation.  Thanks for the help everyone!

       
    • okay,  now when I attempt the restore process after having manually restored the schema, I no longer get an error when I attempt the restore:

      "The database has been successfully restored to its state at 2009/08/26 08:28."