Unable to pg_restore SQL file on remote Linux VM
I am using PostgreSQL as my DB.
I have SCPed a .sql file on my remote Ubuntu VM.
I did sudo su - postgres and create a DB.
then I switch backed to my original account and tried this:
sudo -su postgres pg_restore < temp.sql
The command ran successfully.
But when I again switched back to postgres user and checked the db for the list of tables using \dt I found no tables.
What am I doing wrong?
Answer
'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