You may get the following error when attempting to restore a postgreSQL database. The issue will be that you are attemping to restore a dump of a database using PostgreSQL version 8.4 and anove into a PostgreSQL database that is version 8.3 or below.
ERROR: syntax error at or near “COLLATE”
To resolve this issue manually edit the dump file by removing two options that are included in your database CREATE statements by default in Postgres 8.4 and above as shown below.
Before Editing:
- CREATE DATABASE dbname WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8' COLLATE = 'en_US.UTF-8' CTYPE = 'en_US.UTF-8';
- REVOKE ALL ON DATABASE template1 FROM PUBLIC;
- REVOKE ALL ON DATABASE template1 FROM postgres;
- GRANT ALL ON DATABASE template1 TO postgres;
- GRANT CONNECT ON DATABASE template1 TO PUBLIC;
- CREATE DATABASE test WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8' COLLATE = 'en_US.UTF-8' CTYPE = 'en_US.UTF-8';
After Editing:
- CREATE DATABASE dbname WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8';
- REVOKE ALL ON DATABASE template1 FROM PUBLIC;
- REVOKE ALL ON DATABASE template1 FROM postgres;
- GRANT ALL ON DATABASE template1 TO postgres;
- GRANT CONNECT ON DATABASE template1 TO PUBLIC;
- CREATE DATABASE test WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'UTF8';
As you can see the above examples include the creation of two databases from the dump files that were created using pg_dump.
