How to modify dump files created by pg_dump

Started by 任洪彩almost 13 years ago9 messagesgeneral

qdurenhongcai@163.com

almost 13 years ago

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?

Best wishes.

Raymond O'Donnell

rod@iol.ie

almost 13 years ago

In reply to: 任洪彩 (#1)

Re: 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

任洪彩

qdurenhongcai@163.com

almost 13 years ago

In reply to: Raymond O'Donnell (#2)

Re: How to modify dump files created by pg_dump

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

在 2013-06-04 23:40:50，"Raymond O'Donnell" <rod@iol.ie> 写道：

Show quoted text

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

Raymond O'Donnell

rod@iol.ie

almost 13 years ago

In reply to: 任洪彩 (#3)

Re: How to modify dump files created by pg_dump

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

任洪彩

qdurenhongcai@163.com

almost 13 years ago

In reply to: Raymond O'Donnell (#4)

Re: How to modify dump files created by pg_dump

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Raymond O'Donnell

rod@iol.ie

almost 13 years ago

In reply to: 任洪彩 (#5)

Re: How to modify dump files created by pg_dump

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

almost 13 years ago

In reply to: 任洪彩 (#5)

Re: How to modify dump files created by pg_dump

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

任洪彩

qdurenhongcai@163.com

almost 13 years ago

In reply to: Adrian Klaver (#7)

Re: How to modify dump files created by pg_dump

Hmm...It seem like a wonderful solution.
But, i can't change the way to restore.
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.

在 2013-06-06 08:30:44，"Adrian Klaver" <adrian.klaver@gmail.com> 写道：

Show quoted text

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

Adrian Klaver

adrian.klaver@aklaver.com

almost 13 years ago

In reply to: 任洪彩 (#8)

Re: How to modify dump files created by pg_dump

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general