Обсуждение: trouble with upgrade from 9.0 (many schemas and tables)
Hello! I update the databases to version 9.1. Today, faced with a new challenge. The database itself is not very big 60G, but has ~ 164,000 tables in 1260 schemes. I tried and pg_upgrade and pg_dumpall and pg_dump. But they all work very, very long time. For so long that I do not have patience. And pg_dump worked for almost a day, gave "out off memory" (but this experience made a colleague, so vouch for the accuracy can not). I tried to just dump schemes-only, but also the procedure lasted more than 17 hours and not over .... I ran out of patience. If I am not mistaken, all of these programs work on approximately the same algorithm. At the beginning of block all the tables in all schemas, indexes, and so then.... So I do not see a way to significantly speed up the process. The only thing that came up while doing a dump on each schema. But will it be the right approach? Maybe tell me what to do?
On Tue, Jan 29, 2013 at 9:23 PM, Groshev Andrey <greenx@yandex.ru> wrote: > Hello! > I update the databases to version 9.1. > Today, faced with a new challenge. > The database itself is not very big 60G, but has ~ 164,000 tables in 1260 schemes. Are the schemas all about the same size (in number of tables)? There are several places in the code which are quadratic in the number of tables being dumped and restored. There have been many fixes recently in 9.2 and especially (future) 9.3, but unfortunately you have to upgrade in order to get those benefits. > I tried and pg_upgrade and pg_dumpall and pg_dump. > But they all work very, very long time. > For so long that I do not have patience. > And pg_dump worked for almost a day, gave "out off memory" Was it exactly that, or was it about shared memory for holding locks? ... > The only thing that came up while doing a dump on each schema. > But will it be the right approach? Unfortunately, that might be your best option to get around the quadratic behavior. You would probably want to use the pg_dump from 9.2, as there are improvements in that version of pg_dump to speed up partial dumps. You can use pg_dump from 9.2 against server 9.0 and still get the improvements. But that means you should be upgrading to 9.2 rather than 9.1. (Which you should probably do anyway unless you have a specific reason not to.) Cheers, Jeff
Groshev Andrey <greenx@yandex.ru> writes:
> I update the databases to version 9.1.
> Today, faced with a new challenge.
> The database itself is not very big 60G, but has ~ 164,000 tables in 1260 schemes.
> I tried and pg_upgrade and pg_dumpall and pg_dump.
> But they all work very, very long time.
This isn't very clear. Are you saying that you successfully updated to
9.1 and now you have a pg_dump performance problem you didn't have before?
Or are you still on 9.0 and you are just now finding out that you can't
pg_dump (that is, you don't have any routine backup policy)?
Also, 9.0.what and 9.1.what? We've fixed a number of pg_dump
performance issues with huge schemas in the last year or two, but it's
impossible to guess whether you have those fixes.
regards, tom lane
30.01.2013, 18:47, "Jeff Janes" <jeff.janes@gmail.com>: > On Tue, Jan 29, 2013 at 9:23 PM, Groshev Andrey <greenx@yandex.ru> wrote: > >> Hello! >> I update the databases to version 9.1. >> Today, faced with a new challenge. >> The database itself is not very big 60G, but has ~ 164,000 tables in 1260 schemes. > > Are the schemas all about the same size (in number of tables)? Yes, 130 tables, 120 triggers, 700 functions. Only some schemas different from them. > There are several places in the code which are quadratic in the number > of tables being dumped and restored. There have been many fixes > recently in 9.2 and especially (future) 9.3, but unfortunately you > have to upgrade in order to get those benefits. > >> I tried and pg_upgrade and pg_dumpall and pg_dump. >> But they all work very, very long time. >> For so long that I do not have patience. >> And pg_dump worked for almost a day, gave "out off memory" > > Was it exactly that, or was it about shared memory for holding locks? > ... Unfortunately, I can't say anything about memory problems. This post was my colleague. In my attempt to not wait ... but I waited 17 hours. >> The only thing that came up while doing a dump on each schema. >> But will it be the right approach? > > Unfortunately, that might be your best option to get around the > quadratic behavior. Yes, some string in shell script. Before me, this problem is solved as well. > You would probably want to use the pg_dump from 9.2, as there are > improvements in that version of pg_dump to speed up partial dumps. > You can use pg_dump from 9.2 against server 9.0 and still get the > improvements. But that means you should be upgrading to 9.2 rather > than 9.1. (Which you should probably do anyway unless you have a > specific reason not to.) > > Cheers, > > Jeff Now think about it. Try to make 9.0 -> 9.2 -> 9.1 ? Thank you!
On Wednesday, January 30, 2013, Groshev Andrey wrote: > > > 30.01.2013, 18:47, "Jeff Janes" <jeff.janes@gmail.com <javascript:;>>: > > > You would probably want to use the pg_dump from 9.2, as there are > > improvements in that version of pg_dump to speed up partial dumps. > > You can use pg_dump from 9.2 against server 9.0 and still get the > > improvements. But that means you should be upgrading to 9.2 rather > > than 9.1. (Which you should probably do anyway unless you have a > > specific reason not to.) > > Now think about it. Try to make 9.0 -> 9.2 -> 9.1 ? > > Downgrading is generally not supported. Why not just stick with 9.2 as the end target? If you really need 9.1, I would not entertain the thought of using 9.2's pg_dump and then trying to go backwards, at least not until I tried 9.1's dump and proved that it was a serious bottleneck. Cheers, Jeff