Failure when trying to perform pg_upgrade

[saygoodbyye]

Describe the bug Fail of pg_upgrade

How are you accessing AGE (Command line, driver, etc.)? Accessing AGE through command line.

What data setup do we need to do? Apache AGE (master branch) with PostgreSQL (REL_15_STABLE).

What is the necessary configuration info needed?

./configure CFLAGS=" -Og" --enable-tap-tests --enable-debug --enable-cassert --prefix=/tmp/pgsql

What is the command that caused the error?

PGDATA=/tmp/pgsql/data

killall postgres
rm -rf $PGDATA

initdb -U postgres -k -D "$PGDATA"
pg_ctl -D "$PGDATA" -l `pwd`/pgsql.log start

createdb -U postgres test
psql -U postgres -dtest -c "CREATE EXTENSION age"

pg_ctl -D "$PGDATA" -l `pwd`/pgsql.log stop

pg_upgrade -U postgres -d /tmp/pgsql/data -D /tmp/pgsql/data -b /tmp/pgsql/bin/ -B /tmp/pgsql/bin

Result

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/pgsql/data/pg_upgrade_output.d/20231127T141248.693/tables_using_reg.txt

Failure, exiting

Expected behavior Successfull pg_upgrade

Best regards, Egor Chindyaskin Postgres Professional: http://postgrespro.com/

[jrgemignani]

@saygoodbyye What are you trying to upgrade and from what version(s)?

In general, upgrades of AGE are within a specific version of PostgreSQL between different versions of AGE. They are not across versions of PostgreSQL nor between a version of AGE and the master branch.

[saygoodbyye]

@jrgemignani Hello! You can get the presented error when upgrading from any version to any, my message contains only the fastest reproduction of the problem when Postgres is updated on itself. Well, how can we upgrade when having, for example, PostgreSQL 15 (with enabled AGE), on PostgreSQL 16?

[jrgemignani]

@saygoodbyye AGE for PostgreSQL version X does not run on PostgreSQL version Y. This is why we have different builds of AGE for each version of PostgreSQL.

Additionally, we don't support PostgreSQL version 16 currently. Although, it will be supported in a couple of weeks.

Changed to question/enhancement as this isn't a bug.

[saygoodbyye]

@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:

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:

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:

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:

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:

In database: test
  ag_catalog.ag_graph_namespace_index.namespace
  ag_catalog.ag_graph.namespace

[jrgemignani]

@saygoodbyye

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."

pg_upgrade is only for data and it even states that not all data is preserved -

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.

AGE is tightly integrated with PG and a lot of the internal structures are based off of initial system settings and OIDs. These settings can change and be different from system to system. As you are using pg_upgrade, I would carefully review the documentation page for it, keeping this in mind.

Currently, we only support upgrades within a version of PG. However, we could add this as something to look into, resources permitting, in the future.

[saygoodbyye]

@jrgemignani Thanks for the clarification, but it turns out that there is no way for people with a petabyte of graphs to upgrade to a major release?

[jrgemignani]

@saygoodbyye

Currently, we only support upgrades within a version of PG. However, we could add this as something to look into, resources permitting, in the future.

Our priorities, atm, are to fully support all current versions of PostgreSQL (nearly done) and the majority of the openCypher specification (nearly done). Afterwards, we will have more resources to focus on additional features and possibly cross version upgrades.

[github-actions[bot]]

This issue is stale because it has been open 45 days with no activity. Remove "Abondoned" label or comment or this will be closed in 7 days.

[sda399]

This is important issue for us too. is pg dump and restore, the only way to upgrade major version of postgres?

what is the status of this topic? is it in the roadmap for any of the future releases?

[saygoodbyye]

@sda399 Hi, The issue is still relevant as some customers with large databases may have a problem when upgrading to a major version.