Search code examples
databasepostgresqldatabase-backups

Postgresql: Cannot restore schema from pg_dump

I am trying to move a schema from one database to another using pg_dump. I am running this command to take a backup of my schema

pg_dump.exe -h myhost -U postgres -d mydb-n schema> C:\Temp\schema.dump

To restore it I copy the SQL into pgadmin, but I get this error. I don't know what the issue is ?

ERROR:  syntax error at or near "2"
LINE 284: 2 \N  Unregistered. Item  \N  2016-07-13 00:00:00 \N

Is there a best recommended way to backup/restore a postgres database? I often run into issues similar issues when backing up/restoring databases and I start to worry that I will loose valuable data one day.

Solution

  • A plain text pg_dump cannot be restored with pgAdmin since pgAdmin cannot perform COPY FROM STDIN, which is used by pg_dump for the data by default.

    You'll have to use psql to restore the dump.

    An alternative would be to use the --inserts option of pg_dump so that the data are dumped as INSERT statrments. This comes with a performance penalty though.