Patch: forcing object owner in TOC file
Hello,
I've created small patch which allows me easily change owner during
pg_restore with backup in custom format (-Fc).
This simplifies migration when users on new server are different that
ones in backup file.
First, I get TOC file:
pg_restore -Fc -l mybackup.custom > mybackup.toc
Then, I can alter owner of any object by adding username after TOC Id,
for ex. changing this:
173; 1259 25139 TABLE public data quaker
into this:
173 quaker1; 1259 25139 TABLE public data quaker
By above line I forced quaker1 to be owner of public.data table after restore.
Then I do normal restore using modified TOC:
$ pg_restore -Fc mybackup.custom -L mybackup.toc -d quaker
pg_restore: [archiver] WARNING: altering owner for TABLE data to quaker1
and have public.data TABLE owned by quaker1 user.
Patch attached.
--
Piotr Gasidło
Attachments:
forced-owner-v1.patchapplication/octet-stream; name=forced-owner-v1.patchDownload
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 7f47a7c..835f323 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -1137,6 +1137,7 @@ SortTocFromFile(Archive *AHX, RestoreOptions *ropt)
int buflen;
char *cmnt;
char *endptr;
+ char *owner = NULL;
DumpId id;
TocEntry *te;
@@ -1172,6 +1173,18 @@ SortTocFromFile(Archive *AHX, RestoreOptions *ropt)
continue;
}
+ /* If there is any string after ID assume it as "forced" owner. */
+ while (*endptr) {
+ if (!isspace(*endptr) && owner == NULL)
+ owner = endptr;
+ else if (isspace(*endptr) && owner != NULL)
+ {
+ *endptr = '\0';
+ break;
+ }
+ *endptr++;
+ }
+
/* Find TOC entry */
te = getTocEntryByDumpId(AH, id);
if (!te)
@@ -1181,6 +1194,14 @@ SortTocFromFile(Archive *AHX, RestoreOptions *ropt)
/* Mark it wanted */
ropt->idWanted[id - 1] = true;
+ /* Alter TOC entry owner, if found "forced" owner. */
+ if (owner != NULL)
+ {
+ write_msg(modulename, "WARNING: forcing owner for %s %s to %s\n", te->desc, te->tag, owner);
+ free(te->owner);
+ te->owner = strdup(owner);
+ }
+
/*
* Move each item to the end of the list as it is selected, so that
* they are placed in the desired order. Any unwanted items will end
On 10/15/2012 07:59 AM, Piotr Gasidło wrote:
Hello,
I've created small patch which allows me easily change owner during
pg_restore with backup in custom format (-Fc).
This simplifies migration when users on new server are different that
ones in backup file.First, I get TOC file:
pg_restore -Fc -l mybackup.custom > mybackup.toc
Then, I can alter owner of any object by adding username after TOC Id,
for ex. changing this:173; 1259 25139 TABLE public data quaker
into this:
173 quaker1; 1259 25139 TABLE public data quaker
By above line I forced quaker1 to be owner of public.data table after restore.
Then I do normal restore using modified TOC:
$ pg_restore -Fc mybackup.custom -L mybackup.toc -d quaker
pg_restore: [archiver] WARNING: altering owner for TABLE data to quaker1and have public.data TABLE owned by quaker1 user.
Sorry, but this doesn't strike me as a very good idea at all. Why not
just alter the table ownership after the restore is done? If we start
allowing stuff other than the TOC ID to be specified in the list file
the modifications will never end. And if we do want to do that then it
needs to be designed properly. For example, one change that seems far
more important to me than changing the owner is to provide for restoring
stuff to a different schema.
BTW, I realize your patch is small, but it's usually a good idea to
discuss an idea on the mailing list before sending in a patch.
cheers
andrew
2012/10/15 Andrew Dunstan <andrew@dunslane.net>:
Sorry, but this doesn't strike me as a very good idea at all. Why not just
alter the table ownership after the restore is done?
Yes, I could restore, wrote later ALTER ... OWNER TO ... - but this
method allowed me to do it quicker.
If we start allowing
stuff other than the TOC ID to be specified in the list file the
modifications will never end.
Understood, sounds reasonably.
BTW, I realize your patch is small, but it's usually a good idea to discuss
an idea on the mailing list before sending in a patch.
I've new here, next time I will send idea and wait for response before
sending any patch.
--
Piotr Gasidło