Re: performance database for backup/restore

On May 21, 2013, at 5:18 PM, Jeison Bedoya <jeisonb(at)audifarma(dot)com(dot)co> wrote:

> Hi people, i have a database with 400GB running in a server with 128Gb RAM, and 32 cores, and storage over SAN with fiberchannel, the problem is when i go to do a backup whit pg_dumpall take a lot of 5 hours, next i do a restore and take a lot of 17 hours, that is a normal time for that process in that machine? or i can do something to optimize the process of backup/restore.
>

I'd recommend you to dump with

pg_dump --format=c

It will compress the output and later you can restore it in parallel with

pg_restore -j 32 (for example)

Right now you can not dump in parallel, wait for 9.3 release. Or may be someone will back port it to 9.2 pg_dump.

Also during restore you can speed up a little more by disabling fsync and synchronous_commit.

> This is my current configuration
>
> Postgres version 9.2.2
> connections 1000
> shared buffers 4096MB
> work_mem = 2048MB
> maintenance_work_mem = 2048MB
> checkpoint_segments = 103
>
> the other params are by default.
>
> Thankyou very much
>
> --
> Atentamente,
>
>
> JEISON BEDOYA DELGADO
> Adm. Servidores y Comunicaciones
> AUDIFARMA S.A.
>
>
> --
> NOTA VERDE:
> No imprima este correo a menos que sea absolutamente necesario.
> Ahorre papel, ayude a salvar un arbol.
>
> --------------------------------------------------------------------
> Este mensaje ha sido analizado por MailScanner
> en busca de virus y otros contenidos peligrosos,
> y se considera que esta limpio.
>
> --------------------------------------------------------------------
> Este texto fue anadido por el servidor de correo de Audifarma S.A.:
>
> Las opiniones contenidas en este mensaje no necesariamente coinciden
> con las institucionales de Audifarma. La informacion y todos sus
> archivos Anexos, son confidenciales, privilegiados y solo pueden ser
> utilizados por sus destinatarios. Si por error usted recibe este
> mensaje, le ofrecemos disculpas, solicitamos eliminarlo de inmediato,
> notificarle de su error a la persona que lo envio y abstenerse de
> utilizar su contenido.
>
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance