pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_restore new parameter request
4+ messages / 4 participants
[nested] [flat]
* pg_restore new parameter request
@ 2015-11-23 14:37 Campbell, Lance <lance@illinois.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Campbell, Lance @ 2015-11-23 14:37 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore new parameter request
@ 2015-11-23 14:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2015-11-23 14:50 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin
"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 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.
This seems fairly redundant with the existing notion of "dump sections".
Have you experimented with "--section=post-data"?
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore new parameter request
@ 2015-11-23 14:55 David G. Johnston <david.g.johnston@gmail.com>
parent: Campbell, Lance <lance@illinois.edu>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2015-11-23 14:55 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin
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, connections and/or timeouts...
I'd be curious to know if you've ever tried dropping all of your indexes
and running your systems under somewhat realistic load levels.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: pg_restore new parameter request
@ 2015-11-23 17:37 Jerry Sievers <gsievers19@comcast.net>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jerry Sievers @ 2015-11-23 17:37 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Campbell\, Lance <lance@illinois.edu>; pgsql-admin
"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, connections and/or timeouts...
>
> I'd be curious to know if you've ever tried dropping all of your indexes and running your systems under somewhat realistic load 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-11-23 17:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-23 14:37 pg_restore new parameter request Campbell, Lance <lance@illinois.edu>
2015-11-23 14:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2015-11-23 14:55 ` David G. Johnston <david.g.johnston@gmail.com>
2015-11-23 17:37 ` Jerry Sievers <gsievers19@comcast.net>
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