9

I'm currently running PostgreSQL 9.4 in my production environment, but we're in the process of doing an OS upgrade on the server so I thought it might be time to upgrade our PostgreSQL version to the latest (12) so that I could potentially make use of some of the new features available. However, several of the tables in the various databases were built quite a while ago (as in, pre-2008, and quite possibly pre-2000 in at least some cases). These databases contain many tables using OIDs (WITH OIDS=TRUE in the definition).

Additionally, I've run a search through a majority of my code base to look for references to OID columns in any table and found a couple of instances where there are queries that explicitly call on the OID columns. Luckily, there aren't too many of these cases, and a majority of them are referencing system tables (i.e., SELECT oid FROM pg_namespace, ...FROM pg_class).

Running the pg_upgrade on the data dumped from our 9.4 database balks at this point in the process and flatly states that it won't continue until the database is free of OIDs. I understand that they've been slowly phasing these out over the past few releases, and that 12 has made them basically irrelevant, but the suggestion from pg_upgrade is to drop the OID columns entirely before proceeding. I consider that to be "overkill" for a mostly irrelevant column.

At this point - and I readily admit that I haven't thought this all the way through yet - I'm wondering if it's sufficient to simply "flip" the WITH OIDS switch in the affected table definitions to FALSE to be able to proceed with the upgrade? Will the existing OID columns remain a part of the database structure if this is flipped (I would think they would, but automated upgrades can do some funny things)?

I realize that, in an ideal situation, I would want to eventually eliminate OIDs from the database completely. However, being a one-man IT department, that's going to have to go "on the list" for later evaluation. For now, I'd simply like to get the database up-and-running on the latest PostgreSQL version while I have the opportunity to do so without immediately affecting operations.

3 Answers 3

4

If the oids of the tables are referenced in the code, you cannot simply get rid of them. Probably the old code relies on oid as an auto-generated identifier. Are there any primary keys or other indexes on these columns?

A way to preserve the oids over an upgrade would be:

  • Before upgrade, on the old database:

    ALTER TABLE has_oids ADD newoid bigint NOT NULL;
    UPDATE has_oids SET newoid = oid;
    ALTER TABLE has_oids SET WITHOUT OIDS;
    
  • After upgrade, on the new database:

    ALTER TABLE has_oids RENAME newoid TO oid;
    CREATE SEQUENCE has_oids_oid_seq OWNED BY has_oids.oid;
    ALTER TABLE has_oids ALTER oid SET DEFAULT nextval('has_oids_oid_seq');
    SELECT setval('has_oids_oid_seq', ???);
    

Here, ??? is a number higher than the highest oid present in the table.

You'd also have to take care of indexes or constraints on the oid column.

3
  • Thank you for the detailed information. I assume from your answer that "flipping" the WITH OIDS switch in the table definition SQL will actually cause the existing oids to be dropped from the database, which is something I was afraid would happen. This path to migrate the oids to new columns as auto-incrementing sequence values seems a reasonable solution, although it's going to take a good deal of work with the number of tables I'll have to change. Still this is far better than dealing with data loss due to just dropping the column entirely. Thank you again. Feb 11, 2020 at 14:38
  • Also, not to be obtuse but just for clarification, I assume your use of has_oids is intended as a "variable" placeholder for the name of any table that currently has a "functional" OID column (WITH OIDS=TRUE). I'm working on creating a script that will go through the list of databases generated by pg_upgrade to execute these statements in one shot, so I want to make sure I have everything as accurate as possible. Feb 11, 2020 at 19:01
  • 1
    Yes, exactly. I used sample names to illustrate the procedure. Feb 11, 2020 at 19:04
6

Here is a query to help find all tables that have the WITH OIDS set. It also generates the DDL to remove.

    SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET WITHOUT OIDS;'
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;
1
  • Thank you for that. I've already completed my upgrade but, if I get around to it, I can add that as a conditional to the batch file for if the tables_with_oids.txt file is not present or found. It might be helpful for someone who knows ahead of time that they're going to encounter this issue to streamline the process a bit. Oct 20, 2021 at 13:53
3

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

  1. 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".
  2. 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.
  3. 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.
  4. 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

  1. 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.
  2. 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.
  3. When it moves on to the next database, it restores any views that were dumped/dropped from the previous database.

TABLES

  1. For each table (a line that doesn't start with Database:), it splits out the schema and table name.
  2. 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).
  3. 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).
  4. 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
  5. Sets the owner of the sequence to the same as the table (see #2).
  6. 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.
  7. Creates a new column (newoid) in the table.
  8. Alters the definition of the newoid column to use the sequence to auto-increment values for new records (see my note above about this).
  9. Alters the definition of the table to WITHOUT OIDS (which automatically drops the existing oid column).
  10. Renames the new column created in #7 to oid.
  11. 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.
  12. 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://docs.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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.