'PostgreSQL consumming more space after pg_upgradecluster
After upgradecluster from PostgreSQL 9.6 to 11 disk space increases from 3.8GB to 4.7GB. Where is this consumed extra space ?
My server was a Debian 9 with PostgreSQL 9.6 I upgraded it to a Debian 10 and PostgreSQL 11 was installed in the upgrade process
Show clusters
su - postgres -c 'pg_lsclusters'
9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
11 main 5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
I followed this procedure to upgrade a 9.6 PostgreSQL cluster to 11: https://blog.samuel.domains/blog/tutorials/from-stretch-to-buster-how-to-upgrade-a-9-6-postgresql-cluster-to-11
I will show you the sizes after the steps that I made.
Initial database size of 9.6
du -sh /var/lib/postgresql/*
40M /var/lib/postgresql/11
4,0G /var/lib/postgresql/9.6
# du -s
4105492 /var/lib/postgresql/9.6
postgres=# \l+
bkp | UTF8 | 36 MB |
cur | UTF8 | 9919 kB |
env_pm | UTF8 | 1883 MB |
env_tur | UTF8 | 48 MB |
homolog | UTF8 | 1874 MB |
ods | portais | UTF8 | 8103 kB |
ponto | UTF8 | 7831 kB |
postgres | UTF8 | 7071 kB |
template0 | UTF8 | 6953 kB |
template1 | UTF8 | 7063 kB |
(10 registros)
After reindexdb and vacuumdb all:
su - postgres -c 'reindexdb --all'
vacuumdb -U postgres --full --all
postgres=# \l+
bkp | UTF8 | 34 MB |
cur | UTF8 | 7847 kB |
env_pm | UTF8 | 1816 MB |
env_tur | UTF8 | 37 MB |
homolog | UTF8 | 1816 MB |
ods | UTF8 | 6439 kB |
ponto | UTF8 | 6233 kB |
postgres | UTF8 | 5927 kB |
template0 | UTF8 | 6953 kB |
template1 | UTF8 | 5817 kB |
(10 registros)
du -s /var/lib/postgresql/*
39956 /var/lib/postgresql/11
3972832 /var/lib/postgresql/9.6
du -sh /var/lib/postgresql/*
40M /var/lib/postgresql/11
3,8G /var/lib/postgresql/9.6
Upgrade from 9.6 to 11 (to /var/www/postgres/ )
su - postgres -c 'pg_dropcluster --stop 11 main'
su - postgres -c 'pg_upgradecluster 9.6 main /var/www/postgres'
du -sh /var/www/postgres/
5,4G /var/www/postgres/
vacuumdb -U postgres --full --all
su - postgres -c 'reindexdb --all'
# du -sh /var/www/postgres/
4,7G /var/www/postgres/
postgres=# \l+
bkp | UTF8 | 35 MB |
cur | UTF8 | 8261 kB |
env_pm | UTF8 | 1816 MB |
env_tur | UTF8 | 37 MB |
homolog | UTF8 | 1815 MB |
ods | UTF8 | 6981 kB |
ponto | UTF8 | 6853 kB |
postgres | UTF8 | 6445 kB |
template0 | UTF8 | 7545 kB |
template1 | UTF8 | 6461 kB |
(10 registros)
My question is how the size increases from 3.8GB to 4.7GB ?
The databases sizes (using \l+) before the upgradedb summed one by one was 3741.3 MB
The sum of sizes of databases one by one after the upgradedb was 3744.5 MB
I could not find where is the increase of consuming space (0.8 or 0.9 GB)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
