As we all know pg_upgrade handles tablespaces in a smart way. However it creates the tablespaces in the same location of old ones. If you want to change the location after upgrade, then you would need some work including updating the catalog table with the new location(IIRC, in PG9.4, you can do it by using ALTER TABLESPACE command). So, for current versions I would like to give you a work around to change the locations of tablespaces while running the pg_upgrade. This tweak is also applicable, If you have any contrib modules installed in databases of old cluster, you have to install them in new cluster also, however we can not create databases in new cluster as it should be empty to run pg_upgrade. "pg_upgrade" creates the databases using template "template 0" in the new cluster.
You can follow below steps to tweak the running pg_upgrade process.
1. I've upgraded PG9.1 cluster "Im_old_one" to PG9.3 cluster "Hey_Im_New", as you see below it is created new tablespace in the same directory.
3. Press control + z (pause the job) just after the catalog dump is created. You can pause the job when you see the below output:
Any suggestions/comments would be most welcome!
You can follow below steps to tweak the running pg_upgrade process.
1. I've upgraded PG9.1 cluster "Im_old_one" to PG9.3 cluster "Hey_Im_New", as you see below it is created new tablespace in the same directory.
-bash-4.1$ /opt/PostgreSQL/9.1/bin/psql -p 5666 -U postgres postgres Timing is on. psql.bin (9.1.7) Type "help" for help. postgres=# CREATE TABLESPACE tablsc_91 LOCATION '/tmp/tablsc_91'; CREATE TABLESPACE Time: 1.663 ms -bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_upgrade -b /opt/PostgreSQL/9.1/bin/ -B /opt/PostgreSQL/9.3/bin/ -d /tmp/Im_old_one/ -D /tmp/Hey_Im_New/ -p 5666 -P 5667 Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is a superuser 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 -bash-4.1$ -bash-4.1$ -bash-4.1$ ls -ltrh /tmp/tablsc_91/ total 8.0K drwx------. 2 postgres postgres 4.0K Apr 5 14:25 PG_9.1_201105231 drwx------. 2 postgres postgres 4.0K Apr 5 14:29 PG_9.3_2013061212. Now delete the new cluster and run the pg_upgrade command and keep monitoring it.
3. Press control + z (pause the job) just after the catalog dump is created. You can pause the job when you see the below output:
-bash-4.1$ /opt/PostgreSQL/9.3/bin/pg_upgrade -b /opt/PostgreSQL/9.1/bin/ -B /opt/PostgreSQL/9.3/bin/ -d /tmp/Im_old_one/ -D /tmp/Hey_Im_New/ -p 5666 -P 5667 Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is a superuser ok Checking for prepared transactions ok Checking for reg* system OID user data types ok Checking for contrib/isn with bigint-passing mismatch ok Creating dump of global objects ok Creating dump of database schemas ok ---> press control + z just after this. ^Z **Note : If we fail to pause job after this "Creating catalog dump" step, then please kill this job and re-initiate the new pg_upgrade process. The re-initiate of pg_upgrade does not require any creation of new cluster or any other changes.4. Open the global object dump and modify CREATE TABLESAPCE command to your new location:
-bash-4.1$ vi pg_upgrade_dump_globals.sql Above file will be created in the location from where you run pg_upgrate command, open the file and change the create tablespace command from old location to new location. Ex:- Change all the CREATE TABLESPACE statements in the file "pg_upgrade_dump_globals.sql" From CREATE TABLESPACE "tablsc_91" OWNER "postgres" LOCATION '/tmp/tablsc_91'; To CREATE TABLESPACE "tablsc_91" OWNER "postgres" LOCATION '/tmp/tablsc_93';5. Once modifications has been completed, then resume the job by using "fg" command. It should continue with the restoration process.
Any suggestions/comments would be most welcome!
I have tried a slightly different approach on Windows 10, with cygwin installed. I upgraded from 9.4 to 9.5. What I did, as far as changing tablespace paths, was to run a sed one-liner in a tight loop, which fix the "CREATE TABLESPACE" command created by PG, to reflect the new tablespace path:
ReplyDeleteif [[ -r pg_upgrade_dump_globals.sql ]]; then sed -i -e 's/\\\\9.4\\\\/\\\\9.5\\\\/g' pg_upgrade_dump_globals.sql;fi
Please note that the script is depending on sed which supports in-place editing (-i option). In Windows, make sure that user executing this one-liner has full access to the directory where the update script is generating file "pg_upgrade_dump_globals.sql" - otherwise editing in-place will fail.
BTW, used this write up last year and it was a saver. So thank you!!
ReplyDeleteUsed this many decades ago and just had to use it again, glad this page was still up
ReplyDelete