pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLost statistics after restore database
3+ messages / 3 participants
[nested] [flat]
* Lost statistics after restore database
@ 2015-02-04 07:01 For@ll <forall@stalowka.info>
0 siblings, 1 reply; 3+ messages in thread
From: For@ll @ 2015-02-04 07:01 UTC (permalink / raw)
To: pgsql-admin
Hi,
I have weird problem with database postgresql 9.3.5. I tried to use
pg_upgrade to migrate dabase from postgresql 9.2 to 9.3 it ended in 2
minutes - orginal database has 45GB and after pg_upgrade has 200MB.
So I used pg_dump (9.3.5) with binary format (Fc) and then restore with
pg_restore 9.3.5. Database did restore but thereis problem with
statistics. I have the same database on the other server and when I look
for statistics I have 75 rows and on my broken db I should have the
same, but I have only 20 rows and there are queries that lasts and lasts.
If I give explain analyze for them it last for 45 minutes and more...
--
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] 3+ messages in thread
* Re: Lost statistics after restore database
@ 2015-02-09 19:25 Bruce Momjian <bruce@momjian.us>
parent: For@ll <forall@stalowka.info>
0 siblings, 1 reply; 3+ messages in thread
From: Bruce Momjian @ 2015-02-09 19:25 UTC (permalink / raw)
To: For@ll <forall@stalowka.info>; +Cc: pgsql-admin
On Wed, Feb 4, 2015 at 08:01:15AM +0100, For@ll wrote:
> Hi,
>
> I have weird problem with database postgresql 9.3.5. I tried to use
> pg_upgrade to migrate dabase from postgresql 9.2 to 9.3 it ended in
> 2 minutes - orginal database has 45GB and after pg_upgrade has
> 200MB.
> So I used pg_dump (9.3.5) with binary format (Fc) and then restore
> with pg_restore 9.3.5. Database did restore but thereis problem with
> statistics. I have the same database on the other server and when I
> look for statistics I have 75 rows and on my broken db I should have
> the same, but I have only 20 rows and there are queries that lasts
> and lasts.
> If I give explain analyze for them it last for 45 minutes and more...
Neither upgrade method preserves optimizer statistics, try ANALYZE.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
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] 3+ messages in thread
* Re: Lost statistics after restore database
@ 2015-02-10 06:26 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 3+ messages in thread
From: Shreeyansh Dba @ 2015-02-10 06:26 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: For@ll <forall@stalowka.info>; pgsql-admin
On Tue, Feb 10, 2015 at 12:55 AM, Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Feb 4, 2015 at 08:01:15AM +0100, For@ll wrote:
> > Hi,
> >
> > I have weird problem with database postgresql 9.3.5. I tried to use
> > pg_upgrade to migrate dabase from postgresql 9.2 to 9.3 it ended in
> > 2 minutes - orginal database has 45GB and after pg_upgrade has
> > 200MB.
> > So I used pg_dump (9.3.5) with binary format (Fc) and then restore
> > with pg_restore 9.3.5. Database did restore but thereis problem with
> > statistics. I have the same database on the other server and when I
> > look for statistics I have 75 rows and on my broken db I should have
> > the same, but I have only 20 rows and there are queries that lasts
> > and lasts.
> > If I give explain analyze for them it last for 45 minutes and more...
>
> Neither upgrade method preserves optimizer statistics, try ANALYZE.
>
> --
> Bruce Momjian <bruce@momjian.us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + Everyone has their own god. +
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
Hi,
I think you should try analyze on your 9.3.5.
There is a script get created after performing the pg_upgrade
So I suggest you to run the analyze script and check the statistics in
catalog tables.
Regards
Ajinkya Bangale
Database administrator
www.shreeyansh.com
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-02-10 06:26 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-02-04 07:01 Lost statistics after restore database For@ll <forall@stalowka.info>
2015-02-09 19:25 ` Bruce Momjian <bruce@momjian.us>
2015-02-10 06:26 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
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