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
explain (buffers, analyze, verbose) select ...
please.pg_upgrade
creates a script for that. Did you run it?