Re: 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 <siva_p(at)hcl(dot)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-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.
>
>
> -----------------------------------------------------------------------------------------------------------------------
>
> --
> 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
>