I am using PostgreSQL as my DB.
I have SCPed a .sql file on my remote Ubuntu VM.
sudo su - postgres
sudo -su postgres pg_restore < temp.sql
'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.
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