Thread: How to modify dump files created by pg_dump

On 04/06/2013 09:44, 任洪彩 wrote:
> Hi, guys
>
> I have met a problem that do confused me.
>
> I created a dump file by pg_dump from database named "DBRNWHSB" with
> parameters -b -o -Fc -Z 0.
> As you know, at the beginning of the dump file contains some SQL
> statements for create the database "DBRNWHSB".
>
> So here comes the question, I want let these SQL statements to create
> another database, e.g. named "myDB". In that case, when restore the dump
> i can restore the modified dump file with "-C" that "myDB" can be founded.
>
> I have tried to modify the dump file manually. But the dump file may
> caused invalid.
>
> # /opt/nokiasiemens/SS_Postgres/bin/pg_restore -C -h CFPU-0 -p 5433 -U
> _qnrnwdbman -d template1 -e -v /root/horen/rnwdump/DBRNWHSB.pgdump
> pg_restore: connecting to database for restore
> pg_restore: implied data-only restore
>
> I wonder is there any way to modify the dump file after create or i can
> specify the database name when dump?

I think you're asking for trouble trying to modify the dump file manually.

If I understand, you want to restore into a database with a different
name to the original one, is that correct? If so, then simply create the
database manually, then restore into it with the -d option:

  pg_restore -d <my-other-database> <dump-file>

HTH,

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 05/06/2013 03:02, 任洪彩 wrote:
> Hi,
> Yes, you are right.
> But -d option not suitable for me. Because our flatform use -C option
> and -d template1 option to create the database described in the dump
> file to restore the dump file.
>
> The whole command like this:
> # /opt/nokiasiemens/SS_Postgres/bin/pg_restore -C -h CFPU-0 -p 5433 -U
> _qnrnwdbman -d template1 -e -v /root/horen/rnwdump/DBRNWHSB.pgdump

Hmmmm, OK. Could you rename the original database before dumping it, so
that its name matches the new one?

Ray.



--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

On 05/06/2013 13:20, 私人邮箱 wrote:
> no，I can't...
>
> Actually the dump file comes from another site...
>
>
> 在 2013-6-5，19:28，Raymond O'Donnell <rod@iol.ie> 写道：
>
>> On 05/06/2013 03:02, 任洪彩 wrote:
>>> Hi,
>>> Yes, you are right.
>>> But -d option not suitable for me. Because our flatform use -C option
>>> and -d template1 option to create the database described in the dump
>>> file to restore the dump file.
>>>
>>> The whole command like this:
>>> # /opt/nokiasiemens/SS_Postgres/bin/pg_restore -C -h CFPU-0 -p 5433 -U
>>> _qnrnwdbman -d template1 -e -v /root/horen/rnwdump/DBRNWHSB.pgdump
>>
>> Hmmmm, OK. Could you rename the original database before dumping it, so
>> that its name matches the new one?

Sounds like you're going to have to script it somehow then.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

no，I can't...

Actually the dump file comes from another site...


在 2013-6-5，19:28，Raymond O'Donnell <rod@iol.ie> 写道：

> On 05/06/2013 03:02, 任洪彩 wrote:
>> Hi,
>> Yes, you are right.
>> But -d option not suitable for me. Because our flatform use -C option
>> and -d template1 option to create the database described in the dump
>> file to restore the dump file.
>>
>> The whole command like this:
>> # /opt/nokiasiemens/SS_Postgres/bin/pg_restore -C -h CFPU-0 -p 5433 -U
>> _qnrnwdbman -d template1 -e -v /root/horen/rnwdump/DBRNWHSB.pgdump
>
> Hmmmm, OK. Could you rename the original database before dumping it, so
> that its name matches the new one?
>
> Ray.
>
>
>
> --
> Raymond O'Donnell :: Galway :: Ireland
> rod@iol.ie

On 06/05/2013 05:20 AM, 私人邮箱 wrote:
> no，I can't...
>
> Actually the dump file comes from another site...

So use the -f option to pg_restore to save the custom format file to  a
text file.  Edit the text file and then run using psql.

Ex:

pg_restore -C -f  whatever_name.sql  /root/horen/rnwdump/DBRNWHSB.pgdump

Open whatever_name.sql
    Change the database name wherever needed.

psql -h CFPU-0 -p 5433 -U_qnrnwdbman -d template1 -f whatever_name.sql

>
>
> 在 2013-6-5，19:28，Raymond O'Donnell <rod@iol.ie> 写道：
>
>> On 05/06/2013 03:02, 任洪彩 wrote:
>>> Hi,
>>> Yes, you are right.
>>> But -d option not suitable for me. Because our flatform use -C option
>>> and -d template1 option to create the database described in the dump
>>> file to restore the dump file.
>>>
>>> The whole command like this:
>>> # /opt/nokiasiemens/SS_Postgres/bin/pg_restore -C -h CFPU-0 -p 5433 -U
>>> _qnrnwdbman -d template1 -e -v
>>
>> Hmmmm, OK. Could you rename the original database before dumping it, so
>> that its name matches the new one?
>>
>> Ray.
>>
>>
>>
>> --
>> Raymond O'Donnell :: Galway :: Ireland
>> rod@iol.ie
>
>
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 06/05/2013 06:17 PM, 任洪彩 wrote:
> Hmm...It seem like a wonderful solution.
> But, i can't change the way to restore.

Then you will not be able to do what you want.

> For some reason, the way to restore as follows.
> pg_restore -C -h xxx -p xxx -U xxx -d template1 -e -v xxx.pgdump
>
> Does any one can give a clarification about whether the dump file
> support modify?
> By the way, dump with these options..-b -o -Fc -Z 0...that i can't chage.
>
>



--
Adrian Klaver
adrian.klaver@gmail.com