1

I am in process of a pg_upgrade from 8.4 to 9.3. I am using this technique:

http://momjian.us/main/writings/pgsql/pg_upgrade.pdf

The upgrade has been running since 250 hours, and it has been on the step saving database definition since 160 hours. This is the current output of strace last few lines:

poll([{fd=5, events=POLLIN|.             
POLLERR}], 1, -1) = 1 ([{fd=5,   
revents=POLLIN}])
recvfrom(5, "T\0\0\0F
\0\2reltoastrelid\0\0\0\4\353\0\n
\0\0\0\32\0"..., 16384, 0, NULL, 
NULL) = 122
(5, "Q\0\0\0\221SELECT 
attname, attacl FROM"..., 146,  
MSG_NOSIGNAL, NULL, 0) = 146
poll([{fd=5, events=POLLIN|
POLLERR}], 1, -1

Is there a way to estimate how much time it will take? There are ~3,300,000 objects in pg_class and the database has around 765,000 tables. There are around 3-5 columns in most tables, and around 2,000,000 records in total.

user3455531
  • 775
  • 1
  • 14
  • 28
  • From what I can see, `pg_upgrade` should be really fast. Despite having a lot of tables, you don't have a really huge data (2kk is not all that much nowadays).I think you should try dump/restore on a separate machine. Do you really have any system activity, like disk IO or CPU usage? Or does it look like there is something locked? – Vinícius Gobbo A. de Oliveira Oct 07 '14 at 21:13
  • This extreme slowness with a large number of objects is a known problem, presumably fixed but only in most recent versions. See [pg_upgrade on high number tables database issues](http://www.postgresql.org/message-id/CAFj8pRAv_hr5BoPjogg_dD=D_iHjK3KJCA4DgMh+uZnVzBzn7w@mail.gmail.com) or [Pg_upgrade speed for many tables](http://www.postgresql.org/message-id/20121105200817.GA16323@momjian.us) – Daniel Vérité Oct 07 '14 at 21:33
  • @DanielVérité `pg_upgrade` will be using 9.3's `pg_dump` though, so it should be on the current release. To the OP: You should see if you can connect to the socket it's using and take a look at `pg_stat_activity`. My suggestion though: http://www.postgresql.org/support/professional_support/ . – Craig Ringer Oct 08 '14 at 01:30
  • thanks for your suggestions. I have 32 processors in my server and the upgrade is using 100% of one of them. along with that it is using 30GB Ram – user3455531 Oct 08 '14 at 04:56
  • Reposted to http://dba.stackexchange.com/q/78649/7788 – Craig Ringer Oct 08 '14 at 07:08
  • 1
    @user3455531 Out of interest, how long does a normal `pg_dump` of this database take? – Craig Ringer Oct 08 '14 at 07:08
  • 1
    @Craig: quoting Jeff Janes in the first thread: _Unfortunately the worst of those bottlenecks were in the server, so they depend on what database you are upgrading from_. That makes it clear that using 9.3's `pg_dump` does not help with this specific problem, it will only on future upgrades. – Daniel Vérité Oct 08 '14 at 08:06
  • i never did a pg_dump on this database – user3455531 Oct 08 '14 at 09:07

0 Answers0