I am trying to test the migration of postgres to 10 from 9.5

On trying in a test DB of size 490 GB in one postgres node it is taking about 18 mins. The command I used

sudo -H -u postgres /usr/lib/postgresql/10/bin/pg_upgrade \
   -b /usr/lib/postgresql/9.5/bin \
   -B /usr/lib/postgresql/10/bin \
   -d /var/lib/postgresql/data/postgresql0 \
   -D /var/lib/postgresql/10/data/postgresql0 \
   -o ' -c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
   -O ' -c config_file=/etc/postgresql/10/main/postgresql.conf' -k 

Is there any faster way of doing this?

  • 18 min for half a TB doesn't feel slow to me. The other option would be the to do the (complicated) upgrade through pg_logical replication, which advertises minimal downtime. Nov 25 '17 at 11:26
  • 18 minutes is pretty slow when using link mode. How many objects (tables, indexes, sequences, etc.) do you have in the database? How long does it take to do pg_dump -s ? How long does it take restore said schema-only dump into a fresh new database?
    – jjanes
    Nov 26 '17 at 0:28

I used a script for loading junk data in postgres. It created a lot of databases (about 500). So this was causing pg_dump to take so much time.

After populating only the relevant database (about 100 GB) I am seeing good performance (about 5 second with -k option).

  • But this -k is already used in the command you've posted in your question. Of course -k is faster as it implies no data copy between versions migrated, it only moves data files from older instance to newer. But also lets older version unusable, so downgrade is not an option when using -k.
    – EAmez
    Nov 5 '19 at 10:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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