Re: pg_Restore

From: bhanu udaya <udayabhanu1984(at)hotmail(dot)com>
To: Chris Travers <chris(dot)travers(at)gmail(dot)com>, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>
Cc: Magnus Hagander <magnus(at)hagander(dot)net>, François Beausoleil <francois(at)teksol(dot)info>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: pg_Restore
Date: 2013-01-21 15:10:05
Message-ID: COL002-W40742820203F6CA7F83992D3170@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support pgsql-general

Hello,Thanks alot for all your replies. I tried all settings suggested, it did not work. pg_restore is very slow. It does not come out less than 1 1/2 hour. Can you please let me know the procedure for Template. Will it restore the data also . Please update. I need the database (metadata + data) to be restored back after certain Java executions. Ex:- I have 9.5 gb database (with 500 schemas + data). This is treated as base database and it needs to be restored every time after certain transactions. Thanks for your reply. Thanks and REgardsRadha Krishna
Date: Mon, 21 Jan 2013 04:16:19 -0800
Subject: Re: [GENERAL] pg_Restore
From: chris(dot)travers(at)gmail(dot)com
To: laurenz(dot)albe(at)wien(dot)gv(dot)at
CC: udayabhanu1984(at)hotmail(dot)com; magnus(at)hagander(dot)net; francois(at)teksol(dot)info; pgsql-general(at)postgresql(dot)org

On Mon, Jan 21, 2013 at 3:39 AM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

bhanu udaya wrote:

> I tried with all the below options. It approximatly takes 1 hour 30 minutes for restoring a 9GB

> database. This much time can not be affordable as the execution of test cases take only 10% of this

> whole time and waiting 1 hour 30 minutes after every test case execution is alot for the team. Kindly

> let me know if we can reduce the database restoration time .

I don't know if that helps, but have you tried creating a template database

and doing DROP DATABASE xxx; CREATE DATABASE xxx TEMPLATE mytemplate;

instead of restoring a dump every time?

Also for test cases, my preferred way is to put every test case in a transaction that cannot commit. This way the tests are safe to run on production environments. See pgTAP for one possibility here if you are testing stored procedures. (Application code we run through wrappers that filter out commits.)

But also the template approach is a good one fi you cannot guarantee that the tests always role back.
Best Wishes,Chris Travers

In response to

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Albe Laurenz 2013-01-21 15:19:45 Re: pg_Restore
Previous Message Chris Ernst 2013-01-21 14:19:53 Re: pg_Restore

Browse pgsql-general by date

  From Date Subject
Next Message Albe Laurenz 2013-01-21 15:19:45 Re: pg_Restore
Previous Message Alexander Farber 2013-01-21 14:45:27 Re: Sending several commands simultaneously to PostgreSQL 8.4