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 <siv...@hcl.com> 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: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce > Sent: Monday, August 08, 2011 1:45 PM > To: pgsql-general@postgresql.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@postgresql.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. > > > ----------------------------------------------------------------------------------------------------------------------- > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
