Обсуждение: pg_restore

Hey,
I have upgraded my pgostgresql from 7.1.2 to 7.1.3 as the pg_restore
is not working properly in 7.1.2
I took the database backup with pg_dump.
After upgrading to 7.1.3, I have restored the database with
pg_restore.
It is restoring , but giving the following error with sequences and
recovery is failing

Archiver(db): Could not execute query. Code = 7. Explanation from
backend: 'ERROR:  mysequence.setval: you don't have permissions to set
sequence mysequence

Why it is giving the error that the perssion is not there. Actually, I
'm restoring from pgsql super user account.

Can anybody help me how to fix it. Thanks in advance

On Wed, 2002-02-06 at 16:47, Srinivasa Rao Chava wrote:
> Hey,
> I have upgraded my pgostgresql from 7.1.2 to 7.1.3 as the pg_restore
> is not working properly in 7.1.2
> I took the database backup with pg_dump.
> After upgrading to 7.1.3, I have restored the database with
> pg_restore.
> It is restoring , but giving the following error with sequences and
> recovery is failing

Hi.

I'm not any guru, but I think if you make the pg_dump/pg_restore with
the -x (whitout acl's), and -O (whitout owner) an, finally, -i
(ignore-version), will possible to make this backup (I think).

Have a nice day ;-)
TooManySecrets

--
Manuel Trujillo         manueltrujillo@dorna.es
Technical Engineer      http://www.motograndprix.com
Dorna Sports S.L.       +34 93 4702864

s.chava@wcom.com (Srinivasa Rao Chava) writes:
> Archiver(db): Could not execute query. Code = 7. Explanation from
> backend: 'ERROR:  mysequence.setval: you don't have permissions to set
> sequence mysequence

Hmm, I think I see an oversight in pg_dump.  Exactly what options did
you give to pg_dump and pg_restore?

            regards, tom lane

Srinivasa R Chava <s.chava@wcom.com> writes:
> [ permissions error when restoring a sequence ]

I've applied the attached patch to 7.2 sources to fix this problem.
You could probably adapt it to 7.1.3 pg_dump, but I haven't looked.

            regards, tom lane


*** src/bin/pg_dump/pg_backup_archiver.c.orig    Fri Jan 18 14:18:18 2002
--- src/bin/pg_dump/pg_backup_archiver.c    Wed Feb  6 12:27:50 2002
***************
*** 311,319 ****

          if ((reqs & REQ_SCHEMA) != 0)    /* We want the schema */
          {
-             /* Reconnect if necessary */
-             _reconnectAsOwner(AH, NULL, te);
-
              ahlog(AH, 1, "creating %s %s\n", te->desc, te->name);
              _printTocEntry(AH, te, ropt, false);
              defnDumped = true;
--- 311,316 ----
***************
*** 2032,2037 ****
--- 2029,2037 ----
  _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isData)
  {
      char       *pfx;
+
+     /* Reconnect if necessary */
+     _reconnectAsOwner(AH, NULL, te);

      if (isData)
          pfx = "Data for ";
*** src/bin/pg_dump/pg_dump.c.orig    Fri Jan 25 13:49:31 2002
--- src/bin/pg_dump/pg_dump.c    Wed Feb  6 12:27:50 2002
***************
*** 4765,4771 ****
                            last, (called ? "true" : "false"));

          ArchiveEntry(fout, tbinfo.oid, tbinfo.relname, "SEQUENCE SET", NULL,
!                      query->data, "" /* Del */ , "", "", NULL, NULL);
      }

      if (!dataOnly)
--- 4765,4772 ----
                            last, (called ? "true" : "false"));

          ArchiveEntry(fout, tbinfo.oid, tbinfo.relname, "SEQUENCE SET", NULL,
!                      query->data, "" /* Del */ , "", tbinfo.usename,
!                      NULL, NULL);
      }

      if (!dataOnly)