Differences

This shows you the differences between two versions of the page.

--- database:liquibase [2019/07/09 13:54] – skipidar
+++ database:liquibase [2023/11/01 07:13] (current) – ↷ Page moved from business_process_management:camunda:database:liquibase to database:liquibase skipidar
@@ Line 8: / Line 8: @@
 </code>
-== how it works==
+=== how it works===
 Liquibase stores all metadata in 2 tables:
 <code>
@@ Line 16: / Line 16: @@
-==Syntax of the call==
+===Syntax of the call===
+== H2 ==
   * There is the syntax of the liquibase call.
   * The Driver name: org.h2.Driver
@@ Line 23: / Line 26: @@
 liquibase --driver=org.h2.Driver --classpath="c:\doj\materials\Liquibase\h2jar\h2-1.4.197.jar"  --changeLogFile=../changelogs/extend.user.changelog.liquibase.xml --url="jdbc:h2:tcp://localhost:9092/mem:dbname" --username=mydbuser --password=mydbpass migrate
 </code>
+== Postgres ==
+<code>
+wget -O liquibase.tar.gz https://github.com/liquibase/liquibase/releases/download/v3.9.0/liquibase-3.9.0.tar.gz
+tar -xzvf liquibase.tar.gz
+wget -O postgresql-jdbc4.jar https://jdbc.postgresql.org/download/postgresql-42.2.5.jre6.jar
+chmod +x postgresql-jdbc4.jar
+./liquibase --driver=org.postgresql.Driver --classpath="/tmp/liquibase/postgresql-jdbc4.jar"  --changeLogFile=dbchangelog.xml --url="jdbc:postgresql://localhost:5432/postgres" --username=postgres --password=postgres generateChangeLog
+</code>
 == Example changeset==
@@ Line 42: / Line 61: @@
     </changeSet>
-    <changeSet author="alf" id="sqlfile-usernames-add-nicknames">
+    <changeSet author="alf" id="sqlfile-usernames-add-nicknames" logicalFilePath="sqlfile-usernames-add-nicknames">
         <sqlFile dbms="h2, oracle"
                 encoding="utf8"
@@ Line 82: / Line 101: @@
 https://www.liquibase.org/documentation/databasechangelog.html
 </code>
+== Debug ==
+<code>
+# create a tunnel to the STAGE ssh jump server and the postgres db on STAGE
+sudo ssh -i /home/vagrant/.ssh/ssh.openssh.priv -NL 9000:privatedbdns.com:5432 myuser@11.222.33.111 -v
+# connect the postgres database
+psql -h localhost -p 9000 -U MyAdmin --dbname my_db
+# PSQL password:
+thepassword
+# list schemas
+select schema_name
+from information_schema.schemata;
+# list tables
+\dt *.*
+# show the liquibase history
+SELECT * FROM databasechangelog;
+# remove the liquibase history
+DELETE FROM databasechangelog;
+# MANUAL creation of the schema. DONT DO THAT
+CREATE SCHEMA my_db;
+# MANUAL REMOVAL of the schema. DONT DO THAT!!!!!!!!!!!!!!!!!!!
+DROP SCHEMA my_db CASCADE;
+</code>
+=== With Spring Boot ===
+== INit the DB at start ==
+To init the table - you only need the core **org.liquibase:liquibase-core** (no plugin)
+build.gradle
+<sxh perl>
+dependencies {
+    // liquibase
+    compile 'org.liquibase:liquibase-core:3.9.0'
+    // get the to the classpath which is needed but not loaded with "ddl-auto=none" and liquibase enabled
+    compile group: 'com.fasterxml.jackson.module', name: 'jackson-module-jaxb-annotations', version: '2.11.0'
+}
+</sxh>
+application.properties
+<sxh perl>
+# https://docs.spring.io/spring-boot/docs/current/reference/html/appendix-application-properties.html
+spring.datasource.driver-class-name=org.postgresql.Driver
+spring.datasource.username=postgres
+spring.datasource.password=postgres
+spring.datasource.host=localhost
+spring.datasource.port=5432
+spring.datasource.database=postgres
+spring.datasource.url=jdbc:postgresql://${spring.datasource.host}:${spring.datasource.port}/${spring.datasource.database}
+# Liquibase without prefix
+#spring.liquibase.enabled=false
+spring.liquibase.enabled=true
+spring.liquibase.change-log=classpath:/db/changelog/db.changelog-master.yaml
+spring.liquibase.default-schema=public
+spring.liquibase.user=postgres
+spring.liquibase.password=postgres
+# will be taken from spring.*  like spring.datasource.host
+#spring.liquibase.host=localhost
+#spring.liquibase.port=5432
+#spring.liquibase.database=postgres
+#spring.liquibase.driver=org.postgresql.Driver
+</sxh>
+resources/db/changelog/db.changelog-master.yaml
+<code>
+databaseChangeLog:
+  - includeAll:
+      path: db/changelog/generated/
+</code>
+resources/db/changelog/generated/dbchangelog-1589882779384.xml
+<code>
+<?xml version="1.1" encoding="UTF-8" standalone="no"?>
+<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-3.9.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
+    <changeSet author="vagrant (generated)" id="1589882779384-1">
+        <createTable tableName="tags">
+            <column name="name" type="VARCHAR(255)">
+                <constraints nullable="false" primaryKey="true" primaryKeyName="tags_pkey"/>
+            </column>
+        </createTable>
+    </changeSet>
+    ...
+</code>