Subject: Re: pg_restore
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
References: 
 <CALm_VjjLrrWiA92HainW3pdQLRMfBQ4qE2tnd-55A7WUorgyGw@mail.gmail.com>
 <c576d9d8-afbb-dbaa-92c9-586cfb860b68@gmail.com>
Message-ID: <7da46322-6a20-ef30-2a95-1cafbc8e5e75@gmail.com>
Date: Thu, 11 Apr 2019 18:16:55 -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: <c576d9d8-afbb-dbaa-92c9-586cfb860b68@gmail.com>
Content-Type: multipart/alternative;
 boundary="------------CA9DE6B69FBD004D80C4795D"
Content-Language: en-US
Precedence: bulk

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

On 4/11/19 6:12 PM, Ron wrote:
> 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

But it just occurred to me that I used directory backups, not custom 
backups, so this probably won't work for you.

-- 
Angular momentum makes the world go 'round.

--------------CA9DE6B69FBD004D80C4795D
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 6:12 PM, Ron wrote:<br>
    <blockquote type="cite"
      cite="mid:c576d9d8-afbb-dbaa-92c9-586cfb860b68@gmail.com">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      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></tt><br>
    </blockquote>
    <br>
    But it just occurred to me that I used directory backups, not custom
    backups, so this probably won't work for you.<br>
    <br>
    <div class="moz-signature">-- <br>
      Angular momentum makes the world go 'round.</div>
  </body>
</html>

--------------CA9DE6B69FBD004D80C4795D--