pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
File system backup question
4+ messages / 3 participants
[nested] [flat]

* File system backup question
@ 2006-08-04 02:06 Benjamin Krajmalnik <kraj@illumen.com>
  2006-08-04 03:01 ` Re: File system backup question Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Benjamin Krajmalnik @ 2006-08-04 02:06 UTC (permalink / raw)
  To: pgsql-admin

I am currently running PG 8.1.4 windows.
The system is for a real time monitoring application (so downtime needs
to be minimized if at all possible).
 
I will be migratng to a new server running PG 8.1.4 on FreeBSD 6.1.
I have been playing with various options for migrating the data.  The
database is currently about 8.5 GB.
pg_dump took about 90 minutes.
pg_restore has been running for over an hour and is abou 4% done (based
on row counts on the tables).
 
I used the pg_dump option to insert records (as oposed to use the copy
command - I don't know which would be faster for the restore).
 
In any case, I was thinking about performing a file system backup.
My concern is that I am movinf between different OS's (although both are
i386 architecture).  I will probably try it tomorrow to see if it works,
but was just wondering if there are any caveats which I should be aware
of.
I hope this works, since for my scenario, this will have the least
downtime.
 
Thanks in advance for any advice.
 
Regards,
 
Benjamin
 
 
 
PS.  For those who have asked about performance, Windows is much slower
than FreeBSD.
 
Machine specs are as follows:
 
Windows 2003 server, HP DL360, SCSI RAID-1, 2GB RAM, 2.8GHz XEON HT
FreeBSD 6.1, SuperMicro 5014C-T, SATA RAID-1, 1GB RAM, P4 3GHz
 
My stored procedure call went from 47ms avg to 6 ms average execution
time on server, as reported by turning logging on
All queries in general appeared to run between 5x to 10x faster.
 
 
 
 

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

* Re: File system backup question
  2006-08-04 02:06 File system backup question Benjamin Krajmalnik <kraj@illumen.com>
@ 2006-08-04 03:01 ` Joshua D. Drake <jd@commandprompt.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Joshua D. Drake @ 2006-08-04 03:01 UTC (permalink / raw)
  To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: pgsql-admin

Benjamin Krajmalnik wrote:
> I am currently running PG 8.1.4 windows.
> The system is for a real time monitoring application (so downtime needs
> to be minimized if at all possible).
>  
> I will be migratng to a new server running PG 8.1.4 on FreeBSD 6.1.
> I have been playing with various options for migrating the data.  The
> database is currently about 8.5 GB.
> pg_dump took about 90 minutes.
> pg_restore has been running for over an hour and is abou 4% done (based
> on row counts on the tables).
>  
> I used the pg_dump option to insert records (as oposed to use the copy
> command - I don't know which would be faster for the restore).

That is why it is taking so long.

Do not use inserts
Turn off fsync
Crank up shared buffers
make sure you have lots of checkpoint_segments.

Joshua D. Drake


>  
> In any case, I was thinking about performing a file system backup.
> My concern is that I am movinf between different OS's (although both are
> i386 architecture).  I will probably try it tomorrow to see if it works,
> but was just wondering if there are any caveats which I should be aware
> of.
> I hope this works, since for my scenario, this will have the least
> downtime.
>  
> Thanks in advance for any advice.
>  
> Regards,
>  
> Benjamin
>  
>  
>  
> PS.  For those who have asked about performance, Windows is much slower
> than FreeBSD.
>  
> Machine specs are as follows:
>  
> Windows 2003 server, HP DL360, SCSI RAID-1, 2GB RAM, 2.8GHz XEON HT
> FreeBSD 6.1, SuperMicro 5014C-T, SATA RAID-1, 1GB RAM, P4 3GHz
>  
> My stored procedure call went from 47ms avg to 6 ms average execution
> time on server, as reported by turning logging on
> All queries in general appeared to run between 5x to 10x faster.
>  
>  
>  
>  
> 


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/





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

* Re: File system backup question
@ 2006-08-04 04:05 Benjamin Krajmalnik <kraj@illumen.com>
  2006-08-08 16:48 ` Re: File system backup question Jim Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 4+ messages in thread

From: Benjamin Krajmalnik @ 2006-08-04 04:05 UTC (permalink / raw)
  To: jd@commandprompt.com; +Cc: pgsql-admin

I believe I have to use onserts due to the problem with the records with the improper utf encoding.  According to the docs this will cause the restore to fail.

I will try it as well as a file system restore.

-----Original Message-----
From: Joshua D. Drake <jd@commandprompt.com>
To: Benjamin Krajmalnik <kraj@illumen.com>
CC: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Sent: Thu Aug 03 21:01:54 2006
Subject: Re: [ADMIN] File system backup question

Benjamin Krajmalnik wrote:
> I am currently running PG 8.1.4 windows.
> The system is for a real time monitoring application (so downtime needs
> to be minimized if at all possible).
>  
> I will be migratng to a new server running PG 8.1.4 on FreeBSD 6.1.
> I have been playing with various options for migrating the data.  The
> database is currently about 8.5 GB.
> pg_dump took about 90 minutes.
> pg_restore has been running for over an hour and is abou 4% done (based
> on row counts on the tables).
>  
> I used the pg_dump option to insert records (as oposed to use the copy
> command - I don't know which would be faster for the restore).

That is why it is taking so long.

Do not use inserts
Turn off fsync
Crank up shared buffers
make sure you have lots of checkpoint_segments.

Joshua D. Drake


>  
> In any case, I was thinking about performing a file system backup.
> My concern is that I am movinf between different OS's (although both are
> i386 architecture).  I will probably try it tomorrow to see if it works,
> but was just wondering if there are any caveats which I should be aware
> of.
> I hope this works, since for my scenario, this will have the least
> downtime.
>  
> Thanks in advance for any advice.
>  
> Regards,
>  
> Benjamin
>  
>  
>  
> PS.  For those who have asked about performance, Windows is much slower
> than FreeBSD.
>  
> Machine specs are as follows:
>  
> Windows 2003 server, HP DL360, SCSI RAID-1, 2GB RAM, 2.8GHz XEON HT
> FreeBSD 6.1, SuperMicro 5014C-T, SATA RAID-1, 1GB RAM, P4 3GHz
>  
> My stored procedure call went from 47ms avg to 6 ms average execution
> time on server, as reported by turning logging on
> All queries in general appeared to run between 5x to 10x faster.
>  
>  
>  
>  
> 


-- 

    === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
    Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/




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

* Re: File system backup question
  2006-08-04 04:05 Re: File system backup question Benjamin Krajmalnik <kraj@illumen.com>
@ 2006-08-08 16:48 ` Jim Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Jim Nasby @ 2006-08-08 16:48 UTC (permalink / raw)
  To: Benjamin Krajmalnik <kraj@illumen.com>; +Cc: jd@commandprompt.com; pgsql-admin

If you must use inserts, you'll get much better performance if you  
wrap the restore in a transaction. I think someone came up with a  
clever way to do this on the command line, or you could edit the dump  
file. Note that if you do this and there's any errors, the restore  
will fail.

Also, greatly increasing maintenance_work_mem during a restore can  
help with the index builds.

On Aug 3, 2006, at 11:05 PM, Benjamin Krajmalnik wrote:
> I believe I have to use onserts due to the problem with the records  
> with the improper utf encoding.  According to the docs this will  
> cause the restore to fail.
>
> I will try it as well as a file system restore.
>
> -----Original Message-----
> From: Joshua D. Drake <jd@commandprompt.com>
> To: Benjamin Krajmalnik <kraj@illumen.com>
> CC: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
> Sent: Thu Aug 03 21:01:54 2006
> Subject: Re: [ADMIN] File system backup question
>
> Benjamin Krajmalnik wrote:
> > I am currently running PG 8.1.4 windows.
> > The system is for a real time monitoring application (so downtime  
> needs
> > to be minimized if at all possible).
> >
> > I will be migratng to a new server running PG 8.1.4 on FreeBSD 6.1.
> > I have been playing with various options for migrating the data.   
> The
> > database is currently about 8.5 GB.
> > pg_dump took about 90 minutes.
> > pg_restore has been running for over an hour and is abou 4% done  
> (based
> > on row counts on the tables).
> >
> > I used the pg_dump option to insert records (as oposed to use the  
> copy
> > command - I don't know which would be faster for the restore).
>
> That is why it is taking so long.
>
> Do not use inserts
> Turn off fsync
> Crank up shared buffers
> make sure you have lots of checkpoint_segments.
>
> Joshua D. Drake
>
>
> >
> > In any case, I was thinking about performing a file system backup.
> > My concern is that I am movinf between different OS's (although  
> both are
> > i386 architecture).  I will probably try it tomorrow to see if it  
> works,
> > but was just wondering if there are any caveats which I should be  
> aware
> > of.
> > I hope this works, since for my scenario, this will have the least
> > downtime.
> >
> > Thanks in advance for any advice.
> >
> > Regards,
> >
> > Benjamin
> >
> >
> >
> > PS.  For those who have asked about performance, Windows is much  
> slower
> > than FreeBSD.
> >
> > Machine specs are as follows:
> >
> > Windows 2003 server, HP DL360, SCSI RAID-1, 2GB RAM, 2.8GHz XEON HT
> > FreeBSD 6.1, SuperMicro 5014C-T, SATA RAID-1, 1GB RAM, P4 3GHz
> >
> > My stored procedure call went from 47ms avg to 6 ms average  
> execution
> > time on server, as reported by turning logging on
> > All queries in general appeared to run between 5x to 10x faster.
> >
> >
> >
> >
> >
>
>
> --
>
>     === The PostgreSQL Company: Command Prompt, Inc. ===
> Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
>     Providing the most comprehensive  PostgreSQL solutions since 1997
>               http://www.commandprompt.com/
>
>
>

--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



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


end of thread, other threads:[~2006-08-08 16:48 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-04 02:06 File system backup question Benjamin Krajmalnik <kraj@illumen.com>
2006-08-04 03:01 ` Joshua D. Drake <jd@commandprompt.com>
2006-08-04 04:05 Re: File system backup question Benjamin Krajmalnik <kraj@illumen.com>
2006-08-08 16:48 ` Jim Nasby <jnasby@pervasive.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