Thread: Restoring A Dump As A Normal User

Restoring A Dump As A Normal User

From
"Jeff Flowers"
Date:
I want to be able to dump and restore my personal databases without
having to be the PostgreSQL super user, so I performed the following
step using a test database:

   GOO:~ jeff$ pg_dump -O birthdays > dump.txt

I then dropped the database and did the following:


   GOO:~ jeff$ createdb birthdays
   CREATE DATABASE
   GOO:~ jeff$ psql -d birthdays -f dump.txt
   SET
   SET
   REVOKE
   psql:dump.txt:14: ERROR:  permission denied for schema public
   SET
   CREATE TABLE
   CREATE VIEW
   psql:dump.txt:57: ERROR:  must be owner of schema public


The lines that failed in dump.txt, with line numbers added, are:

14. GRANT ALL ON SCHEMA public TO PUBLIC;
57. COMMENT ON SCHEMA public IS 'Standard public schema';

I looked at the resulting restored birthdays database, and it looks
correct. Do I need to worry about these error messages?

My entire dump.txt file is included below.


Thanks,

Jeff Flowers



-------dump.txt listing-------

GOO:~ jeff$ cat dump.txt
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;

--
-- TOC entry 4 (OID 2200)
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


SET search_path = public, pg_catalog;

--
-- TOC entry 5 (OID 20151)
-- Name: listing; Type: TABLE; Schema: public; Owner: jeff
--

CREATE TABLE listing (
    "last" character varying(30),
    "first" character varying(30),
    bday date
);


--
-- TOC entry 6 (OID 20155)
-- Name: nice; Type: VIEW; Schema: public; Owner: jeff
--

CREATE VIEW nice AS
    SELECT listing."last", listing."first", listing.bday FROM listing
    ORDER BY listing."last", listing."first";


--
-- Data for TOC entry 7 (OID 20151)
-- Name: listing; Type: TABLE DATA; Schema: public; Owner: jeff
--

COPY listing ("last", "first", bday) FROM stdin;
Flowers Zoe     2003-05-06
Flowers Heather 1977-10-15
Flowers Jeff    1967-09-08
\.


--
-- TOC entry 3 (OID 2200)
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';