227 lines
8.2 KiB
Bash
227 lines
8.2 KiB
Bash
#!/bin/bash
|
|
|
|
#title :upgrade_postgres.sh
|
|
#description :
|
|
#author :max.rieck@metasfresh.com
|
|
#date :2022-11-29
|
|
#usage :nohup bash ./upgrade_postgres.sh
|
|
#logs :tail -f nohup.out
|
|
#
|
|
# Prerequisites: Enough storage space to store a second db-volume
|
|
#
|
|
# unknown data-types aren't supported anymore, they should be altered before upgrade
|
|
# SELECT *
|
|
# FROM information_schema.columns
|
|
# WHERE data_type ILIKE 'unknown';
|
|
#
|
|
# before starting set POSTGRES_PASSWORD in docker-compose-before-upgrade-postgres.yml
|
|
# before starting set POSTGRES_PASSWORD in docker-compose-upgrade-postgres.yml
|
|
# before starting set POSTGRES_PASSWORD in docker-compose-after-upgrade-postgres.yml
|
|
#
|
|
# ATTENTION:
|
|
# this script generates and executes SQL to alternate Tables with oids
|
|
#======================================================================================================
|
|
|
|
start=$(date +%s)
|
|
|
|
instance=$(basename "${PWD%/*}")
|
|
|
|
echo "stopping metasfresh instance $instance"
|
|
docker-compose -p "$instance" stop
|
|
|
|
while [ "$(docker inspect -f '{{.State.Running}}' "${instance}"_db_1)" == "true" ] ; do
|
|
sleep 2
|
|
echo "waiting for $instance to stop..."
|
|
done
|
|
|
|
echo "waiting for $instance to stop... done"
|
|
echo ""
|
|
|
|
echo "docker-compose up -f docker-compose-before-upgrade-postgres.yml -p ${instance}-before-upgrade-postgres"
|
|
|
|
if ! docker-compose -f docker-compose-before-upgrade-postgres.yml -p "${instance}"-before-upgrade-postgres up -d; then
|
|
end=$(date +%s)
|
|
runtime=$((end-start))
|
|
echo "script failed after ${runtime}s"
|
|
exit 1
|
|
fi
|
|
|
|
echo "starting postgres beforedb for handling tables with oids"
|
|
|
|
while [ "$(docker inspect -f '{{.State.Health.Status}}' "${instance}"-before-upgrade-postgres-beforedb-1)" != "healthy" ]; do
|
|
echo "waiting for db..."
|
|
sleep 5
|
|
done
|
|
|
|
echo "waiting for db... done"
|
|
echo ""
|
|
|
|
echo "alter known oid-tables to without oids"
|
|
docker exec -i "${instance}"-before-upgrade-postgres-beforedb-1 psql -U metasfresh -d metasfresh -tAc "
|
|
ALTER TABLE de_metas_acct.accounting_docs_to_repost SET WITHOUT OIDS;
|
|
ALTER TABLE public.c_invoice_candidate_recompute SET WITHOUT OIDS;
|
|
ALTER TABLE public.fact_acct_log SET WITHOUT OIDS;
|
|
"
|
|
|
|
docker exec -i "${instance}"-before-upgrade-postgres-beforedb-1 psql -U metasfresh -d metasfresh -tAc "
|
|
SELECT quote_ident(nspname) || '.' || quote_ident(relname)
|
|
FROM pg_catalog.pg_class c
|
|
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE 1=1
|
|
AND c.relkind = 'r'
|
|
AND c.relhasoids = true
|
|
AND n.nspname <> 'pg_catalog'
|
|
order by n.nspname, c.relname;
|
|
" >tables_with_oids.txt
|
|
|
|
rm handle_tables_with_oids.sql 2> /dev/null
|
|
|
|
while read -ru 3 p; do
|
|
nextOID=$(docker exec -i "${instance}"-before-upgrade-postgres-beforedb-1 pg_controldata -D /var/lib/postgresql/data | grep OID)
|
|
nextOID=${nextOID#"Latest checkpoint's NextOID:"}
|
|
nextOID=${nextOID// /}
|
|
|
|
echo "
|
|
|
|
ALTER TABLE $p DISABLE TRIGGER ALL;
|
|
ALTER TABLE $p ADD newoid bigint NOT NULL;
|
|
UPDATE $p SET newoid = oid;
|
|
ALTER TABLE $p SET WITHOUT OIDS;
|
|
ALTER TABLE $p RENAME newoid TO oid;
|
|
CREATE SEQUENCE IF NOT EXISTS oid_seq;
|
|
ALTER TABLE $p ALTER oid SET DEFAULT nextval('oid_seq');
|
|
SELECT setval('oid_seq', $nextOID);
|
|
ALTER TABLE $p ENABLE TRIGGER ALL;
|
|
|
|
" >> handle_tables_with_oids.sql
|
|
done 3< tables_with_oids.txt
|
|
|
|
oidsql=$(cat handle_tables_with_oids.sql)
|
|
docker exec -i "${instance}"-before-upgrade-postgres-beforedb-1 psql -U metasfresh -d metasfresh -c "$oidsql"
|
|
|
|
echo "docker stop ${instance}-before-upgrade-postgres-beforedb-1"
|
|
docker stop "${instance}"-before-upgrade-postgres-beforedb-1
|
|
|
|
while [ "$(docker inspect -f '{{.State.Running}}' "${instance}"-before-upgrade-postgres-beforedb-1)" == "true" ]; do
|
|
sleep 2
|
|
echo "waiting for ${instance}-before-upgrade-postgres-beforedb-1 to stop..."
|
|
done
|
|
|
|
echo "waiting for ${instance}-before-upgrade-postgres-beforedb-1 to stop... done"
|
|
|
|
echo "docker rm ${instance}-before-upgrade-postgres-beforedb-1"
|
|
docker rm "${instance}"-before-upgrade-postgres-beforedb-1
|
|
|
|
echo "docker-compose up -f docker-compose-upgrade-postgres.yml -p ${instance}-upgrade-postgres"
|
|
docker-compose -f docker-compose-upgrade-postgres.yml -p "${instance}"-upgrade-postgres up
|
|
|
|
docker exec -i -u postgres "${instance}"-upgrade-postgres-upgradedb-1 cp ./update_extensions.sql /var/lib/postgresql/15/data/update_extensions.sql
|
|
|
|
upgradelogs=$(docker logs "${instance}"-upgrade-postgres-upgradedb-1 --tail 100)
|
|
grep -wq "Upgrade Complete" <<< "$upgradelogs"
|
|
upgradeComplete=$?
|
|
|
|
echo "docker stop ${instance}-upgrade-postgres-upgradedb-1"
|
|
docker stop "${instance}"-upgrade-postgres-upgradedb-1
|
|
|
|
while [ "$(docker inspect -f '{{.State.Running}}' "${instance}"-upgrade-postgres-upgradedb-1)" == "true" ]; do
|
|
sleep 2
|
|
echo "waiting for ${instance}-upgrade-postgres-upgradedb-1 to stop..."
|
|
done
|
|
echo "waiting for ${instance}-upgrade-postgres-upgradedb-1 to stop... done"
|
|
echo ""
|
|
|
|
echo "docker rm ${instance}-upgrade-postgres-upgradedb-1"
|
|
docker rm "${instance}"-upgrade-postgres-upgradedb-1
|
|
|
|
if [ $upgradeComplete -ne 0 ]; then
|
|
echo "upgrade failed removing newdb volume..."
|
|
rm -r ./../volumes/newdb
|
|
end=$(date +%s)
|
|
runtime=$((end-start))
|
|
echo "script failed after ${runtime}s"
|
|
exit 1
|
|
fi
|
|
|
|
echo "copying config files..."
|
|
cp ./../volumes/db/data/pg_hba.conf ./../volumes/newdb/data/pg_hba.conf
|
|
cp ./../volumes/db/data/postgresql.conf ./../volumes/newdb/data/postgresql.conf
|
|
cp ./../volumes/db/data/postgresql.auto.conf ./../volumes/newdb/data/postgresql.auto.conf
|
|
echo "copying config files... done"
|
|
echo ""
|
|
|
|
docker-compose -f docker-compose-after-upgrade-postgres.yml -p "${instance}"-after-upgrade-postgres up -d
|
|
|
|
echo "starting postgres afterdb for update extensions, vacuumdb and reindex"
|
|
|
|
while [ "$(docker inspect -f '{{.State.Health.Status}}' "${instance}"-after-upgrade-postgres-afterdb-1)" != "healthy" ]; do
|
|
echo "waiting for db..."
|
|
sleep 5
|
|
done
|
|
|
|
echo "waiting for db... done"
|
|
echo ""
|
|
|
|
echo "update extensions"
|
|
docker exec -i -u postgres "${instance}"-after-upgrade-postgres-afterdb-1 psql -U metasfresh -d metasfresh -f "/var/lib/postgresql/data/update_extensions.sql"
|
|
rm ./../volumes/newdb/data/update_extensions.sql
|
|
echo ""
|
|
|
|
start_vacuumdb=$(date +%s)
|
|
echo "vacuumdb -d metasfresh --analyze-in-stages -v"
|
|
docker exec -i -u postgres "${instance}"-after-upgrade-postgres-afterdb-1 vacuumdb -d metasfresh --analyze-in-stages -v
|
|
end_vacuumdb=$(date +%s)
|
|
runtime_vacuumdb=$((end_vacuumdb-start_vacuumdb))
|
|
echo "vacuumdb took ${runtime_vacuumdb}s"
|
|
echo ""
|
|
|
|
start_reindex=$(date +%s)
|
|
echo "REINDEX each table of metasfresh database"
|
|
docker exec -i -u postgres "${instance}"-after-upgrade-postgres-afterdb-1 psql -U metasfresh -d metasfresh -tAc "
|
|
SELECT 'REINDEX (VERBOSE, CONCURRENTLY) TABLE ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' /*' || pg_size_pretty(pg_total_relation_size(C.oid)) || '*/;'
|
|
FROM pg_class C
|
|
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
|
|
WHERE nspname <> 'pg_catalog'
|
|
AND C.relkind = 'r'
|
|
AND nspname !~ '^pg_toast'
|
|
ORDER BY pg_total_relation_size(C.oid) ASC;
|
|
" > tables_to_reindex.txt
|
|
|
|
#ignore table if needed
|
|
#grep -v "tablename" tables_to_reindex.txt > tmpfile && mv tmpfile tables_to_reindex.txt
|
|
|
|
while read -ru 3 p; do
|
|
echo "$p"
|
|
docker exec -i -u postgres "${instance}"-after-upgrade-postgres-afterdb-1 psql -U metasfresh -d metasfresh -c "$p"
|
|
echo ""
|
|
done 3< tables_to_reindex.txt
|
|
|
|
end_reindex=$(date +%s)
|
|
runtime_reindex=$((end_reindex-start_reindex))
|
|
echo "REINDEX took ${runtime_reindex}s"
|
|
echo ""
|
|
|
|
echo "docker stop afterdb"
|
|
docker stop "${instance}"-after-upgrade-postgres-afterdb-1
|
|
|
|
while [ "$(docker inspect -f '{{.State.Running}}' "${instance}"-after-upgrade-postgres-afterdb-1)" == "true" ]; do
|
|
sleep 2
|
|
echo "waiting for ${instance}-after-upgrade-postgres-afterdb-1 to stop..."
|
|
done
|
|
|
|
echo "waiting for ${instance}-after-upgrade-postgres-afterdb-1 to stop... done"
|
|
|
|
echo "docker rm afterdb"
|
|
docker rm "${instance}"-after-upgrade-postgres-afterdb-1
|
|
|
|
mv ./../volumes/db ./../volumes/olddb
|
|
mv ./../volumes/newdb ./../volumes/db
|
|
|
|
echo ""
|
|
echo "upgrade done! You can now change db image and restart the instance"
|
|
echo ""
|
|
|
|
end=$(date +%s)
|
|
runtime=$((end-start))
|
|
echo "script finished in ${runtime}s"
|