PostgreSQL pg_upgrade 大版本升级支持插件更新脚本

作者

digoal

日期

2021-08-05

标签

PostgreSQL , pg_upgrade , extension

如果你的new cluster安装的插件版本已经超过了old cluster的插件版本, 在pg_upgrade生成的脚本中会自动加入alter extension upgrade的内容, 执行升级插件.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=e462856a7a559c94bad51507c6b324f337d8254b

+  
+  
+/*  
+ * report_extension_updates()  
+ * Report extensions that should be updated.  
+ */  
+void  
+report_extension_updates(ClusterInfo *cluster)  
+{  
+   int         dbnum;  
+   FILE       *script = NULL;  
+   bool        found = false;  
+   char       *output_path = "update_extensions.sql";  
+  
+   prep_status("Checking for extension updates");  
+  
+   for (dbnum = 0; dbnum < cluster->dbarr.ndbs; dbnum++)  
+   {  
+       PGresult   *res;  
+       bool        db_used = false;  
+       int         ntups;  
+       int         rowno;  
+       int         i_name;  
+       DbInfo     *active_db = &cluster->dbarr.dbs[dbnum];  
+       PGconn     *conn = connectToServer(cluster, active_db->db_name);  
+  
+       /* find hash indexes */  
+       res = executeQueryOrDie(conn,  
+                               "SELECT name "  
+                               "FROM pg_available_extensions "  
+                               "WHERE installed_version != default_version"  
+           );  
+  
+       ntups = PQntuples(res);  
+       i_name = PQfnumber(res, "name");  
+       for (rowno = 0; rowno < ntups; rowno++)  
+       {  
+           found = true;  
+  
+           if (script == NULL && (script = fopen_priv(output_path, "w")) == NULL)  
+               pg_fatal("could not open file \"%s\": %s\n", output_path,  
+                        strerror(errno));  
+           if (!db_used)  
+           {  
+               PQExpBufferData connectbuf;  
+  
+               initPQExpBuffer(&connectbuf);  
+               appendPsqlMetaConnect(&connectbuf, active_db->db_name);  
+               fputs(connectbuf.data, script);  
+               termPQExpBuffer(&connectbuf);  
+               db_used = true;  
+           }  
+           fprintf(script, "ALTER EXTENSION %s UPDATE;\n",  
+                   quote_identifier(PQgetvalue(res, rowno, i_name)));  
+       }  
+  
+       PQclear(res);  
+  
+       PQfinish(conn);  
+   }  
+  
+   if (script)  
+       fclose(script);  
+  
+   if (found)  
+   {  
+       report_status(PG_REPORT, "notice");  
+       pg_log(PG_REPORT, "\n"  
+              "Your installation contains extensions that should be updated\n"  
+              "with the ALTER EXTENSION command.  The file\n"  
+              "    %s\n"  
+              "when executed by psql by the database superuser will update\n"  
+              "these extensions.\n\n",  
+              output_path);  
+   }  
+   else  
+       check_ok();  
+}  

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.