Thread: Restore time differences between full database dumps and separate schema/data dumps
Restore time differences between full database dumps and separate schema/data dumps
From
Glen Barber
Date:
Hello, I recently attempted an upgrade on one of our servers from 8.2.6 to 8.4.1. I created two separate dumps for the database, separating the schema and data. I did separate dumps because I need to expand the size of a column in one of the tables. After the schema restore, everything was going seemingly well until I started the data restore. The data seemed to be restoring at a reasonable rate, considering the database is in excess of 17GB. After approximately 30 minutes, I checked the size of the data directory to try to estimate the total restore time - it was less than 1GB. An hour later, the restore was stopped, and disabled autovacuum in postgresql.conf; this made no change. Two hours later or so, the restore peaked at 2.2GB, and the upgrade was aborted. I copied the schema/data dumps to a testing machine so I could see how long the restore would have actually taken - in 24 hours, the data directory was just shy of 4GB in size. Searching the documentation and archives, I don't see any clear explanation. When schema/data are separated, is the restore treated as INSERTs? Is there something else obvious I am missing, or that I can check? Regards, -- Glen Barber
Re: Restore time differences between full database dumps and separate schema/data dumps
From
Tom Lane
Date:
Glen Barber <glen.j.barber@gmail.com> writes: > When schema/data are separated, is the restore treated as INSERTs? No, but you'll still get killed on performance by other factors, particularly incremental index building and retail foreign key checks. I recommend a close read of http://www.postgresql.org/docs/8.4/static/populate.html What you'll want to do is separate the load into three phases corresponding to the order that a combined schema+data dump does it. regards, tom lane
Re: Restore time differences between full database dumps and separate schema/data dumps
From
Glen Barber
Date:
Hi Tom, On Thu, Dec 10, 2009 at 6:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Glen Barber <glen.j.barber@gmail.com> writes: >> When schema/data are separated, is the restore treated as INSERTs? > > No, but you'll still get killed on performance by other factors, > particularly incremental index building and retail foreign key checks. > I recommend a close read of > http://www.postgresql.org/docs/8.4/static/populate.html > > What you'll want to do is separate the load into three phases > corresponding to the order that a combined schema+data dump > does it. > Thanks for the pointer. Regards, -- Glen Barber