Message-ID: <4E134CAD.5010107@2ndQuadrant.com>
Date: Tue, 05 Jul 2011 13:41:01 -0400
From: Greg Smith <greg@2ndQuadrant.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.16) Gecko/20110505 Icedove/3.0.11
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Postgres bulkload without transaction logs
References: 
 <CAC5KT4R34kN-4nibLir4WTuVPWED_Pupt3usib2+2kvTiGbVwA@mail.gmail.com>
In-Reply-To: 
 <CAC5KT4R34kN-4nibLir4WTuVPWED_Pupt3usib2+2kvTiGbVwA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 07/05/2011 10:42 AM, shouvik basu wrote:
>
> I am trying to perform a bulkload of 1 crore rows using copy of row 
> width 1000, but the transaction logs keeps getting huge space, is 
> there a easy way to do it and avoid the transaction logs
>

If you're using the built-in streaming replication or an archive_command 
to replicate this data, you can't avoid the transaction logs; those are 
how the data gets shipped to the other server.

If you CREATE or TRUNCATE the table you're loading data into as part of 
a transaction that loads into that, in a single server setup this will 
avoid creating the transaction log data.  For example:

BEGIN;
TRUNCATE TABLE t;
COPY t FROM ...
COMMIT;

That COPY will execute with minimal pg_xlog activity, because if the 
server crashes in the middle it will just roll back the table truncation.

Another piece of software you may find useful for this case is 
http://pgbulkload.projects.postgresql.org/

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/