Thread: pg_migrator progress
I have completed all the outstanding pg_migratory TODO items.
I still have more work to do in cleanup and testing, but if people want
to look at my progress, now is a good time.
You can download the current CVS here:
http://pgfoundry.org/scm/?group_id=1000235
and you can subscribe to the "general" email list here:
http://pgfoundry.org/mail/?group_id=1000235
I am attaching main() so you can get an idea of how pg_migrator works.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
int
main(int argc, char **argv)
{
migratorContext ctx = {0};
int ntablespaces = 0;
int i = 0;
char **tablespaces = NULL; /* array to store table space paths of
* old data */
struct timezone tz;
parseCommandLine(&ctx, argc, argv);
setup(&ctx, argv[0]);
/* -- OLD -- */
start_postmaster(&ctx, "Starting postmaster to service old cluster", CLUSTER_OLD);
/* Add any required support functions to the old cluster */
create_support_functions(&ctx, "Adding support functions to old cluster",
ctx.m_oldlibpath, ctx.m_oldbindir, CLUSTER_OLD);
/* Get the pg_database and pg_largeobject relation OID's */
get_db_LO_relfilenodes(&ctx, CLUSTER_OLD);
/* Extract a list of databases and tables from the old cluster */
gen_db_info(&ctx, &ctx.m_olddbarr, CLUSTER_OLD);
dump_old_schema(&ctx, DUMP_FILE);
tablespaces = get_tablespace_paths(&ctx, &ntablespaces, CLUSTER_OLD);
stop_postmaster(&ctx, CLUSTER_OLD);
/* Rename all tablespace paths */
rename_tablespaces(&ctx, tablespaces, ntablespaces);
/* -- NEW -- */
start_postmaster(&ctx, "Starting postmaster to service new cluster", CLUSTER_NEW);
/* XXX check that new database is empty */
/*
* It would make more sense to freeze after loading the schema, but
* that would cause us to lose the frozenids restored by the load.
*/
prepStatus(&ctx, "Freezing all rows on the new server");
exec_prog(&ctx, true, "%s/vacuumdb --port %d --all --frozen --full >> %s 2>&1",
ctx.m_newbindir, ctx.m_newport, ctx.m_logfile);
check_ok(&ctx);
stop_postmaster(&ctx, CLUSTER_NEW);
ask_continue(&ctx);
copy_clog_xlog_xid(&ctx);
/* New now using xid of old system */
/* -- NEW -- */
start_postmaster(&ctx, "Starting postmaster to service new cluster", CLUSTER_NEW);
create_support_functions(&ctx, "Adding support functions to new cluster",
ctx.m_newlibpath, ctx.m_newbindir, CLUSTER_NEW);
get_db_LO_relfilenodes(&ctx, CLUSTER_NEW);
/*
* Although the schema load will create all the databases, we need to perform
* this step first in order to create toast table placeholder relfiles.
*/
create_databases(&ctx, &ctx.m_olddbarr, ctx.m_newbindir);
prepStatus(&ctx, "Creating placeholder relfiles for toast relations");
create_placeholder_relfiles(&ctx, &ctx.m_olddbarr, ctx.m_newpgdata);
check_ok(&ctx);
prepStatus(&ctx, "Restoring database schema");
exec_prog(&ctx, false, "%s/%s --port %d --dbname template1 < %s/%s >> %s 2>&1",
ctx.m_newbindir, ctx.m_newpsql_command, ctx.m_newport,
ctx.m_homedir, DUMP_FILE, ctx.m_logfile);
check_ok(&ctx);
process_relfiles(&ctx);
stop_postmaster(&ctx, CLUSTER_NEW);
#ifdef NOT_USED
/* XXX do we need this at all? */
/*
* Assuming OIDs are only used in system tables, there is no need to
* restore the OID counter because we have not transfered any OIDs
* from the old system.
*/
prepStatus(&ctx, "Setting next oid for new cluster");
exec_prog(&ctx, true, "%s/pg_resetxlog -o %u %s 1>/dev/null",
ctx.m_newbindir, ctx.m_oldctrldata.chkpnt_nxtoid, ctx.m_newpgdata);
check_ok(&ctx);
#endif
cleanup(&ctx);
if (gettimeofday(&ctx.m_endtime, &tz) == -1)
pg_log(&ctx, PG_FATAL, "Unable to get time");
pg_log(&ctx, PG_REPORT, "\nThe data migration completed in %d seconds\n",
ctx.m_endtime.tv_sec - ctx.m_starttime.tv_sec);
return 0;
}
You've moved fast on this! > #ifdef NOT_USED > /* XXX do we need this at all? */ > /* > * Assuming OIDs are only used in system tables, there is no need to > * restore the OID counter because we have not transfered any OIDs > * from the old system. > */ > prepStatus(&ctx, "Setting next oid for new cluster"); > exec_prog(&ctx, true, "%s/pg_resetxlog -o %u %s 1>/dev/null", > ctx.m_newbindir, ctx.m_oldctrldata.chkpnt_nxtoid, ctx.m_newpgdata); > check_ok(&ctx); > #endif It's certainly not impossible for someone to be using OIDs on user tables, is it? I mean, I'm not, but... ...Robert
Robert Haas <robertmhaas@gmail.com> writes:
>> /* XXX do we need this at all? */
>> /*
>> * Assuming OIDs are only used in system tables, there is no need to
>> * restore the OID counter because we have not transfered any OIDs
>> * from the old system.
>> */
> It's certainly not impossible for someone to be using OIDs on user
> tables, is it?
No, but this would just be the same situation that prevails after
OID-counter wraparound, so I don't see a compelling need for us to
change the OID counter in the new DB. If the user has done the Proper
Things (ie, made unique indexes on his OIDs) then it won't matter.
If he didn't, his old DB was a time bomb anyway.
regards, tom lane
Bruce Momjian wrote: > I have completed all the outstanding pg_migratory TODO items. > > I still have more work to do in cleanup and testing, but if people want > to look at my progress, now is a good time. Hmm, don't you need to change the Xid counter (pg_resetxlog) if you're going to mess with pg_clog? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Tom Lane <tgl@sss.pgh.pa.us> writes: > No, but this would just be the same situation that prevails after > OID-counter wraparound, so I don't see a compelling need for us to > change the OID counter in the new DB. If the user has done the Proper > Things (ie, made unique indexes on his OIDs) then it won't matter. > If he didn't, his old DB was a time bomb anyway. Well it was a time bomb but it wasn't necessarily about to go off... He may very well know how close or far he is from oid wraparound and have contingency plans in place. Also I wonder about the performance of skipping over thousands or even millions of OIDs for something like a toast table. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Gregory Stark <stark@enterprisedb.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> No, but this would just be the same situation that prevails after
>> OID-counter wraparound, so I don't see a compelling need for us to
>> change the OID counter in the new DB. If the user has done the Proper
>> Things (ie, made unique indexes on his OIDs) then it won't matter.
>> If he didn't, his old DB was a time bomb anyway.
> Also I wonder about the performance of skipping over thousands or even
> millions of OIDs for something like a toast table.
I think that argument is a red herring. In the first place, it's
unlikely that there'd be a huge run of consecutive OIDs *in the same
table*. In the second place, if he does have such runs, the claim that
he can't possibly have dealt with OID wraparound before seems pretty
untenable --- he's obviously been eating lots of OIDs.
But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.com> writes: >> Also I wonder about the performance of skipping over thousands or even >> millions of OIDs for something like a toast table. > > I think that argument is a red herring. In the first place, it's > unlikely that there'd be a huge run of consecutive OIDs *in the same > table*. Really? Wouldn't all it take be a single large COPY loading data into a table with one or more columns receiving large data which need to be toasted? > In the second place, if he does have such runs, the claim that he can't > possibly have dealt with OID wraparound before seems pretty untenable --- > he's obviously been eating lots of OIDs. Well there's a pretty wide margin between millions and 4 billion. I suppose you could say it would only be a one-time cost (or a few separate one-time costs until the oid counter passed the old value). So a few minutes after doing an in-place upgrade while the oid counter skimmed past all the existing values would be bearable. > But having said that, there isn't any real harm in fixing the OID > counter to match what it was. You need to run pg_resetxlog to set the > WAL position and XID counter anyway, and it can set the OID counter too. Yeah, if it was massive amounts of code I could see arguing that it's not justified, but given that it's about the same degree of complexity either way it seems clear to me that it's better to do it than not to do it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
On Wednesday 18 February 2009 10:47:25 Tom Lane wrote: > Gregory Stark <stark@enterprisedb.com> writes: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > >> No, but this would just be the same situation that prevails after > >> OID-counter wraparound, so I don't see a compelling need for us to > >> change the OID counter in the new DB. If the user has done the Proper > >> Things (ie, made unique indexes on his OIDs) then it won't matter. > >> If he didn't, his old DB was a time bomb anyway. > > > > Also I wonder about the performance of skipping over thousands or even > > millions of OIDs for something like a toast table. > > I think that argument is a red herring. In the first place, it's > unlikely that there'd be a huge run of consecutive OIDs *in the same > table*. In the second place, if he does have such runs, the claim that > he can't possibly have dealt with OID wraparound before seems pretty > untenable --- he's obviously been eating lots of OIDs. > Yeah, but its not just lots... it's lots and lots of lots. Sure, I have multi-billion row _tables_ now, but I know I ran systems for years "back in the day" when we used oids in user tables, and they never made it to oid wraparound terratory, because they just didn't churn through that much data. > But having said that, there isn't any real harm in fixing the OID > counter to match what it was. You need to run pg_resetxlog to set the > WAL position and XID counter anyway, and it can set the OID counter too. > +1 for doing this, otherwise we need some strong warnings in the migrator docs about this case imho. -- Robert Treat Conjecture: http://www.xzilla.net Consulting: http://www.omniti.com
Alvaro Herrera wrote: > Bruce Momjian wrote: > > I have completed all the outstanding pg_migratory TODO items. > > > > I still have more work to do in cleanup and testing, but if people want > > to look at my progress, now is a good time. > > Hmm, don't you need to change the Xid counter (pg_resetxlog) if you're > going to mess with pg_clog? Yes, that happens in copy_clog_xlog_xid(). -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Robert Treat wrote: > On Wednesday 18 February 2009 10:47:25 Tom Lane wrote: > > Gregory Stark <stark@enterprisedb.com> writes: > > > Tom Lane <tgl@sss.pgh.pa.us> writes: > > >> No, but this would just be the same situation that prevails after > > >> OID-counter wraparound, so I don't see a compelling need for us to > > >> change the OID counter in the new DB. If the user has done the Proper > > >> Things (ie, made unique indexes on his OIDs) then it won't matter. > > >> If he didn't, his old DB was a time bomb anyway. > > > > > > Also I wonder about the performance of skipping over thousands or even > > > millions of OIDs for something like a toast table. > > > > I think that argument is a red herring. In the first place, it's > > unlikely that there'd be a huge run of consecutive OIDs *in the same > > table*. In the second place, if he does have such runs, the claim that > > he can't possibly have dealt with OID wraparound before seems pretty > > untenable --- he's obviously been eating lots of OIDs. > > > > Yeah, but its not just lots... it's lots and lots of lots. Sure, I have > multi-billion row _tables_ now, but I know I ran systems for years "back in > the day" when we used oids in user tables, and they never made it to oid > wraparound terratory, because they just didn't churn through that much data. > > > But having said that, there isn't any real harm in fixing the OID > > counter to match what it was. You need to run pg_resetxlog to set the > > WAL position and XID counter anyway, and it can set the OID counter too. > > > > +1 for doing this, otherwise we need some strong warnings in the migrator docs > about this case imho. One compromise is outputting the pg_resetxlog command to the terminal, and suggesting they run it only if they need to. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> One compromise is outputting the pg_resetxlog command to the terminal,
> and suggesting they run it only if they need to.
Er, what? pg_resetxlog is certainly not optional in this process.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > One compromise is outputting the pg_resetxlog command to the terminal, > > and suggesting they run it only if they need to. > > Er, what? pg_resetxlog is certainly not optional in this process. The oid setting part is. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> Er, what? pg_resetxlog is certainly not optional in this process.
> The oid setting part is.
Yeah, but if you have to run it anyway it certainly isn't going to be
any more work to make it set the OID counter too.
regards, tom lane
Gregory Stark wrote: > > But having said that, there isn't any real harm in fixing the OID > > counter to match what it was. You need to run pg_resetxlog to set the > > WAL position and XID counter anyway, and it can set the OID counter too. > > Yeah, if it was massive amounts of code I could see arguing that it's not > justified, but given that it's about the same degree of complexity either way > it seems clear to me that it's better to do it than not to do it. Agreed, done. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Tom Lane wrote: > Gregory Stark <stark@enterprisedb.com> writes: > > Tom Lane <tgl@sss.pgh.pa.us> writes: > >> No, but this would just be the same situation that prevails after > >> OID-counter wraparound, so I don't see a compelling need for us to > >> change the OID counter in the new DB. If the user has done the Proper > >> Things (ie, made unique indexes on his OIDs) then it won't matter. > >> If he didn't, his old DB was a time bomb anyway. > > > Also I wonder about the performance of skipping over thousands or even > > millions of OIDs for something like a toast table. > > I think that argument is a red herring. In the first place, it's > unlikely that there'd be a huge run of consecutive OIDs *in the same > table*. In the second place, if he does have such runs, the claim that > he can't possibly have dealt with OID wraparound before seems pretty > untenable --- he's obviously been eating lots of OIDs. > > But having said that, there isn't any real harm in fixing the OID > counter to match what it was. You need to run pg_resetxlog to set the > WAL position and XID counter anyway, and it can set the OID counter too. FYI, I decided against restoring the oid counter because it might collide with an oid assigned during pg_migrator schema creation. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> But having said that, there isn't any real harm in fixing the OID
>> counter to match what it was. You need to run pg_resetxlog to set the
>> WAL position and XID counter anyway, and it can set the OID counter too.
> FYI, I decided against restoring the oid counter because it might
> collide with an oid assigned during pg_migrator schema creation.
That argument seems pretty empty --- why is it more likely than a
collision happening if you *don't* restore the oid counter? And why
would it matter anyway? We fixed the problems with oid collisions
some time ago.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Tom Lane wrote: > >> But having said that, there isn't any real harm in fixing the OID > >> counter to match what it was. You need to run pg_resetxlog to set the > >> WAL position and XID counter anyway, and it can set the OID counter too. > > > FYI, I decided against restoring the oid counter because it might > > collide with an oid assigned during pg_migrator schema creation. > > That argument seems pretty empty --- why is it more likely than a > collision happening if you *don't* restore the oid counter? And why We don't transfer any system-level oids so there is no chance of a collision against system tables. > would it matter anyway? We fixed the problems with oid collisions > some time ago. Oh, we handled that; OK, old code restored. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +