Re: Backup & Restore a database in PostgreSQL

On 08/08/11 1:01 AM, Siva Palanisamy wrote:
>
> Hi All,
>
> I am also a newbie here! I need to backup a database and restore it
> into the target machine where the database may already present or
> might not. If it exists, I want the "restore" command to overwrite,
> otherwise, just create a new one.
>
> I tried using the commands:
>
> (1) BACKUP: pg_dump -h localhost -U username db > dump_file.out;
>
> RESTORE: pg_dump -h localhost -U username db < dump_file.out;
>
> (2) BACKUP:
>
> pg_dump -h localhost -U username -Ft db > dump_file.tar;
>
> RESTORE:
>
> pg_restore -h localhost -U username -d db dump_file.tar;
>
> "Backup" worked perfect in the above 2 scenarios whereas "restore"
> didn't yield the exact results. For testing it, I took the back-up and
> intentionally deleted few records in a table. I then restored the
> database in the same machine where the database exists. I expected the
> deleted records to come back as I was restoring the one which has the
> complete data. It didn't yield proper results. And I wonder why..
>
> I believe I might be doing something marginally wrong. I would
> appreciate if any geek over here to guide me the "restore" command
> properly.
>
>

specify -c on the pg_restore, and it will drop the database objects and
recreate them

pg_dump -Fc -h localhost -U user -f dumpfile.pg dbname

pg_restore -c -h localhost -U user -d dbname dumpfile.pg

--
john r pierce N 37, W 122
santa cruz ca mid-left coast