I’m moving my website to a new hosting provider and they gave me a SQL backup file from the old PostgreSQL database. I want to test everything on my local development environment first but I’m having trouble getting the backup restored properly.
I have PostgreSQL 9 running locally and tried a few different approaches. First I opened pgAdmin III and looked for some kind of restore option but couldn’t find what I needed. Then I tried copying the SQL content directly into the query editor and running it. The tables get created fine but when it tries to insert the actual data I get syntax errors like this:
ERROR: syntax error at or near "t"
LINE 474: t 2011-05-24 16:45:01.768633 2011-05-24 16:45:01.768633 view...
The problematic section looks like:
COPY user_permissions (active, created_at, updated_at, id, language, name, details) FROM stdin;
t 2011-05-24 16:45:01.768633 2011-05-24 16:45:01.768633 view en ...
I also attempted using command line but when I try:
psql myapp_db < C:/backups/database-dump.sql;
I get this error:
ERROR: syntax error at or near "psql"
LINE 1: psql myapp_db < C:/backups/database-dump...
^
What’s the correct way to import this PostgreSQL dump file?