Re: pg_Restore

[bhanu udaya <udayabhanu1984@xxxxxxxxxxx>]

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 REgards
Radha Krishna

 

---

Date: Mon, 21 Jan 2013 04:16:19 -0800
Subject: Re: pg_Restore
From: chris.travers@xxxxxxxxx
To: laurenz.albe@xxxxxxxxxx
CC: udayabhanu1984@xxxxxxxxxxx; magnus@xxxxxxxxxxxx; francois@xxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx

On Mon, Jan 21, 2013 at 3:39 AM, Albe Laurenz <laurenz.albe@xxxxxxxxxx> 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