PostgreSQL – Debugging Incomplete Backup in PSQL

backuppg-dumppostgresqlpsql

I've been doing backups of my database " screen ".

after connecting to PSQL and typing \l+

I'm getting (among other things):

       Name       | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description
------------------+-------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------
           screen | admin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 36 GB   | pg_default |

The size of my database being around 36GB.

Now i usually make regular backups doing :

pg_dumb screen > screenbackup.bak

And the size of the output was always pretty consistent with the size of my database.

But today i got a backup of only 8gb and that seems really odd to me.

After restoring into a temporary db and performing a few query there indeed seems to be a few missing data.

The size of the restored data was 10GB..

Thankfully I never dropped the original in the first place but there always seems to be a problem. I can't get pg_dump or pg_dumpall to backup the complete DB. The size is inconsistent when in my experience it's often at least the same size of the db or Bigger. not 4 time smaller..

Do you have any idea where to go from there to know where the problem is coming from?

Edit: maybe it's important: I'm on PSQL 9.4.4.1 and trying to do the backup so I can update Postgres in addition to just saving my data.

Best Answer

Did you get any error messages on dump, or on restore? If not, then the most likely explanation is that nothing is wrong. There is no tight coupling between the size of the database and the size of the dump. For example, if you deleted a bunch of data and did not do a "Vacuum full", then the database itself is unlikely to shrink, but the dump (and the restore of it) will be smaller.

Related Question