Обсуждение: Restiring pg_dump text file

This is not the proper way to restore the dump in database and also no need to drop the db instance. See the below steps you can get detail information of dumping/restoring with proper log information.

(1)   Create database in you database instance.

create database <database name>;

(2)   Below commands will give you detail information of dumping/restoring with proper log information.

Dump command:

============

pg_dump -f <out_put_filename> -Fc –v -h <host_name> -P <database_port_number> -U <database_username> DATABASENAME

Restore command:
===============
(2) pg_restore - d <connect_database_name> -Fc -v  -h <host_name_ip> -p <database_port_number>  -U <Database_user_name> [DUMP_FILE_NAME]

Method2:-

      We can able to take dump and restore of database in this way also.

Dump command:-

==============

pg_dump.exe -U postgres (user name) -p 5464 (port)  postgres (database) > post.sql

Restore command:-

     ==============

psql -d raghu (new database) -p 5464 (port) -U postgres (username) -f post.sql (dump file)

Thanks & Regards,
Raghu Ram

> I've backed up my DB via 'pg_dump dbname' using no options. In order to
> restore it via psql, do I first need to drop the db instance and recreate
> it?
>
> Thanks in advance for your help.
>
> -Mike
>
>
>
yes, you have to drop the original db, then use psql -f to restore it.

On Tue, Jun 30, 2009 at 9:36 PM, Scott Mead<scott.lists@enterprisedb.com> wrote:
>
>
>
> On Tue, Jun 30, 2009 at 11:26 PM, <donghe@caltech.edu> wrote:
>>
>> > I've backed up my DB via 'pg_dump dbname' using no options. In order to
>> > restore it via psql, do I first need to drop the db instance and
>> > recreate
>> > it?
>> >
>> > Thanks in advance for your help.
>> >
>> > -Mike
>> >
>> >
>> >
>> yes, you have to drop the original db, then use psql -f to restore it.
>
>   You don't have to drop it, you can create another db by any other name and
> re-run the sql file there.  The only time you have to worry is if you use
> the -C (--create) option to pg_dump.  This would actually issue a 'create
> database' command in the sql dump.  But for you, you can restore to another
> database if you would like.  That being said, if this is production, I would
> be careful to not 'accidentally' run the sql file against the wrong
> database, so for your own sanity, you may want to just drop the db.  But
> there's no *requirement* that you do so.

If you've got the space, an easy trick is to rename the old db to
something else, create a new one in its place and restore to that.