1

Trying to do PostgreSQL upgrade from 9.0 to 9.3. I'm using pg_upgrade with the --link mode since the downtime is very minimal compared to other options like pg_dumpall and pg_upgrade without using --link.

After upgrading and executing an SQL-statement to check execution-time. It is taking nearly 30 minutes the 1st time and for the 2nd time onwards, it is taking 15 mins. The same query executed in 9.0 is not even taking a full 45 seconds.

No of Rows : 150000 records Using same postgresql.conf file vaccumdb all : done after upgrade

Is there any thing else I need to configure, to improve the performance?

It doesn't state anywhere in the documentation that pg_upgrade --link will degrade performance.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID for new cluster                 ok
Deleting files from new pg_multixact/offsets                ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster

mydatabase

Setting minmxid counter in new cluster                      ok
Removing support functions from new cluster                 ok
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /opt/postgres/9.0/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

I got a message like the above, but I couldn't find such files in the postgres folder to execute.

I am using postgresql 9.3.5 from openscg community

**postgresql.conf changes**

shared_buffers = 4GB
work_mem = 128MB
maintenance_work_mem = 256MB
effective_cache_size = 12GB
4
  • Show explain (buffers, analyze, verbose) select ... please. Mar 27, 2015 at 5:30
  • 3
    Sounds as if the statistics aren't up-to-date. pg_upgrade creates a script for that. Did you run it?
    – user1822
    Mar 27, 2015 at 7:05
  • @CraigRinger i have added the configuration in my question. please check.
    – arjun
    Mar 27, 2015 at 13:13
  • @a_horse_with_no_name it seems optimizer statistics is not transferred. i don't find analyze_new_cluster.sh
    – arjun
    Mar 27, 2015 at 13:15

2 Answers 2

4

After a pg_upgrade all your optimizer statistics are gone. You need to ANALYZE all of your tables. This is true regardless of whether or not you use --link.

3

It depends on where you ran your pg_upgrade, but when it has completed the process it will drop log files as well as two shell scripts in that same current working directory (not the postgres directory).

delete_old_cluster.sh
analyze_new_cluster.sh

You want to start up your new postgres cluster with pg_ctl and then run the analyze_new_cluster.sh. The reason your queries are so slow is that the indices are not the least optimal after the transfer. This process can take many hours to complete if you have a large database. Mine of 6.5 TB took 13 hours. They also suggest a faster mode just to get you going (you can then let autovacuum take care of the cleanup or do some followup analyze operations later):

vacuumdb --all --analyze-only

You will also want to revisit your new postgresql.conf file to make sure that your tuning parameters carry over, at least as many as are applicable. The params do change gradually with each new major version.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.