Обсуждение: Table space not returned to the OS ?
Hi, A few months back (October) I had upgraded a Postgres instance from v12 —> 14. The database disk size under /var/lib/postgresql/12 was around 800GB+ back then. Note, that IIRC I had used hard-linking during the upgrade. In the database itself, lots of things have changed since. In fact, that database itself has been dropped at some point and restored from a backup. As I was running out of disk space, I started investigating and found out that /var/lib/postgresql/12/main/base/16385 —> 886GB+ /var/lib/postgresql/14 —> 400GB The last modification date on that directory (../12/) appears to be around a month ago, When the table with relied 16385 was in fact dropped. Now, In my update scripts (I use this db as an OLAP) I occasionally run VACUUM. Is it weird that the 886GB space hasn’t been returned to the OS yet? What’s the safest way to return it to the OS manually? Thanks
On Mon, Jun 27, 2022 at 11:30 AM Florents Tselai <florents.tselai@gmail.com> wrote:
Hi,
A few months back (October) I had upgraded a Postgres instance from v12 —> 14.
The database disk size under /var/lib/postgresql/12 was around 800GB+ back then.
Note, that IIRC I had used hard-linking during the upgrade.
In the database itself, lots of things have changed since.
In fact, that database itself has been dropped at some point and restored from a backup.
As I was running out of disk space, I started investigating and found out that
/var/lib/postgresql/12/main/base/16385 —> 886GB+
/var/lib/postgresql/14 —> 400GB
The last modification date on that directory (../12/) appears to be around a month ago,
When the table with relied 16385 was in fact dropped.
Now, In my update scripts (I use this db as an OLAP) I occasionally run VACUUM.
Is it weird that the 886GB space hasn’t been returned to the OS yet?
What’s the safest way to return it to the OS manually?
When you use hardlinks in the upgrade all the files remain in the old directory when they are removed from the new one such as when you drop a relation. it is there for emergency recoveries. It's only the contents of the files that's "mirrored", not the existance.
It looks like you didn't actually delete the old cluster, which you are supposed to do once you have verified that the new one works. This looks like a debian/ubuntu system, which means you probably forgot to run "pg_dropcluster 12 main"? Or if it's not a debian cluster, the equivalent of that which results in removing the data directory for 12 along with any configuration files it has elsewhere.
--
Hello Florents,
Le lun. 27 juin 2022 à 11:30, Florents Tselai <florents.tselai@gmail.com> a écrit :
Hi,
A few months back (October) I had upgraded a Postgres instance from v12 —> 14.
The database disk size under /var/lib/postgresql/12 was around 800GB+ back then.
Note, that IIRC I had used hard-linking during the upgrade.
In the database itself, lots of things have changed since.
In fact, that database itself has been dropped at some point and restored from a backup.
As I was running out of disk space, I started investigating and found out that
/var/lib/postgresql/12/main/base/16385 —> 886GB+
/var/lib/postgresql/14 —> 400GB
Can you check if your upgrade process has used hard links between the 2 folders, as explained here:
The last modification date on that directory (../12/) appears to be around a month ago,
When the table with relied 16385 was in fact dropped.
Now, In my update scripts (I use this db as an OLAP) I occasionally run VACUUM.
Is it weird that the 886GB space hasn’t been returned to the OS yet?
What’s the safest way to return it to the OS manually?
Thanks
On 27 Jun 2022, at 12:38 PM, Magnus Hagander <magnus@hagander.net> wrote:On Mon, Jun 27, 2022 at 11:30 AM Florents Tselai <florents.tselai@gmail.com> wrote:Hi,
A few months back (October) I had upgraded a Postgres instance from v12 —> 14.
The database disk size under /var/lib/postgresql/12 was around 800GB+ back then.
Note, that IIRC I had used hard-linking during the upgrade.
In the database itself, lots of things have changed since.
In fact, that database itself has been dropped at some point and restored from a backup.
As I was running out of disk space, I started investigating and found out that
/var/lib/postgresql/12/main/base/16385 —> 886GB+
/var/lib/postgresql/14 —> 400GB
The last modification date on that directory (../12/) appears to be around a month ago,
When the table with relied 16385 was in fact dropped.
Now, In my update scripts (I use this db as an OLAP) I occasionally run VACUUM.
Is it weird that the 886GB space hasn’t been returned to the OS yet?
What’s the safest way to return it to the OS manually?When you use hardlinks in the upgrade all the files remain in the old directory when they are removed from the new one such as when you drop a relation. it is there for emergency recoveries. It's only the contents of the files that's "mirrored", not the existance.It looks like you didn't actually delete the old cluster, which you are supposed to do once you have verified that the new one works. This looks like a debian/ubuntu system, which means you probably forgot to run "pg_dropcluster 12 main"? Or if it's not a debian cluster, the equivalent of that which results in
Ah, you’re right
pg_dropcluster 12 main && systemctl daemon-reload
worked fine.
Thanks.
removing the data directory for 12 along with any configuration files it has elsewhere.--
On Mon, 2022-06-27 at 11:38 +0200, Magnus Hagander wrote: > On Mon, Jun 27, 2022 at 11:30 AM Florents Tselai <florents.tselai@gmail.com> wrote: > > A few months back (October) I had upgraded a Postgres instance from v12 —> 14. > > > > The database disk size under /var/lib/postgresql/12 was around 800GB+ back then. > > Note, that IIRC I had used hard-linking during the upgrade. > > > > As I was running out of disk space, I started investigating and found out that > > > > /var/lib/postgresql/12/main/base/16385 —> 886GB+ > > /var/lib/postgresql/14 —> 400GB > > It looks like you didn't actually delete the old cluster, which you are supposed > to do once you have verified that the new one works. I think that it should be done earlier than that, namely immediately after running pg_upgrade. Once you have started the PostgreSQL 14 server (to verify that it works), you can no longer use the old cluster. Yes, the control file is crippled, but in my opinion, the earlier you delete the old cluster, the safer. Yours, Laurenz Albe
On Mon, Jun 27, 2022 at 12:01 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Mon, 2022-06-27 at 11:38 +0200, Magnus Hagander wrote:
> On Mon, Jun 27, 2022 at 11:30 AM Florents Tselai <florents.tselai@gmail.com> wrote:
> > A few months back (October) I had upgraded a Postgres instance from v12 —> 14.
> >
> > The database disk size under /var/lib/postgresql/12 was around 800GB+ back then.
> > Note, that IIRC I had used hard-linking during the upgrade.
> >
> > As I was running out of disk space, I started investigating and found out that
> >
> > /var/lib/postgresql/12/main/base/16385 —> 886GB+
> > /var/lib/postgresql/14 —> 400GB
>
> It looks like you didn't actually delete the old cluster, which you are supposed
> to do once you have verified that the new one works.
I think that it should be done earlier than that, namely immediately after running
pg_upgrade. Once you have started the PostgreSQL 14 server (to verify that it works),
you can no longer use the old cluster.
Yes, the control file is crippled, but in my opinion, the earlier you delete the old
cluster, the safer.
I'd say there is still some recoverable data in the old cluster files, even if you can't just start up the cluster in it. But yes, it comes down to how you define "verified that the new one works" to some level.