pg_upgrade

pg_upgrade offers the version upgrade for your data to the newest AgensGraph without dumping data and reloading. It offers a quick upgrade by creating new system table and reusing user data files.

The guide for using pg_upgrade is described below.

pg_upgrade [OPTION]...

Options

pg_upgrade allows the following command-line parameters.

-b bindir
–old-bindir=bindir
Previous AgensGraph version’s bin directory(environment variable: PGBINOLD)

-B bindir
–new-bindir=bindir
New AgensGraph version’s bin directory(environment variable: PGBINNEW)

-c
–check
Check cluster without any data change.

-d datadir
–old-datadir=datadir
Previous cluster’s data directory(environment variable: PGDATAOLD)

-D datadir
–new-datadir=datadir
New cluster’s data directory(environment variable: PGDATANEW)

-j
–jobs
The number of processes or threads that can be used simultaneously.

-k
–link
Use hard link instead of copying files to new cluster.

-o options
–old-options options
options to be passed directly to the old postgres command; multiple option invocations are appended.

-O options
–new-options options
options to be passed directly to the new postgres command; multiple option invocations are appended.

-p port
–old-port=port
Previous cluster’s port number (environment variable : PGPORTOLD)

-P port
–new-port=port
New cluster’s port number (environment variable: PGPORTNEW)

-r
–retain
Retain SQL and log files after the completion of upgrade.

-U username
–username=username
User name who installed the cluster(environment variable: PGUSER)

-v
–verbose
Use internal logging for detailed information.

-V
–version
Show version information.

-?
–help
Show ‘help’ texts.

Usage

The followings describe how to upgrade AgensGraph using pg_upgrade.

Backup previous database cluster.

mv /user/local/AgensGraph /usr/local/AgensGraph.old

Install new AgensGraph from its new binary.
Initialize new AgensGraph cluster.
```
initdb
```

Terminates the service of previous and new cluster.

ag_ctl -D /opt/AgensGraph/2.14 or 2.15/db_cluster stop
ag_ctl -D /opt/AgensGraph/2.16/db_cluster stop

Execute pg_upgrade.

pg_upgrade -d oldCluster/data -D newCluster/data \
  -b oldCluster/bin -B newCluster/bin

or

export PGDATAOLD=oldCluster/data
export PGDATANEW=newCluster/data
export PGBINOLD=oldCluster/bin
export PGBINNEW=newCluster/bin
pg_upgrade

Start new AgensGraph.
```
ag_ctl start
```
Do following tasks after the upgrade.
- Statistics
  Optimizer statistics is not transmitted by pg_upgrade, so you need to regenerate statistics by executing commands on the completion of upgrade. You may need to configure connection parameters again to fit new cluster.
- Delete old cluster
  If you are satisfied with the upgrade. You can delete the old cluster’s data directory by running the script mentioned upon the completion of pg_upgrade.

Demo

The following example describes how to upgrade from AgensGraph v2.15.0 to v2.16.0 with command execution examples. You may need to change your directory depending on your working environment. AgensGraph v2.15.x users can upgrade their AgensGraph version to v2.16.0 by using the upgrade guide described in this document.

Terminate previous version of AgensGraph.

ag_ctl stop
waiting for server to shut down.... done
server stopped

ps -ef | grep postgres
agens    57540 54996  0 13:31 pts/3    00:00:00 grep --color=auto postgres

Backup previous database cluster.

mv /home/agens/AgensGraph /home/agens/AgensGraph_v2.15.0

Install the new version of AgensGraph from its binary.

# upload v2.16.0 binary to server and unzip it
tar vxzf AgensGraph_v2.16.0_linux.tar.gz 

# Environment variable configuration
export AGDATA=/home/agens/AgensGraph/data
export LD_LIBRARY_PATH=/home/agens/AgensGraph/lib:$LD_LIBRARY_PATH
export PATH=/home/agens/AgensGraph/bin:$PATH

Initialize new version of AgensGraph cluster.
```
initdb
```

Terminate the services of previous and new clusters.

ag_ctl -D /home/agens/AgensGraph/data stop
ag_ctl -D /home/agens/AgensGraph_v2.15.0/data stop

Execute pg_upgrade. When executing pg_upgrade, you should use the pg_upgrade in the newer version (the destination version of upgrade) of AgensGraph.

pg_upgrade -b /home/agens/AgensGraph_v2.15.0/bin -B /home/agens/AgensGraph/bin \
  -d /home/agens/AgensGraph_v2.15.0/data -D /home/agens/AgensGraph/data

or

export PGDATAOLD=/home/agens/AgensGraph_v2.15.0/data
export PGDATANEW=/home/agens/AgensGraph/data
export PGBINOLD=/home/agens/AgensGraph_v2.15.0/bin
export PGBINNEW=/home/agens/AgensGraph/bin
pg_upgrade

Start the upgraded version of AgensGraph, connects to agens, and check the version.

ag_ctl start
agens
agens (AgensGraph 2.16.0, based on PostgreSQL 16.0)
Type "help" for help.

agens=# 

Complete the following tasks after the upgrade.
- Statistics
```
$ ./analyze_new_cluster.sh
```
- Delete the previous cluster
Notice: You must delete cluster only after you confirm the completion of upgrade.
```
$ ./delete_old_cluster.sh
```

Restore to original version

The following describes how to roll back to the previous version If the upgrade is failed.

Terminate AgensGraph.

ag_ctl stop
waiting for server to shut down.... done
server stopped

ps -ef | grep postgres
agens    57540 54996  0 13:31 pts/3    00:00:00 grep --color=auto postgres

Paste the previous version of AgensGraph (v2.15.0) into AgensGraph.

$ mv AgensGraph AgensGraph_v2.16.0
$ mv AgensGraph_v2.15.0 AgensGraph

Launch AgensGraph.
```
ag_ctl start
```

Check the version of roll-backed AgensGraph.

agens
agens (AgensGraph 2.15.0, based on PostgreSQL 15.12)
Type "help" for help.

agens=#