Unable to pg_restore SQL file on remote Linux VM

'pg_restore' is meant to restore files generated by 'pg_dump'.

From the man page

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump(1) in one of the non-plain-text formats.

https://www.postgresql.org/docs/9.2/static/app-pgrestore.html

If your file was generated by pg_dump you probably need to at least tell it which database to dump into:

pg_restore -d my_new_database temp.sql

My own experience with pg_restore across different flavors shows that many times I need to specify the format of the dump file, even if it was in 'native' format, despite the manpage indicating that it would detect the format.

pg_restore -d my_new_database -Fc temp.dump

This is only a guess, but I'm thinking if the tables actually restored, without specifying the db, they got dumped into the default database. You can check this by listing the tables in the 'postgres' database (there should be none).

postgres=#\c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#\dt
No relations found.

If your tables did restore into the default database they will be listed.

Plain text SQL files need to be treated differently, usually executed via SQL commands using psql.

psql -d my_database < temp.sql

Assuming you generated your database backup similar to this:

pg_dump -a --inserts databasename > exportfilename.sql

Try restoring your file like this:

psql databasename -f exportfilename.sql

Postgres pg_restore as mentioned above is only meant to be used with dump files, you can't restore it like that. Check for this answer in the official postgres.org website

Basically you don't use pg_restore with sql files ---> https://www.postgresql.org/message-id/AANLkTi%3DAqmWrUR4f8%2BEfCHzP%2BQrL1%3DunRLZp_jX7SoqF%40mail.gmail.com