saygoodbyye commented on issue #1422: URL: https://github.com/apache/age/issues/1422#issuecomment-1844718255
@jrgemignani As documentation says "pg_upgrade allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for major version upgrades." To demonstrate a typical situation when upgrading from one major edition of PostgreSQL to another, I wrote the following scripts. The essence of the work is that we install PostgreSQL 14 along with Age (PG14 branch) and then run data.sql to add some AGE graphs. **start14.sh:** ```bash killall postgres export PGDATA=/tmp/REL_14_STABLE/data export PG=/tmp/REL_14_STABLE export PATH=$PG/bin:$PATH rm -rf $PG cd postgres make distclean >/dev/null git clean -dfx >/dev/null git checkout REL_14_STABLE ./configure --enable-cassert --enable-tap-tests --enable-debug --prefix=$PG --quiet make -j$(nproc) -s install && make -j$(nproc) -s -C contrib install cd age make distclean >/dev/null git clean -dfx >/dev/null git checkout PG14 make -j$(nproc) install initdb -D $PGDATA pg_ctl -l logfile start createdb $USER psql -d $USER -f data.sql pg_ctl stop ``` **data.sql:** ```sql CREATE EXTENSION age; SET search_path TO ag_catalog; SELECT * FROM create_graph('ag_graph_1'); SELECT * FROM create_graph('ag_graph_2'); SELECT * FROM create_graph('ag_graph_3'); SELECT create_vlabel('agload_test_graph1','Country1'); SELECT create_vlabel('agload_test_graph2','Country2'); SELECT * FROM create_complete_graph('gp1',5,'edges','vertices'); SELECT * FROM create_complete_graph('gp2',5,'edges'); ``` The second script installs PostgreSQL 15 along with Age (PG15 branch). **start15.sh:** ```bash export PG_old=/tmp/REL_14_STABLE export PG=/tmp/REL_15_STABLE export PATH=$PG/bin:$PATH export PGDATA_old=/tmp/REL_14_STABLE/data export PGDATA=/tmp/REL_15_STABLE/data rm -rf $PG cd postgres make distclean >/dev/null git clean -dfx >/dev/null git checkout REL_15_STABLE ./configure --enable-cassert --enable-tap-tests --enable-debug --prefix=$PG --quiet make -j$(nproc) -s install && make -j$(nproc) -s -C contrib install cd age make distclean >/dev/null git clean -dfx >/dev/null git checkout PG15 make -j$(nproc) install initdb -D $PGDATA pg_upgrade -d $PGDATA_old -D $PGDATA -b $PG_old/bin -B $PG/bin ``` Next, we run pg_upgrade from version 14 to version 15, but we get an error when upgrading. This is wrong behavior. **Upgrade results:** ```log Performing Consistency Checks ----------------------------- Checking cluster versions ok Checking database user is the install user ok Checking database connection settings ok Checking for prepared transactions ok Checking for system-defined composite types in user tables ok Checking for reg* data types in user tables fatal Your installation contains one of the reg* data types in user tables. These data types reference system OIDs that are not preserved by pg_upgrade, so this cluster cannot currently be upgraded. You can drop the problem columns and restart the upgrade. A list of the problem columns is in the file: /tmp/REL_15_STABLE/data/pg_upgrade_output.d/20231207T124124.859/tables_using_reg.txt Failure, exiting ``` **Upgrade log:** ```log In database: test ag_catalog.ag_graph_namespace_index.namespace ag_catalog.ag_graph.namespace ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: dev-unsubscr...@age.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org