PostgreSQL dump and restore

Question

I can't seem to dump/restore on Windows with my database. Lines used and error messages:

pg_dump -h localhost -U postgres --format=c -O -d ue > latest.dump
pg_restore -h localhost -U postgres -d ue latest.dump

Getting this error,

pg_restore: [archiver] input file does not appear to be a valid archive

With this,

pg_dump -h localhost -U postgres -O -d ue > latest.dump
psql -h localhost -U postgres -d ue -f latest.dump

I get this error,

psql:latest.dump:1: ERROR: invalid byte sequence for encoding "UTF8": 0xff

I'm completely stumped. Most of the search results relate to not using the correct pg_restore/psql with the file type exported by pg_dump, but as you can see above, I've accounted for that.

latest.dump seems to be filling up correctly, at least in the plain format it exports SQL that I can read and is the correct export of the database.

• Philᵀᴹ: Have you FTPed it between machines in ASCII mode instead of binary?
• Josh: How do I change the export mode besides --format=? Also, not sure what FTP has to do with it. This simple case dumps and tries to restore to a local Postgresql install. No networking involved.

Answer 1

Apparently the problem was the > operator. Changing that to -f to specify the file for the dump, resolved the problem (using the plain format).

I was writing a Powershell script which may have been the underlying cause. The commands were accepted, but there could have been a corruption issue there.

• Craig Ringer: The problem here is that Windows's command-shell I/O redirection is not 8-bit clean. I'd like pg_dump and pg_restore to refuse to read from and write the terminal, but I don't know how to detect it without preventing other safe uses of stdio/pipes.