Thread: Dump and Restore in postgresql-8.3.6-1PGDG

Dump and Restore in postgresql-8.3.6-1PGDG

From
"suresh adapa"
Date:
=20

I am on Linux using postgresql-8.3.6-1PGDG

=20

Taking Dump using

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

export PGPASSWORD=3Dsuresha && pg_dump -b -c -C --format=3Dc -h localhost -p
5432 -U suresha -f /tmp/dump/db/suresha.sql suresha

=20

Trying to Restore the same like

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

[root@cu018 scripts]# pg_restore -c --format=3Dc -h localhost -p 5432 -U
suresha -d suresha /tmp/dump/db/suresha.sql

Password:

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 6; 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 2663; 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: WARNING:  no privileges could be revoked for "public"

pg_restore: WARNING:  no privileges could be revoked for "public"

pg_restore: WARNING:  no privileges were granted for "public"

pg_restore: WARNING:  no privileges were granted for "public"

WARNING: errors ignored on restore: 3

=20

Wondering how can I suppress the Error and other messages seen above.

=20

=20

Thanks

SureshA

Re: Dump and Restore in postgresql-8.3.6-1PGDG

From
Scott Mead
Date:
On Thu, Apr 15, 2010 at 12:47 PM, Scott Mead
<scott.lists@enterprisedb.com>wrote:

> On Thu, Apr 15, 2010 at 10:33 AM, suresh adapa <suresha@collab.net> wrote:
>
>>
>>
>> I am on Linux using postgresql-8.3.6-1PGDG
>>
>>
>>
>> Taking Dump using
>>
>> =================
>>
>> export PGPASSWORD=suresha && pg_dump -b -c -C --format=c -h localhost -p
>> 5432 -U suresha -f /tmp/dump/db/suresha.sql suresha
>>
>>
>>
>> Trying to Restore the same like
>>
>> =======================
>>
>> [root@cu018 scripts]# pg_restore -c --format=c -h localhost -p 5432 -U
>> suresha -d suresha /tmp/dump/db/suresha.sql
>>
>> Password:
>>
>> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>>
>> pg_restore: [archiver (db)] Error from TOC entry 6; 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 2663; 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: WARNING:  no privileges could be revoked for "public"
>>
>> pg_restore: WARNING:  no privileges could be revoked for "public"
>>
>> pg_restore: WARNING:  no privileges were granted for "public"
>>
>> pg_restore: WARNING:  no privileges were granted for "public"
>>
>> WARNING: errors ignored on restore: 3
>>
>>
>>
>> Wondering how can I suppress the Error and other messages seen above.
>>
>
>
> This is because you're restoring into the same database that you backed up
> from.  I recommend creating a new database 'suresha_new'
>
>  Then, restore into that new database.  Once you've restored, you can:
>
>
>   ALTER DATABASE suresha RENAME TO suresha_orig;
>
>   ALTER DATABASE suresha_new RENAME TO suresha;
>
> --Scott
>


  BTW -- THis is not a bug, you should have posted to the -general or -admin
list, not the -bugs list.

--Scott

>
>>
>>
>>
>> Thanks
>>
>> SureshA
>>
>
>

Re: Dump and Restore in postgresql-8.3.6-1PGDG

From
Scott Mead
Date:
On Thu, Apr 15, 2010 at 10:33 AM, suresh adapa <suresha@collab.net> wrote:

>
>
> I am on Linux using postgresql-8.3.6-1PGDG
>
>
>
> Taking Dump using
>
> =================
>
> export PGPASSWORD=suresha && pg_dump -b -c -C --format=c -h localhost -p
> 5432 -U suresha -f /tmp/dump/db/suresha.sql suresha
>
>
>
> Trying to Restore the same like
>
> =======================
>
> [root@cu018 scripts]# pg_restore -c --format=c -h localhost -p 5432 -U
> suresha -d suresha /tmp/dump/db/suresha.sql
>
> Password:
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>
> pg_restore: [archiver (db)] Error from TOC entry 6; 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 2663; 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: WARNING:  no privileges could be revoked for "public"
>
> pg_restore: WARNING:  no privileges could be revoked for "public"
>
> pg_restore: WARNING:  no privileges were granted for "public"
>
> pg_restore: WARNING:  no privileges were granted for "public"
>
> WARNING: errors ignored on restore: 3
>
>
>
> Wondering how can I suppress the Error and other messages seen above.
>


This is because you're restoring into the same database that you backed up
from.  I recommend creating a new database 'suresha_new'

 Then, restore into that new database.  Once you've restored, you can:


  ALTER DATABASE suresha RENAME TO suresha_orig;

  ALTER DATABASE suresha_new RENAME TO suresha;

--Scott

>
>
>
>
> Thanks
>
> SureshA
>