Unable to restore postgres-dump-Version-8.2.4

[clearissues]

Hi All,

I have a python script which takes dump of postgres and restores the same.

When i take a dump there is no problem.
command to take dump:

pg_dump -b -c -C --format=c -d -h <<hostname>> -p 5432 -U postuser -f /tmp/april_23/abc.sql abc

Restore command:

pg_restore -c --format=c -h <<hostname>> -p 5432 -U postuser -d sfdb /tmp/april_23/abc.sql

While restoring the dump i got into following errors...

Traceback (most recent call last):

   File "./db.py", line 122, in restore_db
    ret = app.util.executeCommand(self.cmd)

 
 RuntimeError: pg_restore -c --format=c -h <<hostname>> -p 5432 -U postuser -d abc /tmp/april_23/abc.sql failed with exit code 256
Std Err: pg_restore: [archiver (db)] Error while PROCESSING TOC:
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 2229; 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
 
Stdoutput:
 
Unable to restore db

Any help would be of great.

Thanks
Prathap

[rski]

I'm afraid you should give more information but this error is interesting

"ERROR: must be owner of schema public Command was: DROP SCHEMA public;".

Who did backup and who is trying to restore. What are database privileges. Why are you trying to delete public schema?

[clearissues]

I'm afraid you should give more information but this error is interesting

Who did backup and who is trying to restore. What are database privileges. Why are you trying to delete public schema?

backup and restore are done by "postgres" user. I have just incorporated the pgsq_restore snippet in my python script.

The python script is invoked as 'root' which does the dump and restore.
Code snippet:

 def dump_db(self):
        if not os.path.exists(self.db_backup_file_path):
            try:
                os.makedirs(self.db_backup_file_path)
            except:
                self.printErrorAndExit("Unable to create path for DB backup: %s " %self.db_backup_file_path, 3)
 
        if self.db_name != '':
            try:
                self.cmd = 'pg_dump -b -c -C --format=c -d -h '+ self.db_host
                self.cmd = self.cmd +' -p '+ self.db_port
                self.cmd = self.cmd +' -U '+ self.db_user
                self.cmd = self.cmd +' -f '+ self.db_backup_file
                self.cmd = self.cmd +' '+ self.db_name
                print self.cmd
                ret = app.util.executeCommand(self.cmd)
            except:
                self.printErrorAndExit("Unable to take DB backup\n", 4)
 
======================================================
  # restores database
    def restore_db(self):
        if self.force:
            option = 'YES'
            print "Force option selected, so proceeding to replace the existing data by the dump you have selected."
        else:
            option  = app.util.readFromUser("Existing data will be replaced by the dump you have selected. Do you want to proceed? YES/NO", 'YES',  ['YES', 'NO'])
            if option == 'YES':
                pass
            else:
                self.printErrorAndExit("Exiting restore db on user request..")
 
        if not os.path.exists(self.db_backup_file):
            self.printErrorAndExit("Unable to find DB backup file: %s " %self.db_backup_file_path, 5)
 
        if self.db_name != '':
            try:
                self.cmd = 'pg_restore -c --format=c -h '+ self.db_host
                self.cmd = self.cmd +' -p '+ self.db_port
                self.cmd = self.cmd +' -U '+ self.db_user
                self.cmd = self.cmd +' -d '+ self.db_name
                self.cmd = self.cmd + ' ' + self.db_backup_file
                print self.cmd
                print "Restoring db, please wait...."
                ret = app.util.executeCommand(self.cmd)
            except:
                print " ".join(app.util.getStackTraceAndException())
                self.printErrorAndExit("Unable to restore db\n", 6)
        else:
            self.printErrorAndExit("Unable to restore db, due to empty db name\n", 7)