Re: pg_restore

Ron <ronljohnsonjr@xxxxxxxxx> · Thu, 11 Apr 2019 18:12:44 -0500



    On 4/11/19 5:09 PM, rammohan ganapavarapu wrote:

    
            Hi,
              

              I have a custome database called mudb along with
                default databases in my postgresql cluster, i am trying
                to restore mydb from a custom format pg_dump file, what
                is the procedure to do it?
              

              psql -t -h localhost  -U ${RUN_USER}  -c "SELECT
                pg_terminate_backend(pg_stat_activity.pid) FROM
                pg_stat_activity WHERE pg_stat_activity.datname = 'mydb'
                AND pid <> pg_backend_pid();"

              
              pg_restore" -h localhost  -U ${RUN_USER}  -d mydb -j
                "${JOBS}" -x -O -C -c --if-exist -v pgdump.dump 

              
              But i am getting below error:
              

                pg_restore: connecting to database for restore
                pg_restore: dropping DATABASE mydb
                pg_restore: [archiver (db)] Error while PROCESSING
                  TOC:
                pg_restore: [archiver (db)] Error from TOC entry
                  4312; 1262 16384 DATABASE mydb mydb
                pg_restore: [archiver (db)] could not execute
                  query: ERROR:  cannot drop the currently open database
                    Command was: DROP DATABASE IF EXISTS mydb;
              
              
              So what is the best way to restore? I am using 9.6
                version.
            
          
    I made the same mistake you did, by doing "-d mydb".  You need to
    specify the postgres database.

    
    This works for me:

    export PGUSER=${RUN_USER}

    export PGHOST=localhost

    DB=mydb

    pg_restore -xOvcC --if-exists --jobs=${JOBS} -d
        postgres ${DB} 2> ${DB}_restore.log

    
    -- 

      Angular momentum makes the world go 'round.