KaiGai Kohei wrote:
> > What happens when
> > there is no entry in pg_largeobject_metadata for a specific row?
>
> In this case, these rows become orphan.
> So, I think we need to create an empty large object with same LOID on
> pg_migrator. It makes an entry on pg_largeobject_metadata without
> writing anything to the pg_largeobject.
> I guess rest of migrations are not difference. Correct?
Agreed. I have modified pg_migrator with the attached patch which
creates a script that adds default permissions for all large object
tables.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
? tools
? log
? src/pg_migrator
Index: src/info.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/info.c,v
retrieving revision 1.25
diff -c -r1.25 info.c
*** src/info.c 10 Dec 2009 23:14:25 -0000 1.25
--- src/info.c 13 Dec 2009 01:17:37 -0000
***************
*** 480,486 ****
"SELECT DISTINCT probin "
"FROM pg_catalog.pg_proc "
"WHERE prolang = 13 /* C */ AND "
! " probin IS NOT NULL");
totaltups += PQntuples(ress[dbnum]);
PQfinish(conn);
--- 480,488 ----
"SELECT DISTINCT probin "
"FROM pg_catalog.pg_proc "
"WHERE prolang = 13 /* C */ AND "
! " probin IS NOT NULL AND "
! " oid >= "
! STRINGIFY(FirstNormalObjectId) ";");
totaltups += PQntuples(ress[dbnum]);
PQfinish(conn);
Index: src/pg_migrator.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.c,v
retrieving revision 1.69
diff -c -r1.69 pg_migrator.c
*** src/pg_migrator.c 10 Dec 2009 14:34:19 -0000 1.69
--- src/pg_migrator.c 13 Dec 2009 01:17:37 -0000
***************
*** 92,97 ****
--- 92,100 ----
sequence_script_file_name =
v8_3_create_sequence_script(&ctx, CLUSTER_OLD);
}
+ if (GET_MAJOR_VERSION(ctx.old.pg_version) <= 804 &&
+ GET_MAJOR_VERSION(ctx.new.pg_version) >= 805)
+ v8_4_populate_pg_largeobject_metadata(&ctx, true, CLUSTER_OLD);
/* Looks okay so far. Prepare the pg_dump output */
generate_old_dump(&ctx);
***************
*** 294,299 ****
--- 297,309 ----
v8_3_invalidate_bpchar_pattern_ops_indexes(&ctx, false, CLUSTER_NEW);
stop_postmaster(&ctx, false, true);
}
+ if (GET_MAJOR_VERSION(ctx.old.pg_version) <= 804 &&
+ GET_MAJOR_VERSION(ctx.new.pg_version) >= 805)
+ {
+ start_postmaster(&ctx, CLUSTER_NEW, true);
+ v8_4_populate_pg_largeobject_metadata(&ctx, false, CLUSTER_NEW);
+ stop_postmaster(&ctx, false, true);
+ }
pg_log(&ctx, PG_REPORT, "\n*Upgrade complete*\n");
***************
*** 416,422 ****
char new_clog_path[MAXPGPATH];
/* copy old commit logs to new data dir */
! prep_status(ctx, "Deleting old commit clogs");
snprintf(old_clog_path, sizeof(old_clog_path), "%s/pg_clog", ctx->old.pgdata);
snprintf(new_clog_path, sizeof(new_clog_path), "%s/pg_clog", ctx->new.pgdata);
--- 426,432 ----
char new_clog_path[MAXPGPATH];
/* copy old commit logs to new data dir */
! prep_status(ctx, "Deleting new commit clogs");
snprintf(old_clog_path, sizeof(old_clog_path), "%s/pg_clog", ctx->old.pgdata);
snprintf(new_clog_path, sizeof(new_clog_path), "%s/pg_clog", ctx->new.pgdata);
***************
*** 424,430 ****
pg_log(ctx, PG_FATAL, "Unable to delete directory %s\n", new_clog_path);
check_ok(ctx);
! prep_status(ctx, "Copying commit clogs");
/* libpgport's copydir() doesn't work in FRONTEND code */
#ifndef WIN32
exec_prog(ctx, true, SYSTEMQUOTE "%s \"%s\" \"%s\"" SYSTEMQUOTE,
--- 434,440 ----
pg_log(ctx, PG_FATAL, "Unable to delete directory %s\n", new_clog_path);
check_ok(ctx);
! prep_status(ctx, "Copying old commit clogs to new server");
/* libpgport's copydir() doesn't work in FRONTEND code */
#ifndef WIN32
exec_prog(ctx, true, SYSTEMQUOTE "%s \"%s\" \"%s\"" SYSTEMQUOTE,
Index: src/pg_migrator.h
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/pg_migrator.h,v
retrieving revision 1.75
diff -c -r1.75 pg_migrator.h
*** src/pg_migrator.h 12 Dec 2009 16:56:23 -0000 1.75
--- src/pg_migrator.h 13 Dec 2009 01:17:37 -0000
***************
*** 395,400 ****
--- 395,402 ----
bool check_mode, Cluster whichCluster);
void v8_3_invalidate_bpchar_pattern_ops_indexes(migratorContext *ctx,
bool check_mode, Cluster whichCluster);
+ void v8_4_populate_pg_largeobject_metadata(migratorContext *ctx,
+ bool check_mode, Cluster whichCluster);
char *v8_3_create_sequence_script(migratorContext *ctx,
Cluster whichCluster);
void check_for_composite_types(migratorContext *ctx,
Index: src/version.c
===================================================================
RCS file: /cvsroot/pg-migrator/pg_migrator/src/version.c,v
retrieving revision 1.32
diff -c -r1.32 version.c
*** src/version.c 7 Aug 2009 20:16:12 -0000 1.32
--- src/version.c 13 Dec 2009 01:17:37 -0000
***************
*** 421,427 ****
"| between your old and new clusters so the tables\n"
"| must be rebuilt. The file:\n"
"| \t%s\n"
! "| when executed by psql by the database super-user,\n"
"| will rebuild all tables with tsvector columns.\n\n",
output_path);
}
--- 421,427 ----
"| between your old and new clusters so the tables\n"
"| must be rebuilt. The file:\n"
"| \t%s\n"
! "| when executed by psql by the database super-user\n"
"| will rebuild all tables with tsvector columns.\n\n",
output_path);
}
***************
*** 535,541 ****
"| they must be reindexed with the REINDEX command.\n"
"| The file:\n"
"| \t%s\n"
! "| when executed by psql by the database super-user,\n"
"| will recreate all invalid indexes; until then,\n"
"| none of these indexes will be used.\n\n",
output_path);
--- 535,541 ----
"| they must be reindexed with the REINDEX command.\n"
"| The file:\n"
"| \t%s\n"
! "| when executed by psql by the database super-user\n"
"| will recreate all invalid indexes; until then,\n"
"| none of these indexes will be used.\n\n",
output_path);
***************
*** 664,670 ****
"| new clusters so they must be reindexed with the\n"
"| REINDEX command. The file:\n"
"| \t%s\n"
! "| when executed by psql by the database super-user,\n"
"| will recreate all invalid indexes; until then,\n"
"| none of these indexes will be used.\n\n",
output_path);
--- 664,670 ----
"| new clusters so they must be reindexed with the\n"
"| REINDEX command. The file:\n"
"| \t%s\n"
! "| when executed by psql by the database super-user\n"
"| will recreate all invalid indexes; until then,\n"
"| none of these indexes will be used.\n\n",
output_path);
***************
*** 675,680 ****
--- 675,762 ----
/*
+ * v8_4_populate_pg_largeobject_metadata()
+ *
+ * 8.5 has a new pg_largeobject permission table
+ */
+ void
+ v8_4_populate_pg_largeobject_metadata(migratorContext *ctx, bool check_mode,
+ Cluster whichCluster)
+ {
+ ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
+ &ctx->old : &ctx->new;
+ int dbnum;
+ FILE *script = NULL;
+ bool found = false;
+ char output_path[MAXPGPATH];
+
+ prep_status(ctx, "Checking for large objects");
+
+ snprintf(output_path, sizeof(output_path), "%s/pg_largeobject.sql",
+ ctx->home_dir);
+
+ for (dbnum = 0; dbnum < active_cluster->dbarr.ndbs; dbnum++)
+ {
+ PGresult *res;
+ int i_count;
+ DbInfo *active_db = &active_cluster->dbarr.dbs[dbnum];
+ PGconn *conn = connectToServer(ctx, active_db->db_name, whichCluster);
+
+ /* find if there are any large objects */
+ res = executeQueryOrDie(ctx, conn,
+ "SELECT count(*) "
+ "FROM pg_catalog.pg_largeobject ");
+
+ i_count = PQfnumber(res, "count");
+ if (atoi(PQgetvalue(res, 0, i_count)) != 0)
+ {
+ found = true;
+ if (!check_mode)
+ {
+ if (script == NULL && (script = fopen(output_path, "w")) == NULL)
+ pg_log(ctx, PG_FATAL, "Could not create necessary file: %s\n", output_path);
+ fprintf(script, "\\connect %s\n",
+ quote_identifier(ctx, active_db->db_name));
+ fprintf(script,
+ "INSERT INTO pg_catalog.pg_largeobject_metadata (lomowner)\n"
+ "SELECT DISTINCT loid\n"
+ "FROM pg_catalog.pg_largeobject;\n");
+ }
+ }
+
+ PQclear(res);
+ PQfinish(conn);
+ }
+
+ if (found)
+ {
+ if (!check_mode)
+ fclose(script);
+ report_status(ctx, PG_WARNING, "warning");
+ if (check_mode)
+ pg_log(ctx, PG_WARNING, "\n"
+ "| Your installation contains large objects.\n"
+ "| The new database has an additional large object\n"
+ "| permission table. After migration, you will be\n"
+ "| given a command to populate the pg_largeobject\n"
+ "| permission table with default permissions.\n\n");
+ else
+ pg_log(ctx, PG_WARNING, "\n"
+ "| Your installation contains large objects.\n"
+ "| The new database has an additional large object\n"
+ "| permission table so default permissions must be\n"
+ "| defined for all large objects. The file:\n"
+ "| \t%s\n"
+ "| when executed by psql by the database super-user\n"
+ "| will define the default permissions.\n\n",
+ output_path);
+ }
+ else
+ check_ok(ctx);
+ }
+
+
+ /*
* v8_3_create_sequence_script()
*
* 8.4 added the column "start_value" to all sequences. For this reason,
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
