0

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.

0

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.

  • 'After restoring into a temporary db and performing a few query there indeed seems to be a few missing data'. Could you expand on that? What query, and what does it show? – jjanes May 19 at 11:15
  • this is exactly what it was. i had deleted lots of data ( 150 millions rows ) and the auto vaccum didn't trigger. Vaccuming fixed it. I'm assuming i was mistaken on the missing row because i can't find them. – backupscreen123 May 19 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.