Re: Backup & Restore a database in PostgreSQL

Hi John,

Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I got the below list of errors! Please help me out on this.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of function public.plpgsql_call_handler
Command was: DROP FUNCTION public.plpgsql_call_handler();
pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to drop procedural language
Command was: DROP PROCEDURAL LANGUAGE plpgsql;
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public
Command was: DROP SCHEMA public;
pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: [archiver (db)] Error from TOC entry 1566; 0 0 COMMENT SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public
Command was: COMMENT ON SCHEMA public IS 'Standard public schema';
pg_restore: [archiver (db)] Error from TOC entry 276; 2612 16387 PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to create procedural language
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: [archiver (db)] Error from TOC entry 14; 1255 16384 FUNCTION plpgsql_call_handler() postgres
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c
Command was: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '$libdir/plpgsql', 'plpgsql_call_handler'
LANGUAG...
pg_restore: WARNING: no privileges could be revoked
pg_restore: WARNING: no privileges could be revoked
pg_restore: WARNING: no privileges were granted
pg_restore: WARNING: no privileges were granted
WARNING: errors ignored on restore: 7

Thanks and Regards,
Siva.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of John R Pierce
Sent: Monday, August 08, 2011 1:45 PM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] 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

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

::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------

The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.

-----------------------------------------------------------------------------------------------------------------------