postgres major upgrade (9.5.x to 9.6.x) within same data space

user_569748 0
Viewed 2207

I was trying to upgrade my postgres installation from 9.5.7 to 9.6.5
My postgres database production instance has several databases and consumed ~700 GB space till now.
pg_upgrade needs 2 different dir for old and new datadir.

pg_upgrade -b oldbindir -B newbindir -d olddatadir -D newdatadir

it needs a new directory to do the pg_upgrade where as I was able to run the above command in my local/stage database as my database size was small in comparison to prod and I observed the following in my local 

    sudo du -sh /var/lib/pgsql/data-9.5
            64G /var/lib/pgsql/data-9.5
    sudo du -sh /var/lib/pgsql/data-9.6
            60G /var/lib/pgsql/data-9.6

and I was having sufficient free data space to do the interim pg_upgrade process in my local/stage and I did it successfully there.

While in production I have only ~300 GB free space.

However after the successful upgrade we will delete the /var/lib/pgsql/data-9.5 dir.

Is there any way to do the in-place data upgrade so that It will not need the same amount of extra space for interim pg_upgrade process ?

maintenance-plan postgresql database
1 Answers

Run pg_upgrade

/usr/lib/postgresql/9.6/bin/pg_upgrade
           -b /usr/lib/postgresql/9.5/bin/
           -B /usr/lib/postgresql/9.6/bin/
           -d /var/lib/pgsql/data-9.5/
           -D /var/lib/pgsql/data/
        --link --check

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 reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for roles starting with 'pg_' ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Clusters are compatible

Always run the pg_upgrade binary of the new server, not the old one. pg_upgrade requires the specification of the old and new cluster's data and executable (bin) directories. You can also specify user and port values, and whether you want the data linked instead of copied (the default).

If you use link mode, the upgrade will be much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade. Link mode also requires that the old and new cluster data directories be in the same file system. (Tablespaces and pg_xlog can be on different file systems.) See pg_upgrade --help for a full list of options.

Thanks to the postgres community comprehensive documentation which helped me a lot to find the solution after all.

user_5697480
Related