Обсуждение: Migrating Data Across Major Versions
Hi all, I'm in the process of migrating our data from 8.4 to 9.0.3 on a different server, both running Ubuntu. My initial attempt involved running pg_dumpall (from the 9.0.3 version) on the entire 8.4 cluster, putting all of the schemas, data and everything into one giant file. Jumping ahead, I attempted to restore that file to the 9.0.3 installed on the new server and it's still running now, but I noticed early on that it spat out error messages about schemas not existing. Does the pg_dumpall know to create the schemas first, before it creates all the stored functions and tables? Being that this is the recommended method from the docs, I'm confused why it would yield such errors. This raised some concern, so I went back to the old server in an attempt to run pg_dumpall to make separate files for schema-only and data-only. At this point, I have to backtrack a bit and explain that after the initial data dump, I discovered that I didn't have enough room on the original machine to restore all of the 8.4 data to the 9.0.3 cluster while the old data still remained in place. So I moved the entire 8.4 pgdata folder to an external harddrive. Unfortunately, the mv operation was interrupted at some point and when I discovered this, I was unable to re-run the same mv command to get the rest of the files into the new location because it was giving me weird errors. The solution seemed to be to use the cp command instead, so I did that and didn't get any negative feedback. After all that occurred, I basically changed my mind about migrating the data to 9.0.3 on the old machine, which is when I decided to put the old 8.4 data back and re-run pg_dumpall in an attempt to get separate files for schema and data. I ran the mv command again to get the 8.4 pgdata folder back in place and initially I had some issues getting the server to start. I got sidetracked with other things at that point, but when I came back to it a few days later, the server issue had apparently fixed itself and 8.4 was running again. I quickly checked PgAdmin and nothing looked obviously out of place. So I went ahead and ran the pg_dumpall (again, from the 9.0.3 version) on the same 8.4 cluster and now ended up with two files. The reason I'm concerned now is because my original file containing everything was 208GB, but now my schema-only file is 2.9MB and my data-only file is 167GB. Is this evidence that my data may have been corrupted due to the incomplete mv operation from before, or could there be some other explanation as to why the sizes are so different? Thanks! sg
On Friday, April 15, 2011 5:25:51 am Shawn Gennaria wrote: > Hi all, > > I'm in the process of migrating our data from 8.4 to 9.0.3 on a > different server, both running Ubuntu. My initial attempt involved > running pg_dumpall (from the 9.0.3 version) on the entire 8.4 cluster, > putting all of the schemas, data and everything into one giant file. > Jumping ahead, I attempted to restore that file to the 9.0.3 installed > on the new server and it's still running now, but I noticed early on > that it spat out error messages about schemas not existing. Does the > pg_dumpall know to create the schemas first, before it creates all the > stored functions and tables? Being that this is the recommended > method from the docs, I'm confused why it would yield such errors. What where the error mesages? Did you do the restore as a Postgres superuser? Do the schemas in fact exist in the 9.0.3 database? > > up with two files. The reason I'm concerned now is because my > original file containing everything was 208GB, but now my schema-only > file is 2.9MB and my data-only file is 167GB. Is this evidence that > my data may have been corrupted due to the incomplete mv operation > from before, or could there be some other explanation as to why the > sizes are so different? Have you done any normal work on the 8.4 database that could have caused the amount of data to decrease i.e DELETE from ? I guess some of this might be further explained by the clarification of the issues you had restarting the 8.4 server. > > Thanks! > sg -- Adrian Klaver adrian.klaver@gmail.com
On Fri, Apr 15, 2011 at 9:32 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Friday, April 15, 2011 5:25:51 am Shawn Gennaria wrote: >> Hi all, >> >> I'm in the process of migrating our data from 8.4 to 9.0.3 on a >> different server, both running Ubuntu. My initial attempt involved >> running pg_dumpall (from the 9.0.3 version) on the entire 8.4 cluster, >> putting all of the schemas, data and everything into one giant file. >> Jumping ahead, I attempted to restore that file to the 9.0.3 installed >> on the new server and it's still running now, but I noticed early on >> that it spat out error messages about schemas not existing. Does the >> pg_dumpall know to create the schemas first, before it creates all the >> stored functions and tables? Being that this is the recommended >> method from the docs, I'm confused why it would yield such errors. > > What where the error mesages? > Did you do the restore as a Postgres superuser? > Do the schemas in fact exist in the 9.0.3 database? Yes, it's running as a postgres superuser. Unfortunately I didn't dump the output to a file so I could read it after it exceeds the terminal's buffer. It just dawned on me that I may have misinterpreted the messages. I just took a look through the early parts of the file I'm using to restore everything and noticed that many of my stored functions contain references to other stored functions. Naturally, one wouldn't be able to create a function unless all dependent referenced functions were created first. It would be rather time-consuming to sort that out manually and re-add each function in the correct order. Does anyone have experience working that out with some kind of tool? Does postgres have anything built-in to help with this? > >> >> up with two files. The reason I'm concerned now is because my >> original file containing everything was 208GB, but now my schema-only >> file is 2.9MB and my data-only file is 167GB. Is this evidence that >> my data may have been corrupted due to the incomplete mv operation >> from before, or could there be some other explanation as to why the >> sizes are so different? > > Have you done any normal work on the 8.4 database that could have caused the > amount of data to decrease i.e DELETE from ? > I guess some of this might be further explained by the clarification of the > issues you had restarting the 8.4 server. No modifications were made to the 8.4 cluster after I put the pgdata folder back in place. All I did was quickly login with PgAdmin to verify that it wasn't completely empty and then I logged out. I wasn't thorough about it and didn't even run a query. I do have logs in my pgdata/pg_log folder from each day. Would those help track down issues with getting the server to start, or are they only used once the server's already up? I took a look through the recent ones, and the only thing that looks odd from that time period are repeating messages: FATAL: unrecognized configuration parameter "application_name" In either case, the issue seems to have worked itself out after the server was rebooted. > >> >> Thanks! >> sg > > -- > Adrian Klaver > adrian.klaver@gmail.com >
On Friday, April 15, 2011 7:10:00 am Shawn Gennaria wrote: > > Yes, it's running as a postgres superuser. Unfortunately I didn't > dump the output to a file so I could read it after it exceeds the > terminal's buffer. > > It just dawned on me that I may have misinterpreted the messages. I > just took a look through the early parts of the file I'm using to > restore everything and noticed that many of my stored functions > contain references to other stored functions. Naturally, one wouldn't > be able to create a function unless all dependent referenced functions > were created first. It would be rather time-consuming to sort that > out manually and re-add each function in the correct order. Does > anyone have experience working that out with some kind of tool? Does > postgres have anything built-in to help with this? So do the schemas exist in the 9.0.3 database? As you stated there are dependency issues but I have not seen that be a problem. The error messages you saw are not unusual and are probably more of the informative kind, not the fatal kind. I don't suppose you had logging turned on in the 9.0.3 server when you did the restore? > No modifications were made to the 8.4 cluster after I put the pgdata > folder back in place. All I did was quickly login with PgAdmin to > verify that it wasn't completely empty and then I logged out. I > wasn't thorough about it and didn't even run a query. I do have logs > in my pgdata/pg_log folder from each day. Would those help track down > issues with getting the server to start, or are they only used once > the server's already up? I took a look through the recent ones, and > the only thing that looks odd from that time period are repeating > messages: There some parts of the server start that may not make it into the logs but most does, so yes they would be useful. > > FATAL: unrecognized configuration parameter "application_name" Have you looked in postgresql.conf or pg_hba.conf to see if the above is there? > > In either case, the issue seems to have worked itself out after the > server was rebooted. > > >> Thanks! > >> sg > > > > -- > > Adrian Klaver > > adrian.klaver@gmail.com -- Adrian Klaver adrian.klaver@gmail.com
So I was forced to re-do the migration since we had to prepare for a power outage anyway. Upon re-running the command, I found out that the reason I was receiving the notices in STDERR was due to the the long string of ordered schemas in my search path. It had a "SET search_path TO public, x, y, z, ..." at the beginning of the file and of course schemas x, y, z, etc hadn't been created yet. So there's no real cause for alarm after all. On Fri, Apr 15, 2011 at 10:23 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote: > On Friday, April 15, 2011 7:10:00 am Shawn Gennaria wrote: > >> >> Yes, it's running as a postgres superuser. Unfortunately I didn't >> dump the output to a file so I could read it after it exceeds the >> terminal's buffer. >> >> It just dawned on me that I may have misinterpreted the messages. I >> just took a look through the early parts of the file I'm using to >> restore everything and noticed that many of my stored functions >> contain references to other stored functions. Naturally, one wouldn't >> be able to create a function unless all dependent referenced functions >> were created first. It would be rather time-consuming to sort that >> out manually and re-add each function in the correct order. Does >> anyone have experience working that out with some kind of tool? Does >> postgres have anything built-in to help with this? > > > So do the schemas exist in the 9.0.3 database? > As you stated there are dependency issues but I have not seen that be a problem. > The error messages you saw are not unusual and are probably more of the > informative kind, not the fatal kind. I don't suppose you had logging turned on > in the 9.0.3 server when you did the restore? > >> No modifications were made to the 8.4 cluster after I put the pgdata >> folder back in place. All I did was quickly login with PgAdmin to >> verify that it wasn't completely empty and then I logged out. I >> wasn't thorough about it and didn't even run a query. I do have logs >> in my pgdata/pg_log folder from each day. Would those help track down >> issues with getting the server to start, or are they only used once >> the server's already up? I took a look through the recent ones, and >> the only thing that looks odd from that time period are repeating >> messages: > > There some parts of the server start that may not make it into the logs but most > does, so yes they would be useful. > >> >> FATAL: unrecognized configuration parameter "application_name" > > Have you looked in postgresql.conf or pg_hba.conf to see if the above is there? > >> >> In either case, the issue seems to have worked itself out after the >> server was rebooted. >> >> >> Thanks! >> >> sg >> > >> > -- >> > Adrian Klaver >> > adrian.klaver@gmail.com > > -- > Adrian Klaver > adrian.klaver@gmail.com >