I've inherited a large PostgreSQL 8.4.7 database that is currently not being backup at all. The database itself I believe is around 100GB, I've been reading about the different methods to backup this type of DB here
My questions are:
1) Typically how fast or slow is the pg_dump
dump process? I know its a subjective question and depends on hardware and the database, but roughly 100gb can I expect this to take 1 hour, 12 hours, 24+? I'd like to set an expectation with management and users before I begin.
2) The restore instructions indicate the following:
Before restoring a SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, then the restore will fail to recreate the objects with the original ownership and/or permissions.
Is it possible to gather this information as part of the backup? Or is there a separate backup process for users and permissions? Because I'm inheriting this database, Im not sure about all the users and permissions yet and would like to just backup everything for offsite storage.
pg_dump
like you expect to have to stop the server. You don't. You should be able to dump a live server just fine. There's some increase in load - but you can address that to a degree with anionice
and/orrenice
on thepostgres
backend that's feedingpg_dump
. The main impact is that the long transaction can hamperautovacuum
's work somewhat.