Обсуждение: pg_restore new parameter request
PostgreSQL pg_restore new feature request
I did some testing last week to see how fast I could get pg_restore to reload data. I found some settings that made a significant speed boost. One request I have is the addition of two parameters to pg_restore.
--no-indexes
When this parameter is used during a full restore of a database the indexes would not be applied.
--only-indexes
When this parameter is selected during a full restore of a database only the indexes would be applied.
Why have these two flags?
The first parameter allows a full database load to be completed faster by not applying indexes. For my database this would be a 15% to 20% load improvement. In a restore situation I could then get the database up and running sooner. Then apply the indexes once my web applications are back up and running. Without the indexes the production system would run slower until they were applied but at least I would be up and running.
Thanks for considering adding these parameters to pg_restore.
Lance Campbell
Web Services
University of Illinois
"Campbell, Lance" <lance@illinois.edu> writes:
> PostgreSQL pg_restore new feature request
> I did some testing last week to see how fast I could get pg_restore to reload data. I found some settings that made
asignificant speed boost. One request I have is the addition of two parameters to pg_restore.
> --no-indexes
> When this parameter is used during a full restore of a database the indexes would not be applied.
> --only-indexes
> When this parameter is selected during a full restore of a database only the indexes would be applied.
This seems fairly redundant with the existing notion of "dump sections".
Have you experimented with "--section=post-data"?
regards, tom lane
Without the indexes the production system would run slower until they were applied but at least I would be up and running.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Mon, Nov 23, 2015 at 7:37 AM, Campbell, Lance <lance@illinois.edu> wrote: > > Without the indexes the production system would run slower until they were applied but at least I would be up and running. > > âAt least until excessive sequential scanning overloads the I/O subsystem and you max out your processes, connectionsand/or timeouts... > > I'd be curious to know if you've ever tried dropping all of your indexes and running your systems under somewhat realisticload levels. Agreed. Unless the OPs app/DB are either tiny and utterly trivial or the app that they're in a hurry to get online again is something that just inserts data, then the notion of omitting all indexes is rather far-fetched and I bet not too generally useful. I can certainly imagine a scenario where there are very large tables which have some indexes created just to support reporting/analytics workloads which perhaps could be deferred in building till after most other application aspects are running. In such a case, then you'd want just to omit and/or reorder building those after everything else. FWIW > David J. > â > Â > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800