¶ Introduction
With the recent release of OSE 3.0, where we support the (2) deployment methodologies:
- Solution Add-On Deployment (
Kubernetes) - Linux Package based (
rpm+deb)
In my deployment, I've been leveraging CentOS 7 with Postgres 13; however, CentOS 7 has reached the EOL. I've decided to clone my OSE psql DB to my Ubuntu 22.04.4 LTS to leverage OSE 3.0 as a Linux deployment.
- Later, I will deploy the Solution Add-On Deployment and create documentation on how to execute and orchestrate the deployment.
¶ Cloning Postgres DB
1. Configure the originating psql server to have the following attributes in the /var/lib/pgsql/13/data/pg_hba.conf to have the following attributes on the originating server.
2. Edit /var/lib/pgsql/13/data/pg_hba.conf
/var/lib/pgsql/13/data/pg_hba.conf3. You must switch peer and md5 to trust
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 0.0.0.0/0 trust
#host all all 127.0.0.1/32 scram-sha-256
# IPv6 local connections:
host all all ::1/128 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
peermeans it will trust the identity (authenticity) of the UNIX users. So not asking for a password.md5means it will always ask for a password, and validate it after hashing withMD5.trustmeans it will never ask for a password, and always trust any connection.
4. Restart the postgresql service via:
systemctl restart postgresql-13¶ PG Dump the PSQL DB from Local Server to Remote Server
- Requirement: The database must exist on the remote host.
- This article assumes that the end-user has created the
psqlDB and user on the remote server.
pg_dump -C -h <LOCALHOST> -U postgres osedb | psql -h <REMOTE_HOST> -U postgres osedb¶ Check the DB Contents
1. Using pg_dump dump the database from the original server:
pg_dump -U postgres -d osedb -f osedb-original.sql2. Using pg_dump dump the database from the remote server:
pg_dump -U postgres -d osedb -f osedb-remote.sql3. scp or copy the sql dump, and vimdiff the (2) DBs.
vimdiff osedb-original.sql osedb-remote.sql¶ Checking the DB Tables
1. Login into the psql CLI:
sudo -u postgres psql2. Connect to the DB osedb
\c osedb3. Invoke the dt command:
\dt4. On the remote server, ensure you have the same number of rows and that the owner is the same:
postgres=# \c osedb
You are now connected to database "osedb" as user "postgres".
osedb=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------+----------
public | bucket_cors_rule | table | oseadmin
public | bucket_info | table | oseadmin
public | cluster_protection_info | table | oseadmin
public | entity_alias | table | oseadmin
public | entity_sync_config | table | oseadmin
public | entity_sync_info | table | oseadmin
public | external_cluster_info | table | oseadmin
public | flyway_schema_history | table | oseadmin
public | folder_info | table | oseadmin
public | multi_upload_info | table | oseadmin
public | object_info | table | oseadmin
public | onboard_timeline | table | oseadmin
public | ose_qrtz_blob_triggers | table | oseadmin
public | ose_qrtz_calendars | table | oseadmin
public | ose_qrtz_cron_triggers | table | oseadmin
public | ose_qrtz_fired_triggers | table | oseadmin
public | ose_qrtz_job_details | table | oseadmin
public | ose_qrtz_locks | table | oseadmin
public | ose_qrtz_paused_trigger_grps | table | oseadmin
public | ose_qrtz_scheduler_state | table | oseadmin
public | ose_qrtz_simple_triggers | table | oseadmin
public | ose_qrtz_simprop_triggers | table | oseadmin
public | ose_qrtz_triggers | table | oseadmin
public | platform_user_mapping | table | oseadmin
public | region_endpoint_info | table | oseadmin
public | role_right_mapping | table | oseadmin
public | shared_bucket_info | table | oseadmin
public | sub_role_assignment | table | oseadmin
public | sub_role_assignment_sub_roles | table | oseadmin
public | system_config | table | oseadmin
public | task_info | table | oseadmin
public | telemetry_metrics | table | oseadmin
public | telemetry_metrics_info | table | oseadmin
public | tenant_encryption_info | table | oseadmin
public | tenant_info | table | oseadmin
public | user_key_info | table | oseadmin
public | user_mapping_info | table | oseadmin
(37 rows)¶ Export OSE Configuration
Export the OSE configuration from the original server:
- This only exports the ose endpoint configuration
ose config export --file="ose-config" --secret="secret"¶ Import OSE Configuration
Import the OSE configuration from the original server (This file should be scp over):
- This only imports the ose endpoint configuration
ose config import --file="ose-config" --secret="secret"¶ Issues Faced During Migration to Ubuntu and PSQL 14
1. The flyway_schema_history is different in OSE 3.0 and the ose-default.log will parse the following:
/opt/vmware/voss/log/ose-default.log
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'flywayInitializer' defined in class path resource [org/springframework/boot/autoconfigure/flyway/FlywayAutoConfiguration$FlywayConfiguration.class]: Validate failed: Migrations have failed validation
Detected applied migration not resolved locally: 3.0.8.
If you removed this migration intentionally, run repair to mark the migration as deleted.
Detected applied migration not resolved locally: 3.0.9.
If you removed this migration intentionally, run repair to mark the migration as deleted.
Need more flexibility with validation rules? Learn more: https://rd.gt/3AbJUZE
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1786)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:600)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:522)
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:325)
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:323)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:312)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
at org.springframework.beans.factory.support.BeanDefinitionValueResolver.resolveReference(BeanDefinitionValueResolver.java:365)
... 95 common frames omitted
Caused by: org.flywaydb.core.api.exception.FlywayValidateException: Validate failed: Migrations have failed validation
Detected applied migration not resolved locally: 3.0.8.
If you removed this migration intentionally, run repair to mark the migration as deleted.
Detected applied migration not resolved locally: 3.0.9.2. After examining the flyway_schema_history from a fresh OSE 3.0 deployment, it did contain 34 rows; however, I was missing versions with their appending sql scripts. In addition, the ose-default.log was outputting an error of having the flyway schemas 3.0.8 + 3.0.9 installed.
3. This is the original flyway_schema_history table:
osedb=# select * from flyway_schema_history;
installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success
----------------+---------+----------------------------+------+----------------------------------------+-------------+--------------+----------------------------+----------------+---------
1 | 1.0 | init | SQL | V1_0__init.sql | -979785306 | oseadmin | 2022-03-16 03:42:17.035807 | 272 | t
2 | 1.0.1 | patch | SQL | V1_0_1__patch.sql | 426241508 | oseadmin | 2022-03-16 03:42:17.317109 | 50 | t
3 | 1.5 | update | SQL | V1_5__update.sql | -1596237536 | oseadmin | 2022-03-16 03:42:17.372252 | 51 | t
4 | 1.5.1 | drop unused table manually | SQL | V1_5_1__drop_unused_table_manually.sql | -142019797 | oseadmin | 2022-03-16 03:42:17.429113 | 4 | t
5 | 2 | update | SQL | V2__update.sql | -1109325960 | oseadmin | 2022-03-16 03:42:17.444768 | 20 | t
6 | 2.1 | update | SQL | V2_1__update.sql | 426886802 | oseadmin | 2022-03-16 03:42:17.470352 | 2 | t
7 | 2.2 | update | SQL | V2_2__update.sql | 1148803843 | oseadmin | 2022-03-16 03:42:17.483217 | 0 | t
8 | 2.3 | update | SQL | V2_3__update.sql | 1019116466 | oseadmin | 2022-03-16 03:42:17.493351 | 0 | t
9 | 2.4 | update | SQL | V2_4__update.sql | -1350150635 | oseadmin | 2022-03-16 03:42:17.497903 | 5 | t
10 | 2.5 | update | SQL | V2_5__update.sql | 1338832042 | oseadmin | 2022-03-16 03:42:17.515422 | 1 | t
11 | 2.6 | update | SQL | V2_6__update.sql | 465901593 | oseadmin | 2022-03-16 03:42:17.746666 | 226 | t
12 | 2.7 | update | SQL | V2_7__update.sql | -77358891 | oseadmin | 2022-03-16 03:42:17.749412 | 16 | t
13 | 3.0.1 | update | SQL | V3_0_1__update.sql | -1760350090 | oseadmin | 2022-03-16 03:42:17.781278 | 30 | t
14 | 3.0.2 | update | SQL | V3_0_2__update.sql | 1658949239 | oseadmin | 2022-03-16 03:42:17.821524 | 13 | t
15 | 3.0.3 | update | SQL | V3_0_3__update.sql | -1255348713 | oseadmin | 2022-03-16 03:42:17.837878 | 20 | t
16 | 3.0.4 | update | SQL | V3_0_4__update.sql | 1583545982 | oseadmin | 2022-03-16 03:42:17.862178 | 3 | t
17 | 3.0.5 | update | SQL | V3_0_5__update.sql | -1945985526 | oseadmin | 2022-03-16 03:42:17.868671 | 0 | t
18 | 3.0.6 | update | SQL | V3_0_6__update.sql | -1058746628 | oseadmin | 2022-03-16 03:42:17.871122 | 0 | t
19 | 3.0.7 | update | SQL | V3_0_7__update.sql | -1063655061 | oseadmin | 2022-03-16 03:42:17.87398 | 4 | t
20 | 3.0.8 | update | SQL | V3_0_8__update.sql | -488014536 | oseadmin | 2022-11-16 16:50:42.543539 | 196 | t
21 | 3.0.9 | update | SQL | V3_0_9__update.sql | -2041621292 | oseadmin | 2022-11-16 16:50:42.77285 | 2 | t
22 | 4.0.0 | update | SQL | V4_0_0__update.sql | 2008559861 | oseadmin | 2022-11-23 16:34:41.926001 | 353 | t
23 | 4.0.1 | update | SQL | V4_0_1__update.sql | 196836990 | oseadmin | 2022-11-23 16:34:42.32554 | 1 | t
24 | 4.0.2 | update | SQL | V4_0_2__update.sql | -877483916 | oseadmin | 2022-11-23 16:34:42.331749 | 1 | t
25 | 4.0.3 | update | SQL | V4_0_3__update.sql | -2092682561 | oseadmin | 2022-11-23 16:34:42.341433 | 1 | t
26 | 4.0.4 | update | SQL | V4_0_4__update.sql | -1404879614 | oseadmin | 2022-11-23 16:34:42.352854 | 19 | t
27 | 4.0.5 | update | SQL | V4_0_5__update.sql | -1988668194 | oseadmin | 2022-11-23 16:34:42.377971 | 0 | t
28 | 4.0.6 | update | SQL | V4_0_6__update.sql | -488014536 | oseadmin | 2022-11-23 16:34:42.382112 | 1 | t
29 | 4.0.7 | update | SQL | V4_0_7__update.sql | -1252102087 | oseadmin | 2022-11-23 16:34:42.394658 | 3 | t
30 | 4.0.8 | update | SQL | V4_0_8__update.sql | -1901761440 | oseadmin | 2022-11-23 16:34:42.399933 | 24 | t
31 | 4.0.9 | update | SQL | V4_0_9__update.sql | -56995991 | oseadmin | 2022-11-23 16:34:42.429215 | 17 | t
32 | 4.0.10 | update | SQL | V4_0_10__update.sql | 290927200 | oseadmin | 2023-03-20 10:43:30.213809 | 938 | t
33 | 4.2.2.0 | update | SQL | V4_2_2_0__update.sql | -790915939 | oseadmin | 2023-07-18 18:17:32.394706 | 643 | t
34 | 4.2.3.0 | update | SQL | V4_2_3_0__update.sql | 1321498512 | oseadmin | 2023-12-02 14:23:01.060987 | 471 | t
(34 rows)4. OSE 3.0 Fresh Deployment flyway_schema_history table:
select * from flyway_schema_history;
installed_rank | version | description | type | script | checksum | installed_by | installed_on | execution_time | success
----------------+---------+----------------------------+------+----------------------------------------+-------------+--------------+----------------------------+----------------+---------
1 | 1.0 | init | SQL | V1_0__init.sql | -979785306 | oseadmin | 2024-03-26 04:47:58.912557 | 60 | t
2 | 1.0.1 | patch | SQL | V1_0_1__patch.sql | 426241508 | oseadmin | 2024-03-26 04:47:59.025002 | 29 | t
3 | 1.5 | update | SQL | V1_5__update.sql | -1596237536 | oseadmin | 2024-03-26 04:47:59.076986 | 36 | t
4 | 1.5.1 | drop unused table manually | SQL | V1_5_1__drop_unused_table_manually.sql | -142019797 | oseadmin | 2024-03-26 04:47:59.138896 | 10 | t
5 | 2 | update | SQL | V2__update.sql | -1109325960 | oseadmin | 2024-03-26 04:47:59.172722 | 17 | t
6 | 2.1 | update | SQL | V2_1__update.sql | 426886802 | oseadmin | 2024-03-26 04:47:59.199745 | 11 | t
7 | 2.2 | update | SQL | V2_2__update.sql | 1148803843 | oseadmin | 2024-03-26 04:47:59.223373 | 8 | t
8 | 2.3 | update | SQL | V2_3__update.sql | 1019116466 | oseadmin | 2024-03-26 04:47:59.245146 | 13 | t
9 | 2.4 | update | SQL | V2_4__update.sql | -1350150635 | oseadmin | 2024-03-26 04:47:59.26809 | 12 | t
10 | 2.5 | update | SQL | V2_5__update.sql | 1338832042 | oseadmin | 2024-03-26 04:47:59.297626 | 9 | t
11 | 2.6 | update | SQL | V2_6__update.sql | 465901593 | oseadmin | 2024-03-26 04:47:59.452608 | 88 | t
12 | 2.7 | update | SQL | V2_7__update.sql | -77358891 | oseadmin | 2024-03-26 04:47:59.466958 | 22 | t
13 | 3.0.1 | update | SQL | V3_0_1__update.sql | -1760350090 | oseadmin | 2024-03-26 04:47:59.500179 | 26 | t
14 | 3.0.2 | update | SQL | V3_0_2__update.sql | 1658949239 | oseadmin | 2024-03-26 04:47:59.542564 | 6 | t
15 | 3.0.3 | update | SQL | V3_0_3__update.sql | -1255348713 | oseadmin | 2024-03-26 04:47:59.565646 | 12 | t
16 | 3.0.4 | update | SQL | V3_0_4__update.sql | 1583545982 | oseadmin | 2024-03-26 04:47:59.59195 | 17 | t
17 | 3.0.5 | update | SQL | V3_0_5__update.sql | -1945985526 | oseadmin | 2024-03-26 04:47:59.618609 | 7 | t
18 | 3.0.6 | update | SQL | V3_0_6__update.sql | -1058746628 | oseadmin | 2024-03-26 04:47:59.637253 | 8 | t
19 | 3.0.7 | update | SQL | V3_0_7__update.sql | -1063655061 | oseadmin | 2024-03-26 04:47:59.662158 | 7 | t
20 | 4.0.0 | update | SQL | V4_0_0__update.sql | 2008559861 | oseadmin | 2024-03-26 04:47:59.680207 | 11 | t
21 | 4.0.1 | update | SQL | V4_0_1__update.sql | 196836990 | oseadmin | 2024-03-26 04:47:59.710238 | 3 | t
22 | 4.0.2 | update | SQL | V4_0_2__update.sql | -877483916 | oseadmin | 2024-03-26 04:47:59.729755 | 9 | t
23 | 4.0.3 | update | SQL | V4_0_3__update.sql | -2092682561 | oseadmin | 2024-03-26 04:47:59.750049 | 4 | t
24 | 4.0.4 | update | SQL | V4_0_4__update.sql | -1404879614 | oseadmin | 2024-03-26 04:47:59.766234 | 1 | t
25 | 4.0.5 | update | SQL | V4_0_5__update.sql | -1988668194 | oseadmin | 2024-03-26 04:47:59.783771 | 5 | t
26 | 4.0.6 | update | SQL | V4_0_6__update.sql | -488014536 | oseadmin | 2024-03-26 04:47:59.79672 | 13 | t
27 | 4.0.7 | update | SQL | V4_0_7__update.sql | -1252102087 | oseadmin | 2024-03-26 04:47:59.816783 | 4 | t
28 | 4.0.8 | update | SQL | V4_0_8__update.sql | -1901761440 | oseadmin | 2024-03-26 04:47:59.831667 | 9 | t
29 | 4.0.9 | update | SQL | V4_0_9__update.sql | -56995991 | oseadmin | 2024-03-26 04:47:59.872243 | 20 | t
30 | 4.0.10 | update | SQL | V4_0_10__update.sql | 290927200 | oseadmin | 2024-03-26 04:47:59.909283 | 16 | t
31 | 4.2.2.0 | update | SQL | V4_2_2_0__update.sql | -790915939 | oseadmin | 2024-03-26 04:47:59.934108 | 7 | t
32 | 4.2.3.0 | update | SQL | V4_2_3_0__update.sql | 1321498512 | oseadmin | 2024-03-26 04:47:59.947421 | 12 | t
33 | 5.0.0.0 | update | SQL | V5_0_0_0__update.sql | 938724545 | oseadmin | 2024-03-26 04:47:59.971721 | 7 | t
34 | 5.0.0.1 | update | SQL | V5_0_0_1__update.sql | -1764790910 | oseadmin | 2024-03-26 04:47:59.981685 | 4 | t
(34 rows)5. I made sure to have a snapshot of the VM, I manipulated the database (I do not recommend to do this in a production environment):
INSERT INTO your_table_name (installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success)
VALUES
(33, '5.0.0.0', 'update', 'SQL', 'V5_0_0_0__update.sql', 938724545, 'oseadmin', '2024-03-26 04:47:59.971721', 7, true),
(34, '5.0.0.1', 'update', 'SQL', 'V5_0_0_1__update.sql', -1764790910, 'oseadmin', '2024-03-26 04:47:59.981685', 4, true);6. I deleted rows 20 +21 (To remove 3.0.8 + 3.0.9)
DELETE FROM flyway_schema_history WHERE installed_rank IN (20, 21);7. Then I faced an issue where the install rank was out of numerical order; therefore, I fixed the install rank order via:
-- Update installed_rank for rows with installed_rank greater than or equal to 20
UPDATE flyway_schema_history
SET installed_rank = installed_rank - 2
WHERE installed_rank >= 20;
-- Delete rows with installed_rank 35 and 36
DELETE FROM flyway_schema_history
WHERE installed_rank IN (35, 36);
- It's not required to
COMMITthe changes as when you execute SQL commands such asUPDATEorDELETE, the changes are immediately applied to the database. PostgreSQL handles transactions implicitly, meaning that each SQL command is treated as a transaction by default.
8. After executing the flyway_schema_history modifications, I was able to successfully start the ose service via:
ose service start¶ Column Change in OSE 3.0
1. After starting the OSE service, I noticed that the Security Credentials and Kubernetes Clusters for the tenant parsed an error in the OSE UI (I didn't capture a screenshot); however, for reference, these are the (2) tabs that were not working:
2. The error can be found in the ose-default.log
/opt/vmware/voss/log/ose-default.log
Caused by: org.postgresql.util.PSQLException: ERROR: column cpi1_0.cluster_name does not exist3. After working with our engineering team, they confirmed that there is new column of table cluster_protection_info, which is added in flyway 5.0.0
4. To resolve this issue, engineering provided a way to ALTER the cluster_protection_info table via:
ALTER TABLE cluster_protection_info ADD IF NOT EXISTS cluster_name VARCHAR(255);
- This resolved the issue!
5. Engineering also explained to me that there is a new DB schema change in OSE 3.0 for multi-part uploads:
ALTER TABLE multi_upload_info ADD IF NOT EXISTS storage_user_id VARCHAR(255);¶ Important Notes
1. Do not do this in a production environment, please use a sandbox or staging environment to test these changes.
2. Leverage snapshots!
3. For production environments please ensure to reach out to your VCSP CSA about migrating to a different OS with the same DB and OSE configuration.