Liquibase , , . .
, liquibase . , PostgreSQL, , , .
, Liquibase, . , , (changeset), (changelog), . .
, liquibase , XML. , . XML , SQL. Liquibase, , SQL, .
liquibase, - SQL, - , - . ? .
, , (, ). , , , ( ).
Linux shell. pgCodeKeeper, rollback sed.
, dbdev dbprod, dbdev , dbprod Liquibase.
/tmp migration , , . PostgreSQL , .
:
$ mkdir /tmp/migration
$ cd /tmp/migration/
$ createdb dbdev
$ createdb dbprod
JDBC PostgreSQL, Liquibase pgCodeKeeper
$ wget https://jdbc.postgresql.org/download/postgresql-42.1.3.jar
$ wget https://github.com/liquibase/liquibase/releases/download/liquibase-parent-3.4.2/liquibase-3.4.2-bin.tar.gz
$ wget http://pgcodekeeper.ru/cli/release/pgCodeKeeper-cli-3.11.4.201707170702.zip
liquibase pgcodekeeper , .
$ tar xzvf liquibase-3.4.2-bin.tar.gz
$ unzip pgCodeKeeper-cli-3.11.4.201707170702.zip
liquibase liquibase.properties :
driver: org.postgresql.Driver
classpath: ./postgresql-42.1.3.jar
url: jdbc:postgresql:dbprod
username: user
password: topsecret
changeLogFile: db.changelog.xml
, Liquibase, .. dbprod, . , ags . db.changelog.xml :
, liquibase :
$ ./liquibase status
ags@jdbc:postgresql:dbprod is up to date
Liquibase 'status' Successful
, , . , pgCodeKeeper.
$ ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod
. , , JDBC URL . :
jdbc.postgresql.org/documentation/head/connect.html
, , .
dbdev:
[ags@saushkin-ag:/tmp/migration] $ psql dbdev
psql (9.6.3, 9.5.7)
"help", .
(ags@[local]:5432) 16:08:43 [dbdev] =# create table users (id serial primary key, name text);
CREATE TABLE
: 20,708
(ags@[local]:5432) 16:09:16 [dbdev] * =# commit;
COMMIT
: 6,913
, pgCodeKeeper .
$ ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod
CREATE SEQUENCE users_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER SEQUENCE users_id_seq OWNER TO ags;
CREATE TABLE users (
id integer DEFAULT nextval('users_id_seq'::regclass) NOT NULL,
name text
);
ALTER TABLE users OWNER TO ags;
ALTER TABLE users
ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER SEQUENCE users_id_seq
OWNED BY users.id;
, . migrate.sh
#!/bin/bash
FILENAME=${1:-changeset.sql}
#
echo "--liquibase formatted sql" > $FILENAME
echo "--changeset $USER:$FILENAME" >> $FILENAME
echo "" >> $FILENAME
#
./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod >> $FILENAME
echo "" >> $FILENAME
# ( )
./pgcodekeeper-cli.sh jdbc:postgresql:dbprod jdbc:postgresql:dbdev | sed -e 's/^/--rollback /' >> $FILENAME
, , changeset.sql. SQL, Liquibase . .
:
./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod >> $FILENAME
, dbdev dbprod .
:
./pgcodekeeper-cli.sh jdbc:postgresql:dbprod jdbc:postgresql:dbdev | sed -e 's/^/--rollback /' >> $FILENAME
, , , . Liquibase --rollback.
, . , , /dev/stdout:
$ chmod +x ./migrate.sh
$ ./migrate.sh /dev/stdout
--liquibase formatted sql
--changeset ags:/dev/stdout
CREATE SEQUENCE users_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER SEQUENCE users_id_seq OWNER TO ags;
CREATE TABLE users (
id integer DEFAULT nextval('users_id_seq'::regclass) NOT NULL,
name text
);
ALTER TABLE users OWNER TO ags;
ALTER TABLE users
ADD CONSTRAINT users_pkey PRIMARY KEY (id);
ALTER SEQUENCE users_id_seq
OWNED BY users.id;
--rollback ALTER TABLE users
--rollback DROP CONSTRAINT users_pkey;
--rollback
--rollback DROP TABLE users;
--rollback
, , . SQL ! .
$ ./migrate.sh 001_users.sql
, db.changelog.xml, include 001_users.sql:
dbprod, , .
$ ./liquibase tag 001_before_users
Successfully tagged ags@jdbc:postgresql:dbprod
Liquibase 'tag' Successful
$ ./liquibase migrate
Liquibase Update Successful
$ ./liquibase status
ags@jdbc:postgresql:dbprod is up to date
Liquibase 'status' Successful
$ ./liquibase rollback 001_before_users
Liquibase Rollback Successful
$ ./liquibase status
1 change sets have not been applied to ags@jdbc:postgresql:dbprod
Liquibase 'status' Successful
$ ./liquibase migrate
Liquibase Update Successful
, ? , dbdev dbprod . ?
$ ./pgcodekeeper-cli.sh jdbc:postgresql:dbdev jdbc:postgresql:dbprod
ALTER TABLE databasechangeloglock
DROP CONSTRAINT pk_databasechangeloglock;
DROP TABLE databasechangeloglock;
DROP TABLE databasechangelog;
pgCodeKeeper , dbdev databasechangeloglock databasechangelog. liquibase . liquibase . pgCodeKeeper.
, .pgcodekeeperignore :
$ cat .pgcodekeeperignore
SHOW ALL
HIDE REGEX "databasechangelog.*"
,
$ ./pgcodekeeper-cli.sh -I .pgcodekeeperignore jdbc:postgresql:dbdev jdbc:postgresql:dbprod
.
, , :
-
-
-
- (, )
- ,
Liquibase . SQL , . , .
PS. Liquibase
www.liquibase.org/documentation/diff.html, , , , pgCodeKeeper .
PPS. pgCodeKeeper 100 , , ( . Telegram
pgcodekeeper.ru ) .
https://habrahabr.ru/post/333762/
