pg_restore schema dump to schema with different name
Hi, I know I can alter schema name after restoring but the problem is the name already exist and I don't want to touch that existing schema.The dump type is "custom". So effectively I want something like.pg_dump -U postgres --schema "source_schema" --format "c" --create --file "source_schema.bak" my_dbpg_restore -U postgres --exit-on-error --dbname "my_db" --destination-schema "destination_schema" Currently this is not something can do. this functionality is there in oracle. Is this future considering to add? (it would really help for create any test schemas without disturbing current schema. ) Thanks,Rj
Re: pg_restore schema dump to schema with different name
On Mon, 2021-08-23 at 09:44 +, Nagaraj Raj wrote: > I know I can alter schema name after restoring but the problem is the name > already exist and I don't want to touch that existing schema. > The dump type is "custom". > > So effectively I want something like. > pg_dump -U postgres --schema "source_schema" --format "c" --create --file > "source_schema.bak" my_db > pg_restore -U postgres --exit-on-error --dbname "my_db" --destination-schema > "destination_schema" The only way to do that is to create a new database, import the data there, rename the schema and dump again. Then import that dump into the target database. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: pg_restore schema dump to schema with different name
The only way to do that is to create a new database, import the data there, rename the schema and dump again. Then import that dump into the target database. Or maybe (if you can afford to have source_schema unavailable for some time) : * rename source_schema to tmp_source * import (that will re-create source_schema) * rename source_schema to destination_schema * rename back tmp_source to source_schema
Re: pg_restore schema dump to schema with different name
Wouldn’t be easy if we have option to_schema ? Absolutely, I should not alter current schema, as it live 24/7. Thanks,RjOn Monday, August 23, 2021, 06:39:03 AM PDT, Jean-Christophe Boggio wrote: > The only way to do that is to create a new database, import the data > there, rename the schema and dump again. > > Then import that dump into the target database. Or maybe (if you can afford to have source_schema unavailable for some time) : * rename source_schema to tmp_source * import (that will re-create source_schema) * rename source_schema to destination_schema * rename back tmp_source to source_schema
Re: Postgres using the wrong index index
On Mon, Aug 23, 2021 at 08:53:15PM -0400, Matt Dupree wrote: > Is it possible that the row estimate is off because of a column other than > time? I would test this by writing the simplest query that reproduces the mis-estimate. > I looked at the # of events in that time period and 1.8 million is > actually a good estimate. What about the > ((strpos(other_events_1004175222.hierarchy, '#close_onborading;'::text) <> > 0) condition in the filter? It makes sense that Postgres wouldn't have a > way to estimate how selective this condition is. The issue I see is here. I don't know where else I'd start but to understand this. | Index Scan using other_events_1004175222_pim_evdef_67951aef14bc_idx on public.other_events_1004175222 (cost=0.28..1,648,877.92 ROWS=1,858,891 width=32) (actual time=1.008..15.245 ROWS=23 loops=1) |Output: other_events_1004175222.user_id, other_events_1004175222."time", other_events_1004175222.session_id |Index Cond: ((other_events_1004175222."time" >= '162477720'::bigint) AND (other_events_1004175222."time" <= '162736920'::bigint)) |Buffers: shared read=25 This has no "filter" condition, it's a "scan" node with bad over-estimate. Note that this is due to the table's column stats, not any index's stats, so every plan is affected. even though some happen to work well. The consequences of over-estimates are not as terrible as for under-estimates, but it's bad to start with inputs that are off by 10^5. -- Justin
Re: Postgres using the wrong index index
I increased (and decreased) the stats target for the column and re-analyzed. Didn't make a difference. Is it possible that the row estimate is off because of a column other than time? I looked at the # of events in that time period and 1.8 million is actually a good estimate. What about the ((strpos(other_events_1004175222.hierarchy, '#close_onborading;'::text) <> 0) condition in the filter? It makes sense that Postgres wouldn't have a way to estimate how selective this condition is. On Tue, Aug 17, 2021 at 2:52 PM Justin Pryzby wrote: > On Mon, Aug 16, 2021 at 11:22:44AM -0400, Matt Dupree wrote: > > > Is either half of the AND estimated correctly? If you do a query > > > with only ">=", and a query with only "<=", do either of them give an > > > accurate rowcount estimate ? > > > > Dropping >= results in the correct index being used. Dropping <= doesn't > > have this effect. > > This doesn't answer the question though: are the rowcount estimes accurate > (say > within 10%). > > It sounds like interpolating the histogram is giving a poor result, at > least > over that range of values. It'd be interesting to see the entire > histogram. > > You might try increasing (or decreasing) the stats target for that column, > and > re-analyzing. > > Your histogram bounds are for ~38 months of data, and your query is for the > previous month (July). > > $ date -d @1530186399 > Thu Jun 28 06:46:39 CDT 2018 > $ date -d @1629125609 > Mon Aug 16 09:53:29 CDT 2021 > > $ date -d @1627369200 > Tue Jul 27 02:00:00 CDT 2021 > $ date -d @1624777200 > Sun Jun 27 02:00:00 CDT 2021 > > The timestamp column has ndistinct near -1, similar to a continuous > distribution, so I'm not sure why the estimate would be so bad. > > -- > Justin > -- K. Matt Dupree Data Science Engineer 321.754.0526 | matt.dup...@heap.io
