Re: Size of PostgreSQL backup ./. Sybase DUMP

El día viernes, abril 16, 2021 a las 03:59:09p. m. +0200, Laurenz Albe escribió:

> On Fri, 2021-04-16 at 15:47 +0200, Matthias Apitz wrote:
> > We migrated a customer from Sybase ASE 15.7 to PostgreSQL 11.10, both on
> > Linux server. With Sybase you create DUMP of only the database in
> > question, not the server, and the gzip'ed DUMP files is around 2,6 GByte in size.
> >
> > For PostgreSQL we do backup with something like this cmd:
> >
> > pg_basebackup -U ${DBSUSER} -Ft -z -D ${BACKUPDIR}-${DATE}-${NUM}
> >
> > The resulting files
> >
> > # ls -lh backup-20210416-3
> > insgesamt 999M
> > -rw------- 1 postgres postgres 999M 16. Apr 14:02 base.tar.gz
> > -rw------- 1 postgres postgres 17K 16. Apr 14:02 pg_wal.tar.gz
> >
> > are less than 1 GByte.
> >
> > Any ideas about the cause of the difference? Can the backup somehow be
> > checked without doing a recovery-restore in a new server?
>
> I don't know anything about Sybase, but if that dump is something
> akin to "pg_dump", then you are comparing apples and oranges.

As I said, the Sybase DUMP command dumps a single database, i.e. works
more like pg_dump of PostgreSQL. Ofc, they're like apples and oranges,
but the netto data of the tables must be written to disk, and as I said
above in both cases compressed with gzip.

Even more, the pg_basebackup does not only write the netto data of the
database, but also system tables and configuration information of the
PG server. That made me asking, why it's less than the half in size as
the gzip'ed DUMP of Sybase.

One reason I could imagine is, that Sybase's DUMP writes all the pages,
even the unused pages into the output file. But this is a guess, because
I never investigated the internals of Sybase's DUMP command.

> Moreover, the base backup is compressed, and I don't know if the
> Sybase dump is.

I said this above, so you could have known it. :-)

> If you had used PostgreSQL v13, you could check the backup for
> completenes. But the best test for a backup is to restore it.

Yes, completely correct and this is what I suggested to the customer too
and we will do it.

Thanks

matthias

--
Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
¡Con Cuba no te metas! «» Don't mess with Cuba! «» Leg Dich nicht mit Kuba an!
http://www.cubadebate.cu/noticias/2020/12/25/en-video-con-cuba-no-te-metas/