Dump and Restore in postgresql-8.3.6-1PGDG

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

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