Thread: Restore time differences between full database dumps and separate schema/data dumps

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

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

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