Thread: 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

Hi John,

Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I
gotthe 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@postgresql.org [mailto:pgsql-general-owner@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.

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

Siva --


>
> Thanks a lot for your reply. As usual Backup worked perfectly. When I tried restore using the command you provided, I
gotthe 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();

Clue #1 -- you are not running as the correct user for the restore into this database.

> 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;

Clue #2!

So you need to change to being the postgres user that created the database, which presumably has the proper permissions
todrop these various entities. 

<snipped redundant error messages?

HTH,

Greg Williamson

Siva Palanisamy wrote:
> 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?

The messages you get are to be expected if you try to restore a database
without the necessary permissions.

It worked for the objects that the user owns.

If you want to backup just a few tables or a certain schema in the
database,
use the -t or -n flags.
If you want to backup the whole database, make sure you have appropriate
permissions when you restore.

Yours,
Laurenz Albe