5 Replies Latest reply on Nov 18, 2014 8:56 AM by edinor

    Jive Cloud Admin Analytics Issue

    ernichols

      Lately whenever I'm trying to restore .dmp files from JCA to our reporting server's PostgreSQL pgAdmin III tool, the tables won't restore to my database and it will instead time out and freeze the program. When I go to reopen the program, all the tables are listed in the schema, but there is no data in any of them; just empty columns. Is this an issue others have experienced and what could be causing this?

       

      When I check the checkbox for "Exit on error" when restoring the .dmp files, I almost immediately received the following error

       

      C:/Program Files/PostgreSQL/9.3/bin\pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "analytics" --role "postgres" --no-password  --exit-on-error --verbose "E:\dmpFiles\jacobsegv7_analytics-20141113-0000.dmp"

      pg_restore: connecting to database for restore

      pg_restore: creating SCHEMA public

      pg_restore: creating COMMENT SCHEMA public

      pg_restore: creating PROCEDURAL LANGUAGE plpgsql

      pg_restore: [archiver (db)] Error while PROCESSING TOC:

      pg_restore: [archiver (db)] Error from TOC entry 657; 2612 24008 PROCEDURAL LANGUAGE plpgsql jacobsegv7_analytics

      pg_restore: [archiver (db)] could not execute query: ERROR:  role "jacobsegv7_analytics" does not exist

          Command was: ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO jacobsegv7_analytics;

       

      Process returned exit code 1.

       

      Does anyone know what could be causing this error/how to fix it?

       

      Otherwise, does anyone know if there is a way that I can connect directly to the JCA .dmp file server for these reports? Is there already a data provider installed on there/does anyone already do this?

        • Re: Jive Cloud Admin Analytics Issue

          I don't do this a lot myself but I suspect if you remove the --exit-on-error flag from your command line the whole thing will work out.

           

          The import process is trying to grant some sort of permission to the owner "jacobsegv7_analytics" which probably does not exist. However I don't think that particular piece is particularly important to the schema and the kinds of things you want to do.

           

          Alternatively, you could define an account  jacobsegv7_analytics in the DB you are importing into (I think)

            • Re: Jive Cloud Admin Analytics Issue
              ernichols

              Unfortunately when I don't have the exit-on-error in the command, the query will run until it eventually times out and freezes my server.

               

              I tried to "create user jacobsegv7_analytics" first to see if that would make any difference, and instead then i just get a different error.

               

              Do you know if there's any way I can connect my reports to JCA itself so I don't have to use PostgreSQL to restore the dmp files at all? I'm trying to run weekly analytics reports for my community and I'm trying to make it as seamless a process as possible.

               

              (by the way, not to totally fangirl, but I was in a couple of your sessions at JiveWorld this year, Josh!)

            • Re: Jive Cloud Admin Analytics Issue
              edinor

              Hello! I'll pull down a copy from JCA and see if I can reproduce the issue. It looks like the expanded DB is almost 75GB, I don't think a clean restore would be that large, but that sort of hanging could be symptomatic of a disk space issue. One thing we could look at is what it is hanging on, the query (while it is hanging):

               

              SELECT now()-query_start, pid, state, waiting, query FROM pg_stat_activity WHERE state != 'idle' ORDER BY query_start;

               

              Another thought, can you add --no-owner to your restore command? I am thinking that it exited on this user error, but this isn't why it is hanging. It's also possible it will hang anyway, and not error out, but let's see what happens. I will let you know how my restore goes as soon as I get it downloaded. Thanks! Ed