refactored functionality to the migrate.sh for automation
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/commit/90eb3287 Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/tree/90eb3287 Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/diff/90eb3287 Branch: refs/heads/master Commit: 90eb3287f498651e7246e47ac515e81e35fadd63 Parents: 41e6539 Author: Dewayne Richardson <dewr...@apache.org> Authored: Thu Jan 26 14:43:47 2017 -0700 Committer: Dan Kirkwood <dang...@gmail.com> Committed: Thu Jan 26 20:59:43 2017 -0700 ---------------------------------------------------------------------- .../app/db/pg-migration/Dockerfile-convert | 8 +- .../app/db/pg-migration/Dockerfile-mysql | 7 ++ .../pg-migration/Dockerfile-mysql-to-postgres | 38 +++++++++ .../app/db/pg-migration/Dockerfile-pgloader | 3 - .../app/db/pg-migration/Dockerfile-postgres | 24 ------ .../pg-migration/Dockerfile-traffic_ops-client | 27 ------ traffic_ops/app/db/pg-migration/README.md | 13 +-- traffic_ops/app/db/pg-migration/convert.yml | 2 +- traffic_ops/app/db/pg-migration/dataimport.env | 17 ---- traffic_ops/app/db/pg-migration/dataimport.yml | 26 ------ .../app/db/pg-migration/docker-compose.yml | 42 --------- traffic_ops/app/db/pg-migration/get-to-data.sh | 38 --------- traffic_ops/app/db/pg-migration/migrate.sh | 90 ++++++++++++++++++++ .../app/db/pg-migration/mysql-to-postgres.env | 43 ++++++++++ .../app/db/pg-migration/mysql-to-postgres.sh | 59 +++++++++++++ .../app/db/pg-migration/mysql-to-postgres.yml | 24 ++++++ traffic_ops/app/db/pg-migration/mysql.env | 20 ----- traffic_ops/app/db/pg-migration/mysql_host.yml | 4 +- traffic_ops/app/db/pg-migration/pgloader.yml | 3 +- traffic_ops/app/db/pg-migration/pgmigration.yml | 56 ------------ traffic_ops/app/db/pg-migration/postgres.env | 21 ----- .../app/db/pg-migration/postgres_host.yml | 26 ------ traffic_ops/app/db/pg-migration/runconvert.sh | 2 +- traffic_ops/app/db/pg-migration/runmysql.sh | 11 --- traffic_ops/app/db/pg-migration/runpgloader.sh | 13 +-- 25 files changed, 279 insertions(+), 338 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/Dockerfile-convert ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/Dockerfile-convert b/traffic_ops/app/db/pg-migration/Dockerfile-convert index 4f1223d..941912f 100644 --- a/traffic_ops/app/db/pg-migration/Dockerfile-convert +++ b/traffic_ops/app/db/pg-migration/Dockerfile-convert @@ -15,7 +15,13 @@ FROM dewrich/postgres:latest MAINTAINER Dan Kirkwood -RUN apt-get update && apt-get -y install netcat +# Postgres Access +ENV POSTGRES_HOST=$POSTGRES_HOST +ENV POSTGRES_PORT=$POSTGRES_PORT +ENV POSTGRES_DATABASE=$POSTGRES_DATABASE +ENV POSTGRES_PASSWORD=$POSTGRES_PASSWORD + +RUN apt-get update ADD pg-migration/runconvert.sh . ADD convert_bools.sql . http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/Dockerfile-mysql ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/Dockerfile-mysql b/traffic_ops/app/db/pg-migration/Dockerfile-mysql index a4bf51f..0b3d0df 100644 --- a/traffic_ops/app/db/pg-migration/Dockerfile-mysql +++ b/traffic_ops/app/db/pg-migration/Dockerfile-mysql @@ -15,5 +15,12 @@ FROM mysql:5.6 MAINTAINER Dan Kirkwood +# Mysql Access +ENV MYSQL_HOST=$MYSQL_HOST +ENV MYSQL_PORT=$MYSQL_PORT +ENV MYSQL_PASSWORD=$MYSQL_PASSWORD +ENV MYSQL_DATABASE=$MYSQL_DATABASE + +EXPOSE 3306 ADD runmysql.sh / CMD /runmysql.sh http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/Dockerfile-mysql-to-postgres ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/Dockerfile-mysql-to-postgres b/traffic_ops/app/db/pg-migration/Dockerfile-mysql-to-postgres new file mode 100644 index 0000000..b1da689 --- /dev/null +++ b/traffic_ops/app/db/pg-migration/Dockerfile-mysql-to-postgres @@ -0,0 +1,38 @@ +# +# Licensed under the Apache License, Version 2.0 (the "License"); +# you may not use this file except in compliance with the License. +# You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# +FROM dimitri/pgloader:latest +MAINTAINER Dan Kirkwood <dang...@apache.org> + +# Traffic Ops Access +ENV TO_SERVER=$TO_SERVER +ENV TO_USER=$TO_USER +ENV TO_PASSWORD=$TO_PASSWORD + +# Mysql Access +ENV MYSQL_HOST=$MYSQL_HOST +ENV MYSQL_PORT=$MYSQL_PORT +ENV MYSQL_PASSWORD=$MYSQL_PASSWORD +ENV MYSQL_DATABASE=$MYSQL_DATABASE + +# Postgres Access +ENV POSTGRES_HOST=$POSTGRES_HOST +ENV POSTGRES_PORT=$POSTGRES_PORT +ENV POSTGRES_DATABASE=$POSTGRES_DATABASE +ENV POSTGRES_PASSWORD=$POSTGRES_PASSWORD + +# install the tools for getting data +RUN apt-get -y update && apt-get -y install curl mysql-client + +ADD mysql-to-postgres.sh . +CMD ./mysql-to-postgres.sh http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/Dockerfile-pgloader ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/Dockerfile-pgloader b/traffic_ops/app/db/pg-migration/Dockerfile-pgloader index 35a4f4e..01bc9a4 100644 --- a/traffic_ops/app/db/pg-migration/Dockerfile-pgloader +++ b/traffic_ops/app/db/pg-migration/Dockerfile-pgloader @@ -14,8 +14,5 @@ FROM dimitri/pgloader:latest MAINTAINER Dan Kirkwood <dang...@apache.org> -# install nc to check that postgres and mysql are ready.. -RUN apt-get -y install netcat - ADD pg-migration/runpgloader.sh . CMD ./runpgloader.sh http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/Dockerfile-postgres ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/Dockerfile-postgres b/traffic_ops/app/db/pg-migration/Dockerfile-postgres deleted file mode 100644 index 1279b50..0000000 --- a/traffic_ops/app/db/pg-migration/Dockerfile-postgres +++ /dev/null @@ -1,24 +0,0 @@ -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -FROM dewrich/postgres:latest - -MAINTAINER Dan Kirkwood - -# Adjust PostgreSQL configuration so that remote connections to the -# database are possible. -RUN echo "host all all 0.0.0.0/0 md5" >> $PGDATA/pg_hba.conf -RUN echo "listen_addresses='*'" >> $PGDATA/postgresql.conf -COPY create_tables.sql /docker-entrypoint-initdb.d/. - -EXPOSE "5432:5432" http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/Dockerfile-traffic_ops-client ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/Dockerfile-traffic_ops-client b/traffic_ops/app/db/pg-migration/Dockerfile-traffic_ops-client deleted file mode 100644 index ebef652..0000000 --- a/traffic_ops/app/db/pg-migration/Dockerfile-traffic_ops-client +++ /dev/null @@ -1,27 +0,0 @@ -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -FROM debian -MAINTAINER Dan Kirkwood <dang...@apache.org> - -# install nc to check that postgres and mysql are ready.. -RUN apt-get -y update && apt-get -y install curl - -ENV TO_SERVER=$TO_SERVER -ENV TO_USER=$TO_USER -ENV TO_PASSWORD=$TO_PASSWORD - -ADD get-to-data.sh / - -# get data, trigger mysql startup -CMD /get-to-data.sh /docker-entrypoint-initdb.d/traffic_ops.sql http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/README.md ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/README.md b/traffic_ops/app/db/pg-migration/README.md index f565291..c02251b 100644 --- a/traffic_ops/app/db/pg-migration/README.md +++ b/traffic_ops/app/db/pg-migration/README.md @@ -1,17 +1,10 @@ -# Converting existing mysql `traffic_ops` database to postgres +# Converts existing mysql `traffic_ops` database to postgres * Requires a recent ( 1.12 ) version of `docker-engine` and `docker-compose`. -* Modify the mysql.env for your existing Mysql Database - -* Modify the postgres.env for your new Postgres Database - (NOTE: do not set the POSTGRES_HOST to 'localhost' it needs to be the IP address or DNS available hostname so that the container can reach out to Postgres) - +* Modify the mysql-to-postgres.env file for the parameters in your Migration * Ensure that your new Postgres service is running (local or remote) * Run the Mysql to Postgres Migration Docker flow - * `$ docker-compose -f pgmigration.yml down -v && docker-compose -f pgmigration.yml build && TO_SERVER=https://traffic_ops.kabletown.com TO_USER=me TO_PASSWORD='my!passwd' docker-compose -f pgmigration.yml up` - -* Run the Postgres datatype conversion - * `$ docker-compose -f convert.yml up` + * `sh migrate.sh http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/convert.yml ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/convert.yml b/traffic_ops/app/db/pg-migration/convert.yml index ab547e1..fb130e9 100644 --- a/traffic_ops/app/db/pg-migration/convert.yml +++ b/traffic_ops/app/db/pg-migration/convert.yml @@ -20,4 +20,4 @@ services: context: .. dockerfile: pg-migration/Dockerfile-convert env_file: - - postgres.env + - mysql-to-postgres.env http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/dataimport.env ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/dataimport.env b/traffic_ops/app/db/pg-migration/dataimport.env deleted file mode 100644 index 3b41e0d..0000000 --- a/traffic_ops/app/db/pg-migration/dataimport.env +++ /dev/null @@ -1,17 +0,0 @@ -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -TO_SERVER -TO_USER -TO_PASSWORD - http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/dataimport.yml ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/dataimport.yml b/traffic_ops/app/db/pg-migration/dataimport.yml deleted file mode 100644 index 1087efa..0000000 --- a/traffic_ops/app/db/pg-migration/dataimport.yml +++ /dev/null @@ -1,26 +0,0 @@ -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -version: '2' - -services: - # dataimport reads data from an existing traffic_ops server running mysql thru the API - dataimport: - build: - context: . - dockerfile: Dockerfile-traffic_ops-client - restart: "no" - environment: - - TO_USER - - TO_PASSWORD - - TO_SERVER http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/docker-compose.yml ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/docker-compose.yml b/traffic_ops/app/db/pg-migration/docker-compose.yml deleted file mode 100644 index 31145a4..0000000 --- a/traffic_ops/app/db/pg-migration/docker-compose.yml +++ /dev/null @@ -1,42 +0,0 @@ -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -version: '2' - -services: - db: - build: - context: .. - dockerfile: pg-migration/Dockerfile-postgres - environment: - - PGDATA=/opt/postgresql/data/pgdata - - POSTGRES_DB=traffic_ops - - POSTGRES_PASSWORD=twelve - - POSTGRES_USER=traffic_ops - env_file: - - postgres.env - ports: - - "5432:5432" - volumes: - - /opt/postgresql/data/pgdata:/opt/postgresql/data/pgdata - - /var/log/postgresql:/var/log/postgresql - networks: - - default - -networks: - default: - driver: bridge - -volumes: - pgdata: - http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/get-to-data.sh ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/get-to-data.sh b/traffic_ops/app/db/pg-migration/get-to-data.sh deleted file mode 100755 index 5eb801c..0000000 --- a/traffic_ops/app/db/pg-migration/get-to-data.sh +++ /dev/null @@ -1,38 +0,0 @@ -#!/bin/bash -x -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# - -output=$1 -[[ -n $output ]] && output="-o $output" - - -cookiejar=/tmp/cookiejar -cred=/tmp/cred.json - -cat >$cred <<-CREDS - { "u" : "$TO_USER", "p" : "$TO_PASSWORD" } -CREDS - -curl -f -k -H "Accept: application/json" --cookie "$cookiejar" --cookie-jar "$cookiejar" -X POST --data @"$cred" "$TO_SERVER/api/1.2/user/login" || exit 1 -curl $output -f -k -s --cookie "$cookiejar" -X GET "$TO_SERVER/dbdump" || exit 1 - -waiting=/sync/waiting-for-dataimport -while [[ ! -f $waiting ]]; do - # wait for signal that other container is waiting - echo "Data import finished.." - sleep 3 -done - -# signal to waiting container that we're finished -rm $waiting http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/migrate.sh ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/migrate.sh b/traffic_ops/app/db/pg-migration/migrate.sh new file mode 100755 index 0000000..3cec4d2 --- /dev/null +++ b/traffic_ops/app/db/pg-migration/migrate.sh @@ -0,0 +1,90 @@ +#!/bin/bash +# +# Licensed under the Apache License, Version 2.0 (the "License"); +# you may not use this file except in compliance with the License. +# You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# + +: ${TO_SERVER?"Please the TO_SERVER environment variable: ie: https://kabletown.net"} +: ${TO_USER?"Please the TO_USER environment variable: ie: <your Traffic Ops userid>"} +: ${TO_PASSWORD?"Please the TO_PASSWORD environment variable: ie: <your Traffic Ops password>"} + +MYSQL_PORT=3306 +POSTGRES_PORT=5432 + +separator="---------------------------------------" + +function shutdown_trafficops_database() { + sudo systemctl stop trafficops-db +} + +function start_staging_mysql_server() { + docker-compose -p trafficops -f mysql_host.yml up --build -d + while [[ ! `netstat -lnt | grep :$MYSQL_PORT` ]]; do + # wait for signal that other container is waiting + echo "Waiting for Mysql to Start..." + sleep 3 + done + echo $separator + echo "Mysql Host is started..." + echo $separator +} + +function start_staging_postgres_server() { + sudo systemctl start trafficops-db + while [[ ! `netstat -lnt | grep :$POSTGRES_PORT` ]]; do + # wait for signal that other container is waiting + echo "Waiting for Postgres to Start..." + sleep 3 + done + echo $separator + echo "Postgres started.." + echo $separator +} + + +function run_postgres_datatypes_conversion() { + echo $separator + echo "Starting Mysql to Postgres Migration..." + echo $separator + docker-compose -p trafficops -f convert.yml up --build +} + + +function migrate_data_from_mysql_to_postgres() { + echo $separator + echo "Starting Mysql to Postgres Migration..." + echo $separator + docker-compose -p trafficops -f mysql-to-postgres.yml up --build +} + +function clean() { + echo $separator + echo "Cleaning up..." + echo $separator + docker kill trafficops_mysql_host_1 + docker-compose -p trafficops -f mysql-to-postgres.yml down --remove-orphans + docker-compose -p trafficops -f convert.yml down --remove-orphans + docker rm trafficops_mysql-to-postgres_1 + docker rm trafficops_convert_1 + docker rm trafficops_mysql_host_1 + docker rmi trafficops_mysql-to-postgres + docker rmi trafficops_convert + docker rmi trafficops_mysql_host + docker rmi mysql:5.6 + docker rmi dimitri/pgloader:latest +} + +start_staging_mysql_server +start_staging_postgres_server +migrate_data_from_mysql_to_postgres +run_postgres_datatypes_conversion +clean http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/mysql-to-postgres.env ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/mysql-to-postgres.env b/traffic_ops/app/db/pg-migration/mysql-to-postgres.env new file mode 100644 index 0000000..5c3644b --- /dev/null +++ b/traffic_ops/app/db/pg-migration/mysql-to-postgres.env @@ -0,0 +1,43 @@ +#!/bin/bash +# +# Licensed under the Apache License, Version 2.0 (the "License"); +# you may not use this file except in compliance with the License. +# You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# + +#Traffic Ops Settings +# The following configs should be configured to point to the +# Traffic Ops instances that is connected to the MySQL that +# you want to convert +TO_SERVER=to_url +TO_USER=(your_user) +TO_PASSWORD=(your_password) + +# Staging Mysql +# These variables are for the "staging" Docker MySQL instance that is used +# to load the Traffic Ops "dbdump" into. ONLY change the MYSQL_HOST variable +# Leave the other variables. +MYSQL_HOST=( the_ip_address_running_this_migration ) +MYSQL_PORT=3306 +MYSQL_DATABASE=traffic_ops_db +MYSQL_USER=to_user +MYSQL_PASSWORD=twelve +MYSQL_RANDOM_ROOT_PASSWORD=yes + +# The following +# New Producion Postgres Settings, these variables should be changed for the Postgres +# instance that will contain the existing MySQL data converted +POSTGRES_HOST=( the_ip_address_running_this_migration ) +POSTGRES_PORT=5432 +# NOTE: The new database name for Traffic Ops +POSTGRES_DATABASE=traffic_ops +POSTGRES_USER=traffic_ops +POSTGRES_PASSWORD=( your_new_postgres_password ) http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/mysql-to-postgres.sh ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/mysql-to-postgres.sh b/traffic_ops/app/db/pg-migration/mysql-to-postgres.sh new file mode 100755 index 0000000..f738810 --- /dev/null +++ b/traffic_ops/app/db/pg-migration/mysql-to-postgres.sh @@ -0,0 +1,59 @@ +#!/bin/bash +# +# Licensed under the Apache License, Version 2.0 (the "License"); +# you may not use this file except in compliance with the License. +# You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# +# ------------------------------------------------------ + +echo "Dumping Data from Traffic Ops Instance: $TO_SERVER" +output=/tmp/trafficops_init.sql +[[ -n $output ]] && output="-o $output" + +cookiejar=/tmp/cookiejar +cred=/tmp/cred.json + +echo "mig:MYSQL_HOST: $MYSQL_HOST" +echo "mig:MYSQL_USER: $MYSQL_USER" +echo "mig:MYSQL_PASSWORD: $MYSQL_PASSWORD" +echo "mig:MYSQL_DATABASE: $MYSQL_DATABASE" + +echo "mig:POSTGRES_HOST: $POSTGRES_HOST" +echo "mig:POSTGRES_USER: $POSTGRES_USER" +echo "mig:POSTGRES_DATABASE: $POSTGRES_DATABASE" +echo "mig:POSTGRES_PASSWORD: $POSTGRES_PASSWORD" + +cat >$cred <<-CREDS + { "u" : "$TO_USER", "p" : "$TO_PASSWORD" } +CREDS + +curl -f -k -H "Accept: application/json" --cookie "$cookiejar" --cookie-jar "$cookiejar" -X POST --data @"$cred" "$TO_SERVER/api/1.2/user/login" || exit 1 +curl $output -f -k -s --cookie "$cookiejar" -X GET "$TO_SERVER/dbdump" || exit 1 + +echo "[client]" > /root/.my.cnf +echo "user=$MYSQL_USER" >> /root/.my.cnf +echo "password=$MYSQL_PASSWORD" >> /root/.my.cnf +chmod 0600 /root/.my.cnf +mysql -h $MYSQL_HOST $MYSQL_DATABASE < /tmp/trafficops_init.sql + +pgloader -v \ + --cast 'type tinyint to smallint drop typemod' \ + --cast 'type varchar to text drop typemod' \ + --cast 'type double to numeric drop typemod' \ + mysql://$MYSQL_USER:$MYSQL_PASSWORD@$MYSQL_HOST/$MYSQL_DATABASE \ + postgresql://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST/$POSTGRES_DATABASE + +# For debugging +#while true; do +# echo "Waiting.." +# sleep 3 +#done + http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/mysql-to-postgres.yml ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/mysql-to-postgres.yml b/traffic_ops/app/db/pg-migration/mysql-to-postgres.yml new file mode 100644 index 0000000..b5c99df --- /dev/null +++ b/traffic_ops/app/db/pg-migration/mysql-to-postgres.yml @@ -0,0 +1,24 @@ +# +# Licensed under the Apache License, Version 2.0 (the "License"); +# you may not use this file except in compliance with the License. +# You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. +# +version: '2' + +services: + # dataimport reads data from an existing traffic_ops server running mysql thru the API + mysql-to-postgres: + build: + context: . + dockerfile: Dockerfile-mysql-to-postgres + restart: "no" + env_file: + - mysql-to-postgres.env http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/mysql.env ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/mysql.env b/traffic_ops/app/db/pg-migration/mysql.env deleted file mode 100644 index ead4c15..0000000 --- a/traffic_ops/app/db/pg-migration/mysql.env +++ /dev/null @@ -1,20 +0,0 @@ -#!/bin/bash -x -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# - -MYSQL_DATABASE=traffic_ops_db -MYSQL_HOST=mysql -MYSQL_RANDOM_ROOT_PASSWORD=yes -MYSQL_PASSWORD=twelve -MYSQL_USER=to_user http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/mysql_host.yml ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/mysql_host.yml b/traffic_ops/app/db/pg-migration/mysql_host.yml index 59d0b9b..fb02e3b 100644 --- a/traffic_ops/app/db/pg-migration/mysql_host.yml +++ b/traffic_ops/app/db/pg-migration/mysql_host.yml @@ -19,4 +19,6 @@ services: context: . dockerfile: Dockerfile-mysql env_file: - - mysql.env + - mysql-to-postgres.env + ports: + - "3306:3306" http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/pgloader.yml ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/pgloader.yml b/traffic_ops/app/db/pg-migration/pgloader.yml index 23329a7..d9e3149 100644 --- a/traffic_ops/app/db/pg-migration/pgloader.yml +++ b/traffic_ops/app/db/pg-migration/pgloader.yml @@ -20,5 +20,4 @@ services: context: .. dockerfile: pg-migration/Dockerfile-pgloader env_file: - - mysql.env - - postgres.env + - mysql-to-postgres.env http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/pgmigration.yml ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/pgmigration.yml b/traffic_ops/app/db/pg-migration/pgmigration.yml deleted file mode 100644 index 536ea53..0000000 --- a/traffic_ops/app/db/pg-migration/pgmigration.yml +++ /dev/null @@ -1,56 +0,0 @@ -# Ansible managed -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -version: '2' - -volumes: - sync: - pgdata: - -networks: - default: - driver: bridge - -services: - # dataimport reads data from an existing traffic_ops server running mysql thru the API - dataimport: - extends: - service: dataimport - file: dataimport.yml - volumes: - - sync:/sync - - ./mysql/initdb.d:/docker-entrypoint-initdb.d - - # mysql_host loads mysql data locally and provides direct access for pgloader - mysql: - extends: - service: mysql_host - file: mysql_host.yml - depends_on: - - dataimport - volumes: - - sync:/sync - - ./mysql/conf.d:/etc/mysql/conf.d - - ./mysql/initdb.d:/docker-entrypoint-initdb.d - - # pgloader converts the data from mysql to postgres - pgloader: - extends: - service: pgloader - file: pgloader.yml - depends_on: - - mysql - volumes: - - sync:/sync - http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/postgres.env ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/postgres.env b/traffic_ops/app/db/pg-migration/postgres.env deleted file mode 100644 index a759b89..0000000 --- a/traffic_ops/app/db/pg-migration/postgres.env +++ /dev/null @@ -1,21 +0,0 @@ -#!/bin/bash -x -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# - -POSTGRES_HOST=localhost -POSTGRES_DB=traffic_ops -POSTGRES_PASSWORD=twelve -POSTGRES_USER=traffic_ops -POSTGRES_USER_UID=9999 -POSTGRES_USER_GID=9999 http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/postgres_host.yml ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/postgres_host.yml b/traffic_ops/app/db/pg-migration/postgres_host.yml deleted file mode 100644 index f8e0439..0000000 --- a/traffic_ops/app/db/pg-migration/postgres_host.yml +++ /dev/null @@ -1,26 +0,0 @@ -# -# Licensed under the Apache License, Version 2.0 (the "License"); -# you may not use this file except in compliance with the License. -# You may obtain a copy of the License at -# -# http://www.apache.org/licenses/LICENSE-2.0 -# -# Unless required by applicable law or agreed to in writing, software -# distributed under the License is distributed on an "AS IS" BASIS, -# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -# See the License for the specific language governing permissions and -# limitations under the License. -# -version: '2' - -services: - postgres_host: - build: - context: .. - dockerfile: pg-migration/Dockerfile-postgres - environment: - - POSTGRES_DB=traffic_ops - - POSTGRES_PASSWORD=twelve - - POSTGRES_USER=traffic_ops - ports: - - 5432 http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/runconvert.sh ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/runconvert.sh b/traffic_ops/app/db/pg-migration/runconvert.sh index 60cf16c..59b1afb 100755 --- a/traffic_ops/app/db/pg-migration/runconvert.sh +++ b/traffic_ops/app/db/pg-migration/runconvert.sh @@ -12,4 +12,4 @@ # See the License for the specific language governing permissions and # limitations under the License. # Load required conversion of booleans -psql postgresql://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST/$POSTGRES_DB < ./convert_bools.sql +psql postgresql://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST/$POSTGRES_DATABASE < ./convert_bools.sql http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/runmysql.sh ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/runmysql.sh b/traffic_ops/app/db/pg-migration/runmysql.sh index 58185f7..0c29dee 100755 --- a/traffic_ops/app/db/pg-migration/runmysql.sh +++ b/traffic_ops/app/db/pg-migration/runmysql.sh @@ -13,15 +13,4 @@ # limitations under the License. # -waiting=/sync/waiting-for-dataimport -touch $waiting - -# Wait for pgloader to finish -while [[ -f $waiting ]]; do - ls -l $waiting - sleep 3 -done - -echo "Looks like dataimport is finished.. Starting mysql..." - docker-entrypoint.sh mysqld http://git-wip-us.apache.org/repos/asf/incubator-trafficcontrol/blob/90eb3287/traffic_ops/app/db/pg-migration/runpgloader.sh ---------------------------------------------------------------------- diff --git a/traffic_ops/app/db/pg-migration/runpgloader.sh b/traffic_ops/app/db/pg-migration/runpgloader.sh index b56cb55..ac0acd4 100755 --- a/traffic_ops/app/db/pg-migration/runpgloader.sh +++ b/traffic_ops/app/db/pg-migration/runpgloader.sh @@ -13,20 +13,11 @@ # limitations under the License. # -# make sure postgres and mysql ports are both active -echo "POSTGRES_HOST=$POSTGRES_HOST MYSQL_HOST=$MYSQL_HOST" -for c in "$POSTGRES_HOST 5432" "$MYSQL_HOST 3306"; do - while true; do - echo Waiting for $c - sleep 3 - nc -z $c && break - done -done - pgloader -v \ --cast 'type tinyint to smallint drop typemod' \ --cast 'type varchar to text drop typemod' \ --cast 'type double to numeric drop typemod' \ + --with truncate \ mysql://$MYSQL_USER:$MYSQL_PASSWORD@$MYSQL_HOST/traffic_ops_db \ - postgresql://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST/$POSTGRES_DB + postgresql://$POSTGRES_USER:$POSTGRES_PASSWORD@$POSTGRES_HOST/$POSTGRES_DATABASE exit 0