Subject: Re: pg_restore
To: pgsql-admin@lists.postgresql.org
References: 
 <CALm_VjjLrrWiA92HainW3pdQLRMfBQ4qE2tnd-55A7WUorgyGw@mail.gmail.com>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <c576d9d8-afbb-dbaa-92c9-586cfb860b68@gmail.com>
Date: Thu, 11 Apr 2019 18:12:44 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.6.1
MIME-Version: 1.0
In-Reply-To: 
 <CALm_VjjLrrWiA92HainW3pdQLRMfBQ4qE2tnd-55A7WUorgyGw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------63354FF5F29AAB86E9D7F5E1"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------63354FF5F29AAB86E9D7F5E1
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

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.

--------------63354FF5F29AAB86E9D7F5E1
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    On 4/11/19 5:09 PM, rammohan ganapavarapu wrote:<br>
    <blockquote type="cite"
cite="mid:CALm_VjjLrrWiA92HainW3pdQLRMfBQ4qE2tnd-55A7WUorgyGw@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div dir="ltr">
          <div dir="ltr">
            <div dir="ltr">Hi,
              <div><br>
              </div>
              <div>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?</div>
              <div><br>
              </div>
              <div>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 &lt;&gt; pg_backend_pid();"<br>
              </div>
              <div><br>
              </div>
              <div>pg_restore" -h localhost  -U ${RUN_USER}  -d mydb -j
                "${JOBS}" -x -O -C -c --if-exist -v pgdump.dump <br>
              </div>
              <div><br>
              </div>
              <div>But i am getting below error:</div>
              <div><br>
              </div>
              <div>
                <div>pg_restore: connecting to database for restore</div>
                <div>pg_restore: dropping DATABASE mydb</div>
                <div>pg_restore: [archiver (db)] Error while PROCESSING
                  TOC:</div>
                <div>pg_restore: [archiver (db)] Error from TOC entry
                  4312; 1262 16384 DATABASE mydb mydb</div>
                <div>pg_restore: [archiver (db)] could not execute
                  query: ERROR:  cannot drop the currently open database</div>
                <div>    Command was: DROP DATABASE IF EXISTS mydb;</div>
              </div>
              <div><br>
              </div>
              <div>So what is the best way to restore? I am using 9.6
                version.</div>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <br>
    I made the same mistake you did, by doing "-d mydb".  You need to
    specify the postgres database.<br>
    <br>
    This works for me:<br>
    <tt>export PGUSER=${RUN_USER}</tt><tt><br>
    </tt><tt>export PGHOST=localhost</tt><tt><br>
    </tt><tt>DB=mydb</tt><tt><br>
    </tt><tt>pg_restore -xOvcC --if-exists --jobs=${JOBS} </tt><tt><b>-d
        postgres</b></tt><tt> ${DB} 2&gt; ${DB}_restore.log</tt><tt><br>
    </tt><br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------63354FF5F29AAB86E9D7F5E1--