Using the suggestion from @LaurenzAlbe's answer, I've created a batch file that makes all of the necessary changes to the database using the psql command line (including pg_dump and pg_restore). It also logs each step of the process to a text file and creates a separate error log file if something goes wrong. If anything does fail, it will (should) prompt to restore the current database to its pre-update state.
Because that answer formed the foundation for my eventual solution, I've accepted it as the answer. I provide this answer simply to show what I came up with in hopes that it helps anyone else that's encountering issues upgrading to PostgreSQL v12 because of existing OID's.
There are several caveats and "gotchas" in here, but I've successfully used this script to update every table in my database that had the WITH OIDS=TRUE flag set and completed the migration from PostgreSQL version 9.4 to version 12.
NOTES
- This batch file assumes that you've already attempted to use
pg_upgrade to migrate your data from a previous version of PostgreSQL to a new version 12 instance, and that upgrade failed due to existing tables that use OID's. In this case, pg_upgrade should have generated a text file named tables_with_oids.txt. The batch file will need to be able to access this file to make all of the changes "auto-magically".
- Also, instead of using existing
OID values as suggested by @LaurenzAlbe, I made the decision to start the sequence for each of my "new OID" columns at 1. I did some extensive digging through code and the database to see if there was anything that was explicitly referencing a specific OID value and didn't find anything. Everything I found was just making referential calls to the OID to get other values from the table, so I made the call and just decided to restart each table's OID.
- BE AWARE: THIS COULD CAUSE A PROBLEM IN YOUR DATABASE OR APPLICATION if anything in either is looking for a specific value tied to a particular record.
- If you would prefer to use the existing
OID values (as recommended by @LaurenzAlbe), you'll need to do a couple of things differently. Namely, you'll have to wait to set the NOT NULL constraint on your new OID column until after the data has been populated from the old OID column. You'll also most likely want to set the nextval() default value before adding the NOT NULL constraint to make sure that every record has this column populated.
- This batch file does everything in one run instead of doing part of it pre-upgrade and part post-upgrade. I had a couple of instances of PostgreSQL to work with, so I was able to convert all of the "problem" tables on one, testing database, then dump that data before using pg_restore to bring it in to the new PostgreSQL 12 database instance.
- This batch file DOES NOT dump the updated data from the database or import it into the new, v12 server. I still wanted to have final control over that step, but I suppose it shouldn't be too difficult to add a few lines to the
:FINALIZE section that would do this.
THE BATCH FILE
This batch file reads line-by-line through the tables_with_oids.txt file generated by pg_upgrade and does the following:
DATABASES
- For each database found (identified in the file as a line starting with
Database:), a "backup" dump file is created of the entire database in case something goes wrong.
- There may be some views that are dependent on the original
oid, which will prevent you from dropping that column. The script queries for dependencies and dumps the definition of any it finds, then drops the dependent view.
- When it moves on to the next database, it restores any views that were dumped/dropped from the previous database.
TABLES
- For each table (a line that doesn't start with
Database:), it splits out the schema and table name.
- The script then gets the current table's owner to apply to the new sequence (the sequence must have the same owner as the table in order to be used by the new column as a default value).
- All triggers on the table are disabled to prevent them from firing when changes are made to the tables (and there will be a lot of changes being made).
- Creates a new sequence using the current schema and table names to generate a (hopefully) unique sequence name in the format
<schema>_<table>_oid_seq
- Sets the owner of the sequence to the same as the table (see #2).
- Grants the necessary permissions to the appropriate users/groups on the sequence.
- If you use this batch file, you'll need to determine what permissions will be needed and by whom.
- Creates a new column (
newoid) in the table.
- Alters the definition of the
newoid column to use the sequence to auto-increment values for new records (see my note above about this).
- Alters the definition of the table to
WITHOUT OIDS (which automatically drops the existing oid column).
- Renames the new column created in #7 to
oid.
- Associates the new sequence created in #4 with the newly created
oid column. This is done simply to make sure that, if the new oid column is dropped from the database at some point in the future (you've/I've figured out how to eradicate all references to it), the newly created sequence should be dropped as well.
- Reenables all of the triggers on the table.
THE CODE (finally)
Okay, now with all of the explanation out of the way, here's the code I came up with for the batch file. I hope you find it useful. I've tried to document the batch file itself fairly thoroughly. Values that need to be defined explicitly for individual environments are identified by angle brackets and all-caps in the code <YOUR SETTING HERE>
@ECHO OFF
CLS
:: --------------------------------------------------------------------
:: SETLOCAL to ensure that all variables are cleared from memory at the
:: end of the batch file's execution
:: --------------------------------------------------------------------
SETLOCAL EnableDelayedExpansion
SET "startTime=%time: =0%"
:: --------------------------------------------------------------------
:: >>>>>>> MAKE SURE TO SET THESE ACCORDING TO YOUR ENVIRONMENT <<<<<<<
:: PostgreSQL Variables
:: --------------------------------------------------------------------
SET PGPASSWORD=<SUPERUSER/ADMINISTRATIVE PASSWORD>
SET PGSQLHost=<HOSTNAME OF SERVER RUNNING THE "OLD" DATA>
SET PGSQLBin=<PATH TO THE POSTGRESQL bin DIRECTORY FOR THE "OLD" INSTANCE>
SET BackupPath=<PATH WHERE YOU WANT TO STORE BACKUPS AND LOGS>\%PGSQLHost%
:: CHECK FOR/CREATE THE BACKUP/LOG DIRECTORY
IF NOT EXIST %BackupPath%\ (
MD %BackupPath%\
)
FOR /f "tokens=*" %%a in ('powershell get-date -format "{yyyyMMdd}"') DO SET CURRDATE=%%a
SET TEMPLOG="%BackupPath%\pgsql%CURRDATE%.tmp"
SET ERRORLOG="%BackupPath%\pgsql_12_update_errors_%CURRDATE%.log"
SET SUCCESSLOG="%BackupPath%\pgsql_12_update_%CURRDATE%.log"
:: --------------------------------------------------------------------
:: THIS (PUSHD) IS USED TO AVOID UNC PATHS AS THE COMMAND PROMPT CAN'T
:: USE THEM AS THE CURRENT DIRECTORY.
:: See https://learn.microsoft.com/en-us/windows-server/administration/windows-commands/pushd
:: for more information on PUSHD/POPD
:: --------------------------------------------------------------------
PUSHD "%PGSQLBin%"
SET BinPath=%cd%
SET TableCount = 0
ECHO --------------------------------------------------------------------
ECHO Update started at %startTime%
ECHO Preparing list of PostgreSQL 9.4 tables to update...
ECHO --------------------------------------------------------------------
ECHO.
ECHO -------------------------------------------------------------------- > %SUCCESSLOG%
ECHO Removing OID references to facilitate upgrade from PostgreSQL 9.4 >> %SUCCESSLOG%
ECHO to PostgreSQL 12 >> %SUCCESSLOG%
ECHO. >> %SUCCESSLOG%
ECHO See official release announcement for details >> %SUCCESSLOG%
ECHO https://www.postgresql.org/docs/12/release-12.html >> %SUCCESSLOG%
ECHO. >> %SUCCESSLOG%
ECHO PostgreSQL 9.4 update started at %startTime% >> %SUCCESSLOG%
ECHO -------------------------------------------------------------------- >> %SUCCESSLOG%
ECHO. >> %SUCCESSLOG%
:: READ THE tables_with_oids.txt FILE LINE-BY-LINE
FOR /f "eol=; tokens=1 delims=," %%T IN (%BackupPath%\tables_with_oids.txt) DO (
SET oidbuffer=%%T
:: --------------------------------------------------------------------
:: LINES THAT DON'T BEGIN WITH `Database:` ARE THE INDIVIDUAL TABLES
:: WHICH CONTAIN OID'S AND NEED TO BE UPDATED
:: --------------------------------------------------------------------
IF NOT !oidbuffer:~0^,9!==Database: (
SET NameBuffer=%%T
:: SPLIT THE SCHEMA AND TABLE NAMES FROM THE CURRENT LINE
FOR /f "tokens=1,2 delims=." %%a IN ("!NameBuffer!") DO SET SchemaName=%%a&SET TableName=%%b
IF NOT [!DBName!]==[] (
IF NOT [!TableName!]==[] (
CALL :TRIM TableName
IF NOT [!SchemaName!]==[] (
CALL :TRIM SchemaName
ECHO Updating !SchemaName!.!TableName! in !DBName! on %PGSQLHost%
ECHO UPDATING !SchemaName!.!TableName! IN !DBName! ON %PGSQLHost% >> %SUCCESSLOG%
:: GET THE OWNER OF THE TABLE
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "SELECT tableowner FROM pg_tables WHERE schemaname = '!SchemaName!' AND tablename = '!TableName!';" > %BackupPath%\tblown.tmp
:: --------------------------------------------------------------------
:: CODE FROM STACKOVERFLOW FOR psql.exe ERROR HANDLING IN BATCH FILE
:: (USED REPEATEDLY)
:: https://stackoverflow.com/a/30775383/2569697
:: --------------------------------------------------------------------
IF NOT errorlevel 1 (
FOR /F "usebackq delims=" %%g IN (%BackupPath%\tblown.tmp) DO (
SET TABLEOWNER=%%g
ECHO - Table owner is %%g >> %SUCCESSLOG%
)
) ELSE (
ECHO Error retrieving table owner from !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: DISABLE ALL TRIGGERS ON THE TABLE
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "ALTER TABLE \"!SchemaName!\".\"!TableName!\" DISABLE TRIGGER ALL;" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - Triggers disabled >> %SUCCESSLOG%
) ELSE (
ECHO Error adding newoid column to !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: CREATE A NEW SEQUENCE TO BE USED FOR THE NEW oid COLUMN
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "CREATE SEQUENCE \"!SchemaName!\".!SchemaName!_!TableName!_oid_seq;" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - New sequence created for `oid` column: `!SchemaName!_!TableName!_oid_seq` >> %SUCCESSLOG%
) ELSE (
ECHO Error creating oid sequence for !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: SET THE OWNER OF THE NEW SEQUENCE TO THE SAME AS THE TABLE (see above)
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "ALTER SEQUENCE \"!SchemaName!\".!SchemaName!_!TableName!_oid_seq OWNER TO \"!TABLEOWNER!\"" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - Ownership of `!SchemaName!_!TableName!_oid_seq` sequence set to !TABLEOWNER! >> %SUCCESSLOG%
) ELSE (
ECHO Error altering ownership of oid sequence for !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: GRANT USER/GROUP PERMISSIONS ON THE SEQUENCE
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "GRANT ALL ON SEQUENCE \"!SchemaName!\".!SchemaName!_!TableName!_oid_seq TO <USER/GROUP NAME>" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - Permissions granted on `!SchemaName!_!TableName!_oid_seq` sequence for users/groups >> %SUCCESSLOG%
) ELSE (
ECHO Error granting permissions on oid sequence for !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: CREATE THE newoid COLUMN IN TABLE
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "ALTER TABLE \"!SchemaName!\".\"!TableName!\" ADD newoid bigint NOT NULL DEFAULT nextval('\"!SchemaName!\".!SchemaName!_!TableName!_oid_seq');" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - `newoid` column created >> %SUCCESSLOG%
) ELSE (
ECHO Error adding newoid column to !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: REMOVE THE OID ASSOCIATION WITH THE TABLE - THIS ALSO REMOVES THE AUTO-CREATED oid COLUMN
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "ALTER TABLE \"!SchemaName!\".\"!TableName!\" SET WITHOUT OIDS;" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - Table definition updated to remove OID association >> %SUCCESSLOG%
) ELSE (
ECHO Error altering table definition for !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: RENAME THE newoid COLUMN TO REPLACE THE ORIGINAL oid COLUMN
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "ALTER TABLE \"!SchemaName!\".\"!TableName!\" RENAME newoid TO oid;" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - `newoid` column renamed to `oid` >> %SUCCESSLOG%
) ELSE (
ECHO Error renaming newoid column in !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: ASSOCIATE THE NEW SEQUENCE WITH THE NEW oid COLUMN
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "ALTER SEQUENCE \"!SchemaName!\".!SchemaName!_!TableName!_oid_seq OWNED BY \"!SchemaName!\".\"!TableName!\".oid;" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - `!SchemaName!_!TableName!_oid_seq` sequence explicitly linked to `oid` column >> %SUCCESSLOG%
) ELSE (
ECHO Error altering column association of oid sequence for !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: REENABLE ALL TRIGGERS ON THE TABLE
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "ALTER TABLE \"!SchemaName!\".\"!TableName!\" ENABLE TRIGGER ALL;" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - All triggers reenabled on !SchemaName!.!TableName! table >> %SUCCESSLOG%
) ELSE (
ECHO Error reenabling triggers on !SchemaName!.!TableName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
)
SET /A TableCount=TableCount+1
ECHO -------------------------------------------------------------------- >> %SUCCESSLOG%
ECHO !DBName!.!SchemaName!.!TableName! on %PGSQLHost% >> %SUCCESSLOG%
ECHO was updated successfully. >> %SUCCESSLOG%
ECHO -------------------------------------------------------------------- >> %SUCCESSLOG%
ECHO. >> %SUCCESSLOG%
)
)
) ELSE (
:: --------------------------------------------------------------------
:: THE `DBName` VARIABLE WON'T BE SET IF THIS IS THE FIRST LINE, BUT
:: AFTER EACH DATABASE HAS BEEN UPDATED, WE NEED TO RESTORE ANY VIEWS
:: THAT WE DUMPED FROM THE PREVIOUS DATABASE.
:: --------------------------------------------------------------------
IF NOT [!DBName!]==[] (
FOR %%F IN (%BackupPath%\!DBName!\*.dmp) DO (
%BinPath%\pg_restore -h %PGSQLHost% -p 5432 -U <SUPERUSER/ADMINISTRATIVE USERNAME> -d !DBName! "%BackupPath%\!DBName!\%%~nF.dmp"
IF NOT errorlevel 1 (
ECHO - View restored to !DBName!.!SchemaName! from %%~nF.dmp >> %SUCCESSLOG%
) ELSE (
ECHO Error restoring view from %%~nF to !DBName!.!SchemaName!: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
)
)
SET DBName=%%T
SET DBName=!DBName:Database: =!
CALL :TRIM DBName
ECHO --------------------------------------------------------------------
ECHO Backing up !DBName! database on %PGSQLHost%
ECHO -------------------------------------------------------------------- >> %SUCCESSLOG%
ECHO BACKING UP `!DBName!` DATABASE ON %PGSQLHost% >> %SUCCESSLOG%
:: CHECK FOR/CREATE THE BACKUP PATH
IF NOT EXIST %BackupPath%\!DBName!\ (
MD %BackupPath%\!DBName!\
)
:: DUMP THE CURRENT DATABASE AS A BACKUP IN CASE SOMETHING FAILS
%BinPath%\pg_dump -h %PGSQLHost% -p 5432 -U <SUPERUSER/ADMINISTRATIVE USERNAME> -E UTF8 -Fc -f %BackupPath%\!DBName!\!DBName!.gz !DBName!
:: CHECK FOR ANY VIEWS THAT ARE DEPENDENT ON EXISTING `oid` COLUMNS
ECHO Getting oid dependencies in !DBName! database
ECHO GETTING OID DEPENDENCIES IN `!DBName!` DATABASE >> %SUCCESSLOG%
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "SELECT n.nspname || '.' || c.relname AS table_name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON (n.oid = c.relnamespace) LEFT JOIN pg_catalog.pg_attribute a ON (c.oid = a.attrelid AND a.attnum > 0 AND NOT a.attisdropped) LEFT JOIN pg_catalog.pg_stat_all_tables s ON (c.oid = s.relid) WHERE c.relkind = 'v' AND (pg_catalog.pg_get_viewdef(c.oid, true) ~* '.oid' OR pg_catalog.pg_get_viewdef(c.oid, true) ~* ',oid' OR pg_catalog.pg_get_viewdef(c.oid, true) ~* ' oid') AND n."nspname" NOT LIKE 'pg_%%' AND n."nspname" NOT LIKE 'information_%%' GROUP BY n.nspname, c.relowner, c.relkind, c.relname, s.n_live_tup, c.oid ORDER BY n.nspname, c.relname;" > %BackupPath%\!DBName!\!DBName!dep.txt
:: DUMP ANY DEPENDENT VIEWS TO SEPARATE FILES FOR RESTORATION LATER
FOR /F "usebackq delims=" %%V IN (%BackupPath%\!DBName!\!DBName!dep.txt) DO (
SET ViewNameBuffer=%%V
FOR /f "tokens=1,2 delims=." %%a IN ("!ViewNameBuffer!") DO SET ViewSchema=%%a&SET ViewName=%%b
%BinPath%\pg_dump -h %PGSQLHost% -p 5432 -U <SUPERUSER/ADMINISTRATIVE USERNAME> -E UTF8 -s -t \"!ViewSchema!\".\"!ViewName!\" -Ft -f %BackupPath%\!DBName!\!DBName!_!ViewSchema!_!ViewName!_def.dmp !DBName!
IF NOT errorlevel 1 (
ECHO - Dumped definition for !ViewSchema!.!ViewName! >> %SUCCESSLOG%
) ELSE (
ECHO Error dumping definition for `!ViewSchema!.!ViewName!` view: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
:: DROP THE DEPENDENT VIEW
%BinPath%\psql -qtAX -h %PGSQLHost% -p 5432 -d !DBName! -U <SUPERUSER/ADMINISTRATIVE USERNAME> -c "DROP VIEW \"!ViewSchema!\".\"!ViewName!\";" > %TEMPLOG%
IF NOT errorlevel 1 (
ECHO - Dropped !ViewSchema!.!ViewName! view >> %SUCCESSLOG%
) ELSE (
ECHO Error dropping `!ViewSchema!.!ViewName!` view: %errorlevel% >> %ERRORLOG%
GOTO :ERRORHANDLER
)
)
ECHO Updating tables in !DBName! database on %PGSQLHost%
ECHO --------------------------------------------------------------------
ECHO UPDATING TABLES IN `!DBName!` DATABASE ON %PGSQLHost% >> %SUCCESSLOG%
ECHO -------------------------------------------------------------------- >> %SUCCESSLOG%
)
)
ECHO.
ECHO --------------------------------------------------------------------
ECHO Cleaning up and finalizing the update
ECHO --------------------------------------------------------------------
GOTO :FINALIZE
:ERRORHANDLER
:: IF SOMETHING BREAKS, PROMPT TO RESTORE THE DATABASE FROM THE BACKUP
(SET /P restore=Do you want to restore the !DBName! database on %PGSQLHost% from the backup? [Y/N]: )
:: --------------------------------------------------------------------
:: The /i switch allows the test to be case-insensitive (NT-based only)
:: --------------------------------------------------------------------
IF /i "%restore%"=="Y" GOTO RESTOREBACKUP
IF /i "%restore%"=="N" GOTO :FINALIZE
ECHO Invalid selection. Please type "Y" or "N".
ECHO.
GOTO :ERRORHANDLER
:RESTOREBACKUP
:: RESTORE THE DATABASE FROM THE BACKUP CREATED BEFORE CHANGES WERE MADE
ECHO Restoring !DBName!
%BinPath%\dropdb -h %PGSQLHost% -p 5432 -U <SUPERUSER/ADMINISTRATIVE USERNAME> !DBName!
%BinPath%\createdb -E UTF8 -T template0 -O Developers -h %PGSQLHost% -p 5432 -U <SUPERUSER/ADMINISTRATIVE USERNAME> !DBName!
%BinPath%\pg_restore -h %PGSQLHost% -p 5432 -U <SUPERUSER/ADMINISTRATIVE USERNAME> -d !DBName! "%BackupPath%\!DBName!\!DBName!.gz"
ECHO `!DBName!` on %PGSQLHost% restored from backup
ECHO `!DBName!` on %PGSQLHost% restored from backup >> %SUCCESSLOG%
:FINALIZE
:: RELEASE RESOURCES ALLOCATED BY PUSHD
POPD
SET "endTime=%time: =0%"
:: --------------------------------------------------------------------
:: CODE FROM STACKOVERFLOW FOR REPORTING ELAPSED TIME OF PROCESS
:: https://stackoverflow.com/a/9935540/2569697
:: --------------------------------------------------------------------
:: Get elapsed time:
:: --------------------------------------------------------------------
SET "end=!endTime:%time:~8,1%=%%100)*100+1!" & set "start=!startTime:%time:~8,1%=%%100)*100+1!"
SET /A "elap=((((10!end:%time:~2,1%=%%100)*60+1!%%100)-((((10!start:%time:~2,1%=%%100)*60+1!%%100)"
:: --------------------------------------------------------------------
:: Convert elapsed time to HH:MM:SS:CC format:
:: --------------------------------------------------------------------
SET /A "cc=elap%%100+100,elap/=100,ss=elap%%60+100,elap/=60,mm=elap%%60+100,hh=elap/60+100"
:: DISPLAY AND WRITE THE RESULTS OF THE UPDATE TO THE LOG FILE
ECHO.
ECHO --------------------------------------------------------------------
ECHO Update completed at %endTime%
ECHO --------------------------------------------------------------------
ECHO Elapsed Time: %hh:~1%%time:~2,1%%mm:~1%%time:~2,1%%ss:~1%%time:~8,1%%cc:~1%
ECHO.
ECHO %TableCount% tables were updated on %PGSQLHost%.
ECHO. >> %SUCCESSLOG%
ECHO -------------------------------------------------------------------- >> %SUCCESSLOG%
ECHO Update completed at %endTime% >> %SUCCESSLOG%
ECHO --------------------------------------------------------------------
ECHO Elapsed Time: %hh:~1%%time:~2,1%%mm:~1%%time:~2,1%%ss:~1%%time:~8,1%%cc:~1% >> %SUCCESSLOG%
ECHO. >> %SUCCESSLOG%
ECHO %TableCount% tables were successfully updated on %PGSQLHost%. >> %SUCCESSLOG%
:: DELETE THE TEMPORARY FILES USED
DEL /F /Q %TEMPLOG%
DEL /F /Q %BackupPath%\tblown.tmp
ENDLOCAL
GOTO :EOF
:: --------------------------------------------------------------------
:: CODE FROM STACKOVERFLOW FOR TRIMMING TRAILING/LEADING WHITESPACE
:: FROM A STRING VALUE
:: https://stackoverflow.com/a/19686956/2569697
:: --------------------------------------------------------------------
:TRIM
SETLOCAL EnableDelayedExpansion
CALL :TRIMSUB %%%1%%
ENDLOCAL & SET %1=%tempvar%
GOTO :EOF
:TRIMSUB
SET tempvar=%*
GOTO :EOF
I did my best to give credit in cases where I can remember where I found a solution to a particular issue I encountered while writing this. I'm sure I didn't catch them all, but please forgive me if you recognize your contribution without attribution.
