分类导航

程序问答发布时间：2022-06-02 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了仅在重新加载 Spring Boot 应用程序后才更改 PostgreSQL 模式，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决仅在重新加载 Spring Boot 应用程序后才更改 POSTGReSQL 模式？

开发过程中遇到仅在重新加载 Spring Boot 应用程序后才更改 POSTGReSQL 模式的问题如何解决？下面主要结合日常开发的经验，给出你关于仅在重新加载 Spring Boot 应用程序后才更改 POSTGReSQL 模式的解决方法建议，希望对你解决仅在重新加载 Spring Boot 应用程序后才更改 POSTGReSQL 模式有所启发或帮助；

在我的 spring boot 应用程序中，第一次迁移为当前用户创建了一个架构并切换到这个架构。后续迁移将在此架构上正确执行。但是，迁移完成后，直到重新加载应用程序时，应用程序才会找到这些表。

application.yml

spring:
  r2dbc:
    url: r2dbc:POSTGResql://127.0.0.1:5432/pickle_db
    username: rick
    password: morty

r2dbc:
  migrate:
    migrations-scheR_856_11845@a: public
    migrations-table: fun_migrations
    migrations-lock-table: fun_migrations_lock
    resources-paths:
      - classpath:/db/migration/*.sql

V1__scheR_856_11845@a.sql

CREATE scheR_856_11845@A IF NOT EXISTS fun;
ALTER RolE current_user SET search_path TO 'fun';
SET search_path TO 'fun';

V2__tables.sql

create table TREE(ID int,name varchar(64));

迁移成功运行并创建以下表格。

fun.tree
public.fun_migration
public.fun_migration_lock

2021-06-17 19:58:51.845  INFO 4400 --- [  restartedMain] n.n.r.m.a.R2dbcMigrateautoConfiguration  : StarTing R2DBC migration
2021-06-17 19:58:51.847  INFO 4400 --- [  restartedMain] n.n.r2dbc.migrate.core.R2dbcMigrate      : Configured with R2dbcMigratePropertIEs{enable=true,connectionMaxRetrIEs=500,resourcesPaths=[classpath:/db/migration/*.sql],chunkSize=1000,dialect=null,valIDationquery='SELEct '42' as result',valIDationqueryExpectedResultValue='42',valIDationqueryTimeout=PT5S,valIDationRetryDelay=PT1S,acquireLockRetryDelay=PT1S,acquireLockMaxRetrIEs=100,fileCharset=UTF-8,waitForDatabase=true,migrationsscheR_856_11845@a='public',migrationstable='fun_migrations',migrationsLocktable='fun_migrations_lock'}
2021-06-17 19:58:51.909  INFO 4400 --- [  restartedMain] n.n.r2dbc.migrate.core.R2dbcMigrate      : CreaTing new test connection
2021-06-17 19:58:52.523  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Comparing expected value '42' with provIDed result '42'
2021-06-17 19:58:52.525  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Closing test connection
2021-06-17 19:58:52.532  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : successfully got result '42' of test query
2021-06-17 19:58:52.678  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Making internal tables' 1 rows updated
2021-06-17 19:58:52.692  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Acquiring lock' 1 rows updated
2021-06-17 19:58:52.702  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Database version is 0
2021-06-17 19:58:52.723  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Applying MigrationInfo{version=1,description='scheR_856_11845@a',splitByline=false,transactional=truE}
2021-06-17 19:58:52.750  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'MigrationInfo{version=1,transactional=truE}' 0 rows updated
2021-06-17 19:58:52.793  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'WriTing Metadata version 1' 1 rows updated
2021-06-17 19:58:52.800  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Applying MigrationInfo{version=2,description='tables',transactional=truE}
2021-06-17 19:58:52.814  WARN 4400 --- [actor-tcp-nio-1] i.r.p.clIEnt.ReactorNettyClIEnt          : Notice: SEVERITY_LOCAliZED=NOTICE,SEVERITY_NON_LOCAliZED=NOTICE,CODE=00000,messaGE=table "tree" does not exist,skipPing,file=tablecmds.c,liNE=1217,RoutINE=DropErrorMsgNonExistent
2021-06-17 19:58:52.986  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'MigrationInfo{version=2,transactional=truE}' 0 rows updated
2021-06-17 19:58:53.027  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'WriTing Metadata version 2' 1 rows updated
2021-06-17 19:58:53.036  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : Applying MigrationInfo{version=3,description='data',transactional=truE}
2021-06-17 19:58:53.058  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'MigrationInfo{version=3,transactional=truE}' 94 rows updated
2021-06-17 19:58:53.072  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'WriTing Metadata version 3' 1 rows updated
2021-06-17 19:58:53.084  INFO 4400 --- [actor-tcp-nio-1] n.n.r2dbc.migrate.core.R2dbcMigrate      : By 'Releasing lock' 1 rows updated
2021-06-17 19:58:53.090  INFO 4400 --- [  restartedMain] n.n.r.m.a.R2dbcMigrateautoConfiguration  : End of R2DBC migration

一旦我连接到应用程序，我就会收到以下错误。

POSTGResql 日志

database_1  | 2021-06-17 17:56:29.903 UTC [1] LOG:  starTing POSTGResql 13.2 on x86_64-pc-linux-musl,compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203,64-bit
database_1  | 2021-06-17 17:56:29.910 UTC [1] LOG:  Listening on IPv4 address "0.0.0.0",port 5432
database_1  | 2021-06-17 17:56:29.910 UTC [1] LOG:  Listening on IPv6 address "::",port 5432
database_1  | 2021-06-17 17:56:29.939 UTC [1] LOG:  Listening on Unix socket "/var/run/POSTGResql/.s.PGsql.5432"
database_1  | 2021-06-17 17:56:29.960 UTC [51] LOG:  database system was shut down at 2021-06-17 17:56:29 UTC
database_1  | 2021-06-17 17:56:29.972 UTC [1] LOG:  database system is ready to accept connections
database_1  | 2021-06-17 18:03:52.818 UTC [65] ERROR:  relation "tree" does not exist at character 15
database_1  | 2021-06-17 18:03:52.818 UTC [65] STATEMENT:  SELECT * FROM tREE

重新启动 spring boot 应用程序后，一切正常。我假设在重新启动之前选择了不存在的 public.tree。应用程序重新启动后，fun.tree 将被选中。因此，这仅在第一次迁移之后发生。如何使迁移期间使用的 search_path 持久化？或者，如何在迁移后重新加载连接，以便使用定义的角色 search_path？

更新 2021-06-18

我已经找到了这个问题的原因。 spring-boot-starter-data-r2dbc 拉入 io.r2dbc:r2dbc-pool。它在执行 ALTER RolE current_user SET search_path TO 'fun'; 之前创建了 10 个连接。 SET search_path TO 'fun'; 仅对运行迁移的一个会话有效。

那么问题归结为，如何刷新池的所有连接？