Hi Amitabh, I just added the -O option in the middle as detailed below.
pg_restore -c -O -h localhost -U username -d db dumpfile.pg; However, I get the same list of errors as below. The weird thing is, it appears to be working fine. I could not able to comprehend the error list! I don't have any clue about it! For your information, I don't think am running using the superuser account! But just seem like a normal user account.. Could this lead to few issues on privileges? Any help would be greatly appreciated. Thanks and Regards, Siva. From: Amitabh Kant [mailto:[email protected]] Sent: Monday, August 08, 2011 2:42 PM To: Siva Palanisamy Cc: John R Pierce; [email protected] Subject: Re: [GENERAL] Backup & Restore a database in PostgreSQL Hi Siva Not sure if it would help, but try passing -O in your pg_restore command. Amitabh On Mon, Aug 8, 2011 at 2:04 PM, Siva Palanisamy <[email protected]<mailto:[email protected]>> wrote: 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: [email protected]<mailto:[email protected]> [mailto:[email protected]<mailto:[email protected]>] On Behalf Of John R Pierce Sent: Monday, August 08, 2011 1:45 PM To: [email protected]<mailto:[email protected]> 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<http://dumpfile.pg> dbname pg_restore -c -h localhost -U user -d dbname dumpfile.pg<http://dumpfile.pg> -- john r pierce N 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list ([email protected]<mailto:[email protected]>) 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. ----------------------------------------------------------------------------------------------------------------------- -- Sent via pgsql-general mailing list ([email protected]<mailto:[email protected]>) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
