← Learning

Postgres Upgrade from 12.X to 15.4

Each postgres major version upgrade has something in the release notes like this:

"Version [X] contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities...."

With each postgres major upgrade, it might be wise to check all these potential incompatibilities to see if you are using them.

This document encompasses my notes from the upgrade from 12.X to 15.4, from the app side, although I did search for things in environments as well.

All of the ripgrep (rg) commands I used for each bullet point under “observe the following incompatibilities:” are listed, in order of the bullet points. You should be able to go down the list on the release notes and easily pick up the command to use. (Commands are specific to ripgrep but most can be adapted to vanilla grep using grep -Eri ....)

Any commands that find something will need to be inspected by a human.

All of the release notes links:

https://www.postgresql.org/docs/release/13.0/ https://www.postgresql.org/docs/release/14.0/ https://www.postgresql.org/docs/release/15.0/

Status

I am done checking all potential incompatibilities in environments and all Aviato repositories, and I did not find any issues in those repositories. Some couldn’t be directly verified within a reasonable time frame (due to searching for very common words or punctuation), but I was able to reason the safety of it. See the below “Notes” sections for details on those.

Major bump to version 13

rg -iU 'similar\s+to'
rg -iU 'escape\s+null'

rg -i '_to_tsvector'

rg -i 'effective_io_concurrency'

rg -i 'pg_stat_activity|pg_stat_ssl|pg_stat_gssapi'

rg -iU 'ALTER\s+FOREIGN\s+TABLE\s+RENAME\s+COLUMN'

rg -iU 'ALTER\s+MATERIALIZED\s+VIEW\s+RENAME\s+COLUMN'

rg -i 'wal_keep_segments'

rg -i 'CREATE\s+EXTENSION\s+FROM'

rg 'ltree|lquery|LTREE|LQUERY'

rg -i 'bt_metap'

rg -i ssl_min_protocol_version # must not be set

find -iname '*posixrules*'; rg -i posixrules

Major bump to version 14

rg -i 'array_append|array_prepend|array_cat|array_position|array_positions|array_remove|array_replace|width_bucket'

rg -i 'to_tsquery|websearch_to_tsquery'

rg -i 'EXTRACT\s*\('

rg -i 'var_samp|stddev_samp'

rg -i 'has_column_privilege'

rg -i 'inf.*\s+PRECEDING|inf.*\s+FOLLOWING'

rg -i '!|!!|numeric_fac' -g "*.sql"
F=/tmp/sqlfiles.txt; rm -fv $F; rg -i '"(select|insert|update)' -l | egrep -v '\.json$' > $F; rg -i '!|!!|numeric_fac' $(cat $F | tr '\n-\r' ' ')

rg -i 'factorial'

rg '\\D|\\W'

backreferences:  rg '\\[0-9]' -g '!*.css' -g '!*.js'

rg '\[.*(\\w-|-\\w)'

rg -i 'password_encryption'

rg -i 'vacuum_cleanup_index_scale_factor'

rg -i 'operator_precedence_warning'

rg -i '(CREATE|DROP)\s+LANGUAGE'

rg -i '[^e]gist'
rg -i 'gin__'

rg -i pg_standby

rg -i normal_rand

Major bump to version 15

rg 'public schema'

rg 'pg_database_owner'

rg 'pg_start_backup|pg_stop_backup|pg_backup_start_time|pg_is_in_backup'

rg 'plpython2u|plpythonu'

rg 'array_to_tsvector'

rg 'CREATE OR REPLACE VIEW'

rg 'U&""'

quick check for weird json literals like `1.type()`:
rg '[0-9]+\.[a-zA-Z]' -g "*.sql*" -g "*postg*" -g '!*.md' | egrep -v '\.com(:[0-9]+){0,1}"|\.com$(:[0-9]+){0,1}|\.crt"'

quick check for interval input (works for environments, not so much in scala repos):
rg '[0-9]+[YMWDHMS]{1}' -g "*.sql*" -g "*postg*" -g '!*.md' | egrep -v '"wal_buffers":|work_mem":'

rg -i 'justify_interval|justify_hours|justify_days'

rg -i 'ADMIN OPTION'

rg -i 'random' -g "*.sql*" -g "*postg*" -g '!*.md'

rg -i 'PQsendQuery'

rg -i home  -g "*.sql*" -g "*postg*" -g '!*.md'

rg -i "\-\-single-transaction"

rg -i 'postgres_fdw'

rg -i 'xml_is_well_formed'

Notes on more complicated ones

  • v13: Remove support for “opaque” pseudo-types used by pre-PostgreSQL 7.3 servers (Daniel Gustafsson)
  • v13: Remove support for defining foreign key constraints using pre-PostgreSQL 7.3 syntax (Daniel Gustafsson)
  • v13: Remove support for defining operator classes using pre-PostgreSQL 8.0 syntax (Daniel Gustafsson)
  • v13: Remove support for posixrules files in the timezone database (Tom Lane)
    • “IANA’s timezone group has deprecated this feature, meaning that it will gradually disappear from systems’ timezone databases over the next few years. Rather than have a behavioral change appear unexpectedly with a timezone data update, we have removed PostgreSQL’s support for this feature as of version 13. This affects only the behavior of POSIX-style time zone specifications that lack an explicit daylight savings transition rule; formerly the transition rule could be determined by installing a custom posixrules file, but now it is hard-wired. The recommended fix for any affected installations is to start using a geographical time zone name.”
    • There is no mention of, nor file existing for ‘posixrules’ in environments or aviato code.
      • find -iname "*posix*"
      • rg -i posixrules
    • After reading about this feature, it only affects you if you provide this file, and since no mention is made of it anywhere we must not be using it.
    • This is OK.
  • v13: Prevent display of auxiliary processes in pg_stat_ssl and pg_stat_gssapi system views (Euler Taveira)
    • “Queries that join these views to pg_stat_activity and wish to see auxiliary processes will need to use left joins.”
    • rg -i 'pg_stat_activity|pg_stat_ssl|pg_stat_gssapi'
    • environments: used in prometheus, may need to change
    • It’s actually pg_stat_activity_count and pg_stat_activity_transactions_state_age that is used.
    • This should be OK; the warning is for the specific ones listed above not the ones found.
  • v13: Rename various wait events to improve consistency (Fujii Masao, Tom Lane)
  • v14: Remove deprecated containment operators @ and ~ for built-in geometric data types and contrib modules cube, hstore, intarray, and seg (Justin Pryzby)
    • “The more consistently named <@ and @> have been recommended for many years.”
    • See https://www.postgresql.org/docs/release/14.0/ for links.
    • geometric data types: rg -i ‘^a-zA-Z$\=":([^a-zA-Z$\=":]|[^ ][^a-zA-Z])’ rg -i ‘^a-zA-Z$\=":([^a-zA-Z$\=":]|[^ ][^a-zA-Z])’ rg -i ‘(point|line|lseg|box|path|polygon|circle)’ -g “.sql” -g “postg”
      • There is no good way to search (line and path are used very often)
      • NOTE I am making the assumption that there are no geometric types being used (we don’t do mapping in Banno AFAIK).
    • cube: rg -i 'cube_* *\(' no results. OK.
    • hstore: None used. OK.
    • intarray: rg -i 'INT\[\]' - none found. OK.
    • seg: rg -i 'seg[^a-zA-Z0-9-/]' noen found. OK.
    • All OK with 1 assumption made for geometric types.

Notes on ones that are assumed ‘OK’ and disregarded

  • v14: Remove factorial operators ! and !!, as well as function numeric_fac() (Mark Dilger)
    • Note: I searched as best I could and didn’t see anything. I suspect we don’t use the factorial functions anywhere. If we do, then it is in a file that doesn’t end in .sql, or a file that doesn’t include any select, insert, or update statements.
rg -i '!|!!|numeric_fac' -g "*.sql"
F=/tmp/sqlfiles.txt; rm -fv $F; rg -i '"(select|insert|update)' -l | egrep -v '\.json$' > $F; rg -i '!|!!|numeric_fac' $(cat $F | tr '\n-\r' ' ')