WAL file size vs. data file size

From: Ben Chobot <bench(at)silentmedia(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: WAL file size vs. data file size
Date: 2011-10-27 03:55:01
Message-ID: F0D5523B-F32A-47A1-BB86-9A01206B1387@silentmedia.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Today I tried to restore a 70GB database with the standard "pg_dump -h old_server <…> | psql -h new_server <…>" method. I had 100GB set aside for WAL files, which I figured surely would be enough, because all of the data, including indices, is only 70GB. So I was a bit surprised when the restore hung mis-way because my pg_xlogs directory ran out of space.

Is it expected that WAL files are less dense than data files? I understand that they'll include multiple versions of the data, while the data files only include the last one (if recently vacuumed), but it's not like a restore does much besides COPY commands. I also understand that when you specify an archive_timeout value, you might be bloating the WAL files, but again, in the case of a restore, it seems like each WAL file will be full of useful data. So how is it that I can have more WAL bytes than data bytes?

Now, the new server is dedicated to running just this database, so it's not like there was anything else that could be adding info the the WAL files. But it is a master for a hot standby cluster…. maybe that makes a difference?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrea Peri 2011-10-27 06:13:25 PG 9.1.1 - availability of xslt_process()
Previous Message Scott Marlowe 2011-10-27 00:46:22 Re: FATAL: the database system is starting up