PostgreSQL pg_dump custom file input not dumping data

pg-dumppostgresql

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

Best Answer

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

Related Solutions

Postgresql – pg_dump format => custom vs sql

Time it and see. Which is fastest will depend on the balance of CPU to disk I/O on your system. If CPU is abundant and the disk is slow, a -Fc dump will be faster. If CPU is very limited and the disk is fast, an SQL dump might be faster.

I strongly recommend using -Fc custom format dumps anyway. They're much more flexible, and while you can convert a custom format dump into an SQL format dump you can't do the reverse easily.

Postgresql – pg_dump custom format between postgres versions

In my experience, you can use pg_dump without formatting to backup and restore databases between versions without any issues. This is when using pg_dump to simply generate a SQL file.

example

pg_dump mydb > db.sql

However, when using -Fc as an option

$ pg_dump -Fc mydb > db.dump

You can only restore the dump file with a version of PostgreSQL that's higher i.e. more recent. So if you create the dump file with PostgreSQL 8.1, you can only restore that same file with pg_dump from a 8.1 or more recent version of PostgreSQL.

There may be other elements that must exist on the target server such as plpgsql or postgis, if those same elements exist on the source server.

Here is the documentation from Postgres on pg_dump.

Here is the relative paragraph

Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 7.0 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server.

Best Answer

Related Solutions

Postgresql – pg_dump format => custom vs sql

Postgresql – pg_dump custom format between postgres versions

Related Question