after restore the size of the database is increased

From: Luca Ferrari <fluca1978(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: after restore the size of the database is increased
Date: 2019-07-15 13:21:28
Message-ID: CAKoxK+5oK1XR6a-P2nK6=MvT32B+QK_VUkz1WvM81ga1NA0HyQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

% 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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Fabio Pardi 2019-07-15 13:25:05 Re: help understanding pgbench results
Previous Message Luca Ferrari 2019-07-15 13:14:38 Re: help understanding pgbench results