I have used pg_dump on one machine and copied result file to another, where I tried to restore it. I believe schema is the same. However, I get:
pg_restore: [archiver] input file does not appear to be a valid archive
I have done following operations:
pg_dump -a -f db.txt dbname
and:
pg_restore -a -d dbname db.txt
What might be wrong?
pg_dump by default creates the sql commmands necessary to recreate the data. To recover it, you just need to invoke psql (not pg_restore ) with the file as input . pg_restore is only to be used for the binary (not default, and less usual not recommended) format of pg_dump. Read the docs.
Update: The pg_dump binary formats (-Fc -Ft) that are to be used with pg_restore are ok, and offer some extra flexibility. But they are less standard (non SQL), less apt for importing from some tools (eg. a php frontend) or manipulate with a text editor, and a little less portable to other versions and even other databases. For backups, I'd stick with the default plain format. For other scenarios, the binary + pg_restore option can be equally or more apt.
The point to keep is that in Postgresql, in the typical scenario, the backup normally is done by pg_dump (plain) and the restore with the standard command line client (psql).
Try passing the --format=c option to pg_dump. This will allow pg_restore to restore it.
You are dumping in plain sql format which was designed to feed to psql. This is not recognized by pg_restore.
cat db.txt | psql dbname
should do the trick