PostgreSQL – Recommended Procedure to Remove Old Files After Major Version Upgrade

postgresqlpostgresql-10postgresql-11

I (successfully) followed the upgrade procedure in §18.6.1 of the PostgreSQL Manual to upgrade a PostgreSQL database cluster from 10.7 to 11.2 (running Fedora 29). I would like to remove the old tablepaces (some of which are user-defined tablespaces) and other legacy files to reclaim space not needed from the old cluster.

There are two approaches I’ve considered:

  1. Connect to the old database cluster as postgres and run queries against the pg_catalog system tables to DROP tables, tablespaces, databases, etc.
  2. Run find -L /path/to/tablespace/root -type d -user postgres -iname “PG_10_*” exec rm -rf {} \; to remove directories in the tablespace locations (note the new tablespaces share the same root directory as the old ones)

After either (1) or (2), I’d remove the old PGDATA directory.

Is there a preferred approach (including any methods not mentioned)?

Note, this is distinct from the following post: How do I uninstall the old version of Postgres on CentOS 7

Best Answer

One thing to be very careful about here is whether you’ve done a pg_dump+pg_restore or a pg_upgrade.

It sounds like you’ve done a pg_dump+pg_restore based on the docs section you’ve linked to.

In that case it should be safe to completely remove the old data directory, including table spaces, as you have a full copy of the data in the data directory of the new cluster.

If you have used pg_upgrade that is not safe to do (neither are the two methods you’ve described), because some of the files are hard-linked from the old to the new cluster.

Edit: As pointed out in a comment, the latter is only true if you have used the “--link” option for pg_upgrade.

Related Question