[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-admin
Subject: Re: pg_restore
From: Ron <ronljohnsonjr () gmail ! com>
Date: 2019-04-11 23:16:55
Message-ID: 7da46322-6a20-ef30-2a95-1cafbc8e5e75 () gmail ! com
[Download RAW message or body]
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.
[Attachment #3 (text/html)]
<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 <> 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> ${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>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic