We start with postgres 9.3 installed from a self-build deb. We'd like to upgrqde to 9.6, but that requires both versions to be installed on the server. The idea is to add the new version directly from the repository, and adjust the data and log directories so both can run at the same time. We can then run pg_upgrade or a dump/restore to upgrade to a new cluster.
Steps:
# Create new data and log directories
mkdir /postgres/pg_data.new && chown postgres:postgres /postgres/pg_data.new
mkdir /mnt/pg_log.new && chown postgres:postgres /mnt/pg_log.new
# Set up postgres repo
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
apt update
# Install specific version of postgresql. Do not install default as this could uninstall old version
apt install postgresql-9.6 postgresql-contrib-9.6
# Run initdb for new database
su postgres -c "/usr/lib/postgresql/9.6/bin/initdb -D /postgres/pg_data.new"
# Copy old *.conf files into /etc/postgresql/9.6/main
# edit /etc/postgresql/9.6/main/postgresql.conf.
# Change data dir, log dir to new values. Change port to 5433
# Optiopnally, start up new postgresql to ensure it is running
systemctl start postgresql
With both databases up you can dump the old database and restore to the new one. You can also stop both databases and use pg_upgrqade to update
When done, uninstall the old version of the database. You may need to do this for some of the new utilities to work correctly. Because I installed via a custom-built deb called poatgres, I have to do this:
apt remove postgresql
You may need to provide a version. Either way BE SURE you do not remove the new database.
I was unable to run pg_upgrade until I did the following:
- removed plv8 extensions and functions from thedatabase
drop extension plv8 CASCADE; - Fixed library path prior to running pg_upgrade. See below
Library issue fixable by:
export LD_LIBRARY_PATH=/usr/lib/postgresql/9.6/lib
sudo /sbin/ldconfig /usr/lib/postgresql/9.6/lib