Thread: Re: pg_Restore

Le 2013-01-20 à 23:10, bhanu udaya a écrit :

> I am new to postgres and recently got migrated from oracle. I am using postgresql 9.2 version.

Welcome, and good choice for the version.

> I am trying to restore 9.5G database (1GB dumpfile) which has 500 schemas with 1 lakh rows in each schema. Could take
thedata dump using pg_dump and it takes around 40 minutes. I tried to use pg_restore to restore this dump, but it takes
hoursto restore the dump. I have used the configurations parameters as below: 
>
> shared_buffers = 1024MB
> work_mem = 512MB
> maintenance_work_mem = 512MB
> wal_buffers = 100MB
>
> I have used command to restore as below:
> pg_Restore -d newdb -j2 e:\file.dmp
>
> My machine configurations are as below:
> Windows Core I5 with 4GB Ram.

Other settings you can change during the initial restore / load phase:

fsync = off # ONLY DURING INITIAL DATA LOAD!

checkpoint_segments = 128 # large value, such as 128 or 256 (16MB per file, check disk space)
checkpoint_timeout = 30min
checkpoint_completion_target = 0.9

wal_level = minimal # You'll need to do a full base backup if you use this

Read this section of the manual: http://www.postgresql.org/docs/current/static/runtime-config-wal.html

Have a great day!
François Beausoleil

On Jan 21, 2013 7:17 AM, "bhanu udaya" <udayabhanu1984@hotmail.com> wrote:
>
>
>  Hello,
> Greetings !
> Thank you for the prompt reply. I have changed the settings as listed below:
> > > shared_buffers = 1024MB
> > > work_mem = 512MB
> > > maintenance_work_mem = 512MB
> > > wal_buffers = 100MB
> > fsync = off # ONLY DURING INITIAL DATA LOAD!
> > checkpoint_segments = 128 # large value, such as 128 or 256 (16MB per file, check disk space)
> > checkpoint_timeout = 30min
> > checkpoint_completion_target = 0.9
> > wal_level = minimal # You'll need to do a full base backup if you use this
> But, have same problem. It is almost 1 hour now, the restoration is still going on.  After every test case execution, we would like to refresh the database and expected refresh should be completed in less than 10 minutes. Is this achievable with the kind of configuration I have listed in my earlier email.
>  
> Kindly help , as how to speed up this restoration process.
>

Try running pg_restore with the -1 option. If that doesn't help, try -m4 or something like that (you'll have to remove the first option then, can't use both at once)

But it's going to be pushing it anyway. Your scenario is going to create thousands of files (assuming you have multiple tables in each of your schemas as is normal), and that's just not something ntfs does very fast. Once the files are there, I bet loading the data is reasonably fast since it can't be all that big....

/Magnus

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?

Maybe that is faster.

Yours,
Laurenz Albe

On 01/20/2013 11:17 PM, bhanu udaya wrote:
> I am trying to restore 9.5G database (1GB dumpfile) which has 500
> schemas with 1 lakh rows in each schema. Could take the data dump using
> pg_dump and it takes around 40 minutes. I tried to use pg_restore to
> restore this dump, but it takes hours to restore the dump. I have used
> the configurations parameters as
....
> But, have same problem. It is almost 1 hour now, the restoration is
> still going on.  After every test case execution, we would like to
> refresh the database and expected refresh should be completed in less
> than 10 minutes. Is this achievable with the kind of configuration I
> have listed in my earlier email.

Probably not what you want to hear, but I think This is a completely
unrealistic expectation.  If it takes 40 minutes for pg_dump, I would
expect pg_restore to take at least as long and likely significantly
longer (assuming both are done on similar hardware).

pg_dump only has to read the schema(s) and data and write them to a
file.  pg_restore has to read write the schema and  data into a new
database *AND* re-create all of the indexes, analyze, check referential
integrity......

So if the dump is taking 40 minutes, I would expect the restore to take
somewhere in the 60-90 minute range, depending on the number of and
complexity of the indexing.

    - Chris

bhanu udaya wrote:
> Can you please let me know the procedure for Template. Will it restore the data also
> .

It will create a complete copy of an existing database

The procedure is
CREATE DATABASE newdb TEMPLATE olddb;

Nobody may be connected to olddb for this to work.

Yours,
Laurenz Albe

On Monday, January 21, 2013 08:40:05 PM bhanu udaya wrote:
> 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.

Don't use pg_restore, do the backups/restorations outside of PostgreSQL:

- run on a server with a snapshot-capable volume manager, use that for quick
restores
- just try using rsync from a backup copy of the base data directory

(either of the above require PostgreSQL to not be running during the
restorations)

On 01/21/2013 08:46 AM, bhanu udaya wrote:
> Hello All,
>
> Can we achieve this template or pg_Restore in less than 20 minutes time.
> Any more considerations. Kindly reply.

Seems to me this is where Point in Time Recovery(PITR) might be helpful.

http://www.postgresql.org/docs/9.2/static/continuous-archiving.html

> Thanks and Regards



--
Adrian Klaver
adrian.klaver@gmail.com

On Mon, 21 Jan 2013 22:16:00 +0530
bhanu udaya <udayabhanu1984@hotmail.com> wrote:

> Hello All,
>  Can we achieve this template or pg_Restore in less than 20 minutes time. Any more considerations. Kindly reply.
Thanksand RegardsRadha Krishna From: udayabhanu1984@hotmail.com 

Perhaps you can use the ntfs snapshot feature.

a) Create the db with the data you want
b) Shutdown PostgreSql completly
c) Go to data folder, properties, make a recover point
d) Start PostgreSql
f) Work with it
g) Stop PostgreSql
h) Go to data folder, properties, recover the previous version
i) Go to d)

Don't know exactly the name of the ntfs property, if i remember well it's recover point, but haven't used Windows for a
while.

Perhaps other folders should be set to recover.

It's easy with this "feature" shoot your own feet (yes, in plural). If something wrong happens recover from the dump.

HTH

> Don't know exactly the name of the ntfs property, if i remember well it's
> recover point, but haven't used Windows for a while.

I think you're talking about shadow copies.  :)

Benny


--
"The very existence of flamethrowers proves that sometime, somewhere,
someone said to themselves, 'You know, I want to set those people
over there on fire, but I'm just not close enough to get the job
done.'"                          -- George Carlin

On 1/30/2013 5:12 AM, C. Bensend wrote:
>> >Don't know exactly the name of the ntfs property, if i remember well it's
>> >recover point, but haven't used Windows for a while.
> I think you're talking about shadow copies.


the UI and usability of NTFS shadow copies is quite frustrating. you can
create them, but you can't actually access them, they are only available
to software like backup programs that know how to use them.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast