What files are safe to delete after upgrade?

Question

This question is very similar to Recommended procedure to remove old files after major version PostgreSQL database cluster upgrade, but I did the upgrade in a different way, so the answer might be/probably is different.

I administer a database on a headless Ubuntu system with disks very near capacity. Therefore, when I upgraded my database from Postgres 10 to Postgres 12, I used the --link option.

/usr/lib/postgresql/12/bin/pg_upgrade --old-datadir=/var/lib/postgresql/10/main \
--new-datadir=/var/lib/postgresql/12/main [stuff deleted] --link 

This worked great, and now I have Postgres 12 running nicely. Time to cleanup.

I now have 2 folders in /var/lib/postgresql, called 10 and 12. The documentation suggests that these folders (or the files within them) are joined by a hard link, but doing the following suggests otherwise:

[deleted output from du that was misleading and did not say what I thought it said]

I'd like to clean this up as best I can, both because space is at a premium, and I know that there is a danger that future me will come along and delete 10 because we're on 12.

In this situation, what do I need, and what can I delete? How can I make the folder structure as clear as possible to my future self?

Are there things elsewhere I can delete?

Answer 1

That is simple: remove the old data directory, and the garbage should be gone.

You have to do that anyway, because the old data directory cannot be used any more once you have started the new server.

This will of course not remove bloat. To get rid of that, identify affected tables and indexes and use VACUUM (FULL) or REINDEX to rebuild them.

Answer 2

After a successful upgrade, pg_upgrade will create a special script for you:

Running this script will delete the old cluster's data files: ./delete_old_cluster.sh

It containts just rm -rf commands for old data directory and tablespaces (in case this cluster had some create tablespace).

(or delete_old_cluster.bat for windows with appropriate RMDIR /s/q commands)

One more note: check if your pg_wal (or pg_xlog for older versions) is a symbolic link. You need to manually delete the target directory of the symbolic link. Neither rm -rf of the old data directory nor the delete_old_cluster script delete this directory.

---

PS: It's a good idea to perform the delete only after the replica has started. It is quite easy to delete something by mistake. Move the directory to a different location, make sure the updated database is still working, and completely remove directory only after a few minutes is also a good idea.

Answer 3

I got hit by this. https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/6772. I would be careful of deleting via script. I ran pg_upgrade with -k (the hard link)> Then after a successful upgrade I ran that script. I was back to 32MB in the new dir. So that script actually deleted the new upgraded data. I was supposed to have 6.1tb. Also to share more context. I was doing the pg_upgrade on a PVC in K8s. postgres is managed by the cnpg controller.