Database Reference
In-Depth Information
> pg_restore -f chapter10.sql
chapter10.backup
After creating a backup, it is good practice to make sure that the backup is
valid.Wedosowiththe
pg_restore
PostgreSQLtool.The
-f
flaginstructs
pg_restore
toemittherestoredoutputtoafileinsteadofadatabase.The
emitted output comprises standard SQL statements.
3. Use a text editor to view
chapter10.sql
. You should see blocks of SQL
statements for creating tables, filling created tables, and setting privileges
shown as follows:
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
...
CREATE TABLE capolice (
gid serial NOT NULL,
objectid integer,
policestat character varying(8),
...
REVOKE ALL ON SCHEMA postgis FROM PUBLIC;
REVOKE ALL ON SCHEMA postgis FROM
postgres;
GRANT ALL ON SCHEMA postgis TO postgres;
...
4. Becausewebackedupthe
chapter10
databaseusingthecustomformat,
wehaveafine-grainedcontroloverhow
pg_restore
behavesandwhatit
restores. Let's extract only the
public
schema using the
-n
flag as follows:
> pg_restore -f chapter10_public.sql -n
public chapter10.backup
5. If you compare
chapter10_public.sql
to the
chapter10.sql
file ex-
ported in the preceding step, you see that the
postgis
schema is not re-
stored.