'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