On the command line, perform the following steps:
- Even though a backup file was created in this chapter's third recipe, create a new backup file by executing the following command:
> pg_dump -U me -f chapter10.backup -F custom chapter10
- Create a new database to which the backup file will be restored by executing the following commands:
> psql -d postgres -U me
postgres=# CREATE DATABASE new10;
- Connect to the new10, database and create a postgis schema as follows:
postgres=# \c new10
new10=# CREATE SCHEMA postgis;
- Execute the CREATE EXTENSION command to install the Postgis extension in the postgis schema:
new10=# CREATE EXTENSION postgis WITH SCHEMA postgis;
- Make sure you set the search_path parameter to include the postgis schema, as follows:
new10=# ALTER DATABASE new10 SET search_path = public, postgis;
- Restore only the public schema from the backup file to the new10 database by executing the following command:
> pg_restore -U me -d new10 --schema=public chapter10.backup
- The restore method runs and should not generate errors. If it does, an error message such as the following will appear:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3781; 03496229
TABLE DATA prism postgres
pg_restore: [archiver (db)] COPY failed for table "prism":
ERROR: function st_bandmetadata(postgis.raster, integer[])
does not exist
LINE 1: SELECT array_agg(pixeltype)::text[]
FROM st_bandmetadata($1...
We have now installed PostGIS in the postgis schema, but the database server can't find the ST_BandMetadata() function. If a function cannot be found, it is usually an issue with search_path. We will fix this issue in the next step.
- Check what pg_restore actually does by executing the following command:
pg_restore -f chapter10.sql --schema=public chapter10.backup
- Looking at the COPY statement for the prism table, everything looks fine. But the search_path method preceding the table does not include the postgis schema as shown here:
SET search_path = public, pg_catalog;
- Change the search_path value in chapter10.sql to include the postgis schema by executing the following command:
SET search_path = public, postgis, pg_catalog;
- Run chapter10.sql with psql, as follows; the original chapter10.backup file can't be used because the necessary change can't be applied to pg_restore:
> psql -U me -d new10 -f chapter10.sql