pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Postgres bulkload without transaction logs
2+ messages / 2 participants
[nested] [flat]

* Postgres bulkload without transaction logs
@ 2011-07-05 14:42  shouvik basu <babanomania@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: shouvik basu @ 2011-07-05 14:42 UTC (permalink / raw)
  To: pgsql-performance

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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Postgres bulkload without transaction logs
@ 2011-07-05 17:41  Greg Smith <greg@2ndQuadrant.com>
  parent: shouvik basu <babanomania@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Greg Smith @ 2011-07-05 17:41 UTC (permalink / raw)
  To: 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@2ndQuadrant.com   Baltimore, MD
Comprehensive and Customized PostgreSQL Training Classes:
http://www.2ndquadrant.us/postgresql-training/




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2011-07-05 17:41 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-07-05 14:42 Postgres bulkload without transaction logs shouvik basu <babanomania@gmail.com>
2011-07-05 17:41 ` Greg Smith <greg@2ndQuadrant.com>

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