asked on
optimize PostgreSQL pg_dumpall speed
hi,
I have create a ticket :
and this topic is related to that.
if I use pg_dumpall to backup and restore DB from 9.3.6 to 11.14:
pg_dumpall -U postgres -p 5432 --jobs=8 > <output file.sql>
it will say :
pg_dumpall: illegal option -- jobs=8
Try "pg_dumpall --help" for more information.
for Pg_dump, we can optimize the backup progress by adding --jobs=N, can how we do the same thign for pg_dumpall?
PostgreSQL
Last Comment
David Favor
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Just be aware of differences between the two:all from here: https://www.postgresql.org/docs/9.4/app-pg-dumpall.html
ASKER
David ,
this is what I am wondering about, you can see from the message above.
if we only have 1 x database, the --jobs can't help much in this case ?'
pg_dump only backup one DB at a time ?
and we are saying no way to speed up pg_dumpall ?
This is impossible, as pg_dumpall has no --jobs option.
this is what I am wondering about, you can see from the message above.
Then run pg_dump against each database, as pg_dump does support the --jobs option.
if we only have 1 x database, the --jobs can't help much in this case ?'
pg_dump only backup one DB at a time ?
and we are saying no way to speed up pg_dumpall ?
ASKER
hi,
anyone tried the pg_dumpall in v13? and I wonder if use that one to dump from 9.3.6 and restore to 11.14 is much faster?
pg_dumpall from v13 bin folder much better?
anyone tried the pg_dumpall in v13? and I wonder if use that one to dump from 9.3.6 and restore to 11.14 is much faster?
pg_dumpall from v13 bin folder much better?
ASKER
one thing I can say for now is, pg_dumpall by V13 seems much faster and we are generating a 100GB and try it out later and compare with pg_dumpall by V11
can't tune like pg_dump anyway...
can't tune like pg_dump anyway...
All backup utilities tend to run slow, as they backup all tables, one by one, sequentially.
The speedup I'm working on with mysqldump, which applies to all database dumpers, is to parallelize backups.
1) Run some sort of queuing system.
2) Where queuing system runs 1x process/core, limited by number of cores specified for job or max queues.
3) For me, I run queue workers at nice -19 to unload system.
4) Now pull data showing table row count + reverse sort in descending order, largest to smallest.
5) Then spawn off 1x worker per table, so largest tables start backing up first.
The testing I've done with this approach suggests backups speed up dramatically.
The speedup I'm working on with mysqldump, which applies to all database dumpers, is to parallelize backups.
1) Run some sort of queuing system.
2) Where queuing system runs 1x process/core, limited by number of cores specified for job or max queues.
3) For me, I run queue workers at nice -19 to unload system.
4) Now pull data showing table row count + reverse sort in descending order, largest to smallest.
5) Then spawn off 1x worker per table, so largest tables start backing up first.
The testing I've done with this approach suggests backups speed up dramatically.
ASKER
but it seems pg_dumpall can't be parallelize right ?
ASKER
tks both.
You're welcome!
Hang in there!
Hang in there!