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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
