0

When I'm using custom format (-Fc) in pg_dump, the file is smaller (42kb), and when I restore it, it only restores the tables and columns, not the data.

But when I try to use plain text (-Fp), the file is larger (73kb), and when I try to restore it, all data are included (table, column, rows, etc).

Also, when I try to use a Database Manager (dBeaver to be exact), and I try to backup, I can see the command, and it's the same command that I use in pg_dump. But the dump file is larger (73kb) and when I tried to restore using that dump, all data are included.

Any ideas on what seems to be the problem? As of now, I'm using plain-text as the format, but I want to use the custom one.

I'm using PostgreSQL 11.2.

This is full command that I was using in pg_dump. And this is the exact same command that dBeaver is using to dump the database.

pg_dump.exe --format=c -n public --verbose --host=localhost --port=5432 --username=postgres -f db.backup postgres_db
5
  • 1
    What restore command are you using? And what output does it generate?
    – jjanes
    Mar 27, 2019 at 12:35
  • @jjanes Hi, I used the command above, and also the command on an answer below. The result is that I generate a custom file which have a 42kb file size. Meanwhile when I use dbeaver, using the same command, I generate a custom file which have 72kb size.
    – Ardi
    Mar 28, 2019 at 4:01
  • The command you show does not save the output to a file at all, it just spews binary data to the console.
    – jjanes
    Mar 28, 2019 at 12:04
  • @jjanes oops sorry about that. fixed the command.
    – Ardi
    Mar 28, 2019 at 12:23
  • I'm pretty sure this is some kind of user error. Maybe a permissions issue. Without knowing the exact pg_restore and the output it produced, it is hard to say what it might be. Custom format compresses by default, so the smaller size is not surprising. You can convert the custom format into plain-text format by using pg_restore without specifying a connect string, pg_restore.exe db.backup > db.backup.sql, to inspect that for completeness
    – jjanes
    Mar 30, 2019 at 14:09

1 Answer 1

1

I usually do this and I do not have that problem.

Backup

pg_dump -p 5432 -U postgres -Fc -d postgres -f /tmp/postgres.custom

Restoration

pg_restore -d postgres -p 5432 -U postgres -C /tmp/postgres.custom
1
  • Tried using your command, still the same. I generate a 42kb custom file using the command line, but when I use dbeaver, used the same command, generated a 72kb file. I guess I will stick on using plain-text as a dump.
    – Ardi
    Mar 28, 2019 at 4:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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