Re: Postgres bulkload without transaction logs

From: Greg Smith <greg(at)2ndQuadrant(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Postgres bulkload without transaction logs
Date: 2011-07-05 17:41:01
Message-ID: 4E134CAD.5010107@2ndQuadrant.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)2ndQuadrant(dot)com Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Greg Smith 2011-07-05 17:43:26 Re: bitmask index
Previous Message shouvik basu 2011-07-05 14:42:34 Postgres bulkload without transaction logs