Postgres Backup fails with: pg_dump: could not fsync file: Bad file descriptor

Question

I am running Postgres 11 on Windows 10 and this should be my last backup before moving to Linux:

"C:\PostgreSQL\pg11\bin\pg_dump.exe" -U postgres -h localhost -p 5432 --verbose --jobs=4 --format=d --blobs --clean --create --if-exists --compress=0 --dbname="DATABASE_NAME" --file="P:\PG11_dump_20190217"

This is the end of the log:

...
pg_dump: dumping contents of table "schema.table"
pg_dump: finished item 255955 TABLE DATA table
pg_dump: finished item 255956 TABLE DATA table
pg_dump: pg_dump: dumping contents of table "schema.table"
dumping contents of table "schema.table"
pg_dump: finished item 255953 TABLE DATA table
pg_dump: finished item 255952 TABLE DATA table
pg_dump: could not fsync file "P:\PG11_dump_20190217/291267.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/293909.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/294060.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/295715.dat": Bad file descriptor
pg_dump: could not fsync file "P:\PG11_dump_20190217/296142.dat": Bad file descriptor

I searched for the pg_dump: could not fsync file: Bad file descriptor error but could not find any help.

Maybe of importance: The database is about 3.6 TB and I am using the PostGIS extension.

Question: How to prevent this error or work around it?

Additional question: Could I do a file level backup and copy the data folder from windows to linux, given an identical Postgres version and both servers stopped?

Answer 1

Question: How to prevent this error or work around it?

fsync ensures that the data is physically written to disk, so that in case of a crash or loss or power a few seconds after the backup, no data is lost in unflushed memory buffers. Unfortunately it doesn't seem to work in your case. You may add --no-sync to avoid this step, although the errors shown are probably harmless, in the sense that surely they don't affect the contents of the backup. You may also use sync as an external program if you're wary of skipping the sync.

Additional question: Could I do a file level backup and copy the data folder from windows to linux, given an identical Postgres version and both servers stopped?

No. The data files are not portable in general, and especially not across totally different operating systems.

Answer 2

Never used postgres on Windows personally, but could this be one of those "confused slashes" situations?

The error message seems a little "conflicted" about which to use:

         -v-
--file="P:/PG11_dump_20190217" 

                                -v-                -v-
pg_dump: could not fsync file "P:\PG11_dump_20190217/291267.dat": Bad file descriptor

Does it dump work any better with this?

         -v-
--file="P:/PG11_dump_20190217"