How can I restore pg_dump file on vm

Question

I created a dump with pg_dump using,

sudo -u postgres pg_dump dbname > dbexport.psql

I tried to restore that dump with,

sudo -u postgres pg_restore -d dbname < dbexport.psql

I got an error in doing the restore

pg_restore: [archiver] input file appears to be a text format dump. Please use psql.

After that I enter the following

sudo -i -u postgres psqldbname < dbexport.psql

I don't see anything, just a new line waiting for more input. There's no error message and all the tables are still empty. Just a new line waiting for more input. Can someone tell me what I'm doing wrong?

Answer 1

The problem here is that you likely have an underlying file system problem. If you pipe the file into psql by redirecting STDIN with < file, you lose the ability to determine things about the stream (like the line number). You also rely on the shell to tell you the errors, rather than psql. Instead try using the -f flag

psql myDatabase -f myDump

---

Just for more context here, try this:

cat < file_does_not_exist

You'll see something like this,

$ cat < file_does_not_exist
zsh: no such file or directory: file_does_not_exist

That error is coming from zsh! And your shell if not zsh, may not produce the same error. It may just block and wait for the file to be created, or to get access to the file.

Answer 2

Despite conventional wisdom, I suspect this is a situation where a screencap would help. It sounds to me like you've misunderstood how to invoke the psql shell.

Does your terminal look something like this?

Your command sudo -i -u postgres psql entered you into an interactive psql session. TL;DR: you don't need to preface everything with psql anymore.

From outside (in your default bash, zsh, or other), you can use the < character (among other options like [-f|--file] or a pipeline) to input file content to psql.

Inside an interactive session however, use the \i (\include) meta-command to input a file.

Answer 3

Was able to get it done by creating custom backup file & then restoring it. To create the backup

sudo -u postgres pg_dump -Fc sportsapp > mydump.dump

to restore the backup

sudo -u postgres pg_restore -d sportsapp < mydump.dump

I did get a warning pg_restore: [custom archiver] WARNING: ftell mismatch with expected position -- ftell used so I'll have to deal with that but at least the rows are no longer empty...