From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Postgres bulkload without transaction logs
Date: Tue, 05 Jul 2011 13:41:01 -0400
Message-ID: <4E134CAD.5010107@2ndQuadrant.com> (raw)
In-Reply-To: <CAC5KT4R34kN-4nibLir4WTuVPWED_Pupt3usib2+2kvTiGbVwA@mail.gmail.com>
References: <CAC5KT4R34kN-4nibLir4WTuVPWED_Pupt3usib2+2kvTiGbVwA@mail.gmail.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndQuadrant.com
Subject: Re: Postgres bulkload without transaction logs
In-Reply-To: <4E134CAD.5010107@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox