Re: after restore the size of the database is increased

On 7/15/19 6:21 AM, Luca Ferrari wrote:
> Hi all,
> this should be trivial, but if I dump and restore the very same
> database the restored one is bigger than the original one.
> I did vacuumed the database foo, then dumped and restored into bar,
> and the latter, even when vacuumed, remains bigger then the original
> one.
> No other activity was running on the cluster.
>
> What am I missing here?
>
> % vacuumdb --full foo
> vacuumdb: vacuuming database "foo"
>
> % pg_dump -Fd -f backup_foo.d -U postgres foo
>
> % createdb bar
> % pg_restore -Fd backup_foo.d -U postgres -d bar
>
> % psql -U postgres -c '\x' -c "SELECT pg_database_size( 'foo' ),
> pg_database_size( 'bar' );" template1
> Expanded display is on.
> -[ RECORD 1 ]----+-----------
> pg_database_size | 2686571167
> pg_database_size | 2690212355
>
> % vacuumdb --full bar
> vacuumdb: vacuuming database "bar"
>
> % psql -U postgres -c '\x' -c "SELECT pg_database_size( 'foo' ),
> pg_database_size( 'bar' );" template1
> Expanded display is on.
> -[ RECORD 1 ]----+-----------
> pg_database_size | 2686571167
> pg_database_size | 2688193183

What does \l+ show?

>
> % psql -c 'select version();' -U postgres template1
>
> version
> -----------------------------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 11.3 on amd64-portbld-freebsd12.0, compiled by FreeBSD
> clang version 6.0.1 (tags/RELEASE_601/final 335540) (based on LLVM
> 6.0.1), 64-bit
> (1 row)
>
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com