7 Replies Latest reply on Jul 10, 2014 9:28 AM by Caton Guilbault

    Restoring postgres database freezing

    erinclements

      I am trying to restore a database dump using the instructions found in the document How To: Working with Database Dumps.  I am following the instructions exactly, but when it comes down to restoring the database, the PG Admin console freezes up and nothing ever happens.  I have let it sit for hours, and nothing happens.  Is there something I'm doing wrong? I'm running Windows 7, 64 bit, and I"ve downloaded the 9.2/64 bit version (because 9.2 is what the instructions were working with, i wanted to be as close as possible).  Is there another way to restore the database, or is there something I could be doing wrong? 

        • Re: Restoring postgres database freezing
          Caton Guilbault

          Hi Erin,

           

          Sounds like you are doing everything right. I have a couple of suggestion to help track down the problem.

           

          When doing a manual restore we always check the box for "clean before restore" on the Restore Options #1 tab. This makes sure all the old data is purged before restoring which might help if something broke on the first attempt. Alternatively you can just create a new Database each time.

          Restore Options 1.png

           

          I also suggest checking the box for "Verbose Messaging" on Restore Options #2 tab that way if something breaks you can see what it was.

          Restore Options 2.png

           

          Finally I recommended opening Resource Monitor (Start > All Programs > Accessories > System Tools) before restoring and watching the charts to benchmark you system. Once you start the restore you should see CPU and disk activity go way up until the process has completed. The amount of 'up' will depend on your system resources but you should see something to indicate the restore is happening.

          ResourceMonitor.png

           

          Depending on the size of your database and the rig you are running the restore on it may take several hours to complete. Especially if you have the .dmp on the same drive you are restoring too. Try starting with a System Backup those are usually smaller and restore much quicker than an Analytics Backup.

           

          I hope this helps, if not post the results of verbose messaging and it may help us find the bug.

          • Re: Restoring postgres database freezing
            skushawaha

            Hi erinclements,

             

            Try out postgres version 9.3. Hopefully this will resolve your data restoration issue.

             

            Thanks,

            Sanjay

            • Re: Restoring postgres database freezing
              RyanSe

              How large is the DMP file you are working with?

               

              I ask because if you are restoring through the UI it will act like the pgadmin is not responding but technically it is working on the database in the background. If you open up resource monitor and check out the 4th Tab called "Disk" you'd likely notice high Disk Activity. In the second section you can see the file path where the disk activity is taking place (probably something like this (C:\Program Files (x86)\PostgreSQL\9.0\data\base\11818), you can then navigate to that folder and monitor it's overall size.

               

              Back in 2013, our "server" was actually way underpowered and would take 36+ hours to do a restore of one of our larger databases with a 10GB+ dmp file.

               

              Worth checking out.

              • Re: Restoring postgres database freezing
                erinclements

                Thank you so much to all of you!  I think a combined effort of all of this are what helped clear everything up.  I upgraded, added the "Clean before restore" option, and gave it a little more time, and it was able to restore!!  Thank you all again!