Thread: Size of PostgreSQL backup ./. Sybase DUMP
Hello,
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?
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.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/
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.
Moreover, the base backup is compressed, and I don't know if the
Sybase dump is.
If you had used PostgreSQL v13, you could check the backup for
completenes. But the best test for a backup is to restore it.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
I don't know anything about Sybase, but if that dump is something akin to "pg_dump", then you are comparing apples and oranges. Moreover, the base backup is compressed, and I don't know if the Sybase dump is. If you had used PostgreSQL v13, you could check the backup for completenes. But the best test for a backup is to restoreit. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com FWIW, if this is of any help, Sybase dumps are conceptually very similar to those of MS SQL Server. That being said, I don'thave any other insight to offer but hoping this might help someone else. - Ken W
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@unixarea.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/
Matthias Apitz schrieb am 17.04.2021 um 08:59: > 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. Actually pg_basebackup writes the data of **all** databases, not just one. I wonder if the difference is because Sybase uses a single container file for each database. And that container file might be bigger than the actual content due to deleting data, or dropping tables. Postgres uses one file per table (multiple files if the table size exceeds 1GB). So if you drop a table in Postgres the space is immediately freed. I know that in SQL Server there is a way to shrink the database files (=containers). If that is possible in Sybase as well, you could try and compare the size then.