Moshe Jacobson <moshe@neadwerx.com> writes:
> I can pg_dump our prod database and pg_restore it onto our dev server with
> zero errors, but once I try to pg_dump the dev copy again, that's when I
> receive the errors above.
> I didn't drop & recreate the extension to fix it on prod -- I just created
> a new version of it and updated it.
Well, I think you did it wrong, or else you're using a PG version that
predates some necessary fix, because it works for me. I made a simple
extension containing
CREATE TABLE mytable (data text, id serial primary key);
SELECT pg_catalog.pg_extension_config_dump('mytable', '');
SELECT pg_catalog.pg_extension_config_dump('mytable_id_seq', '');
and did
tseq=# create extension myext ;
CREATE EXTENSION
tseq=# \dx+ myext
Objects in extension "myext"
Object Description
-------------------------
sequence mytable_id_seq
table mytable
(2 rows)
tseq=# insert into mytable values ('foo');
INSERT 0 1
tseq=# insert into mytable values ('bar');
INSERT 0 1
and now pg_dump gives me
---------------
--
-- Name: myext; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS myext WITH SCHEMA public;
--
-- Name: EXTENSION myext; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION myext IS 'testing 1,2,3,4';
SET search_path = public, pg_catalog;
--
-- Data for Name: mytable; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY mytable (data, id) FROM stdin;
foo 1
bar 2
\.
--
-- Name: mytable_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--
SELECT pg_catalog.setval('mytable_id_seq', 2, true);
---------------
which is what I'd expect.
To debug, you might try looking in pg_extension to see if the extconfig
entry for your extension includes the OID of the sequence. If not, you
messed up somehow in updating the extension. If so, you must need a
newer version of pg_dump (you did not answer the question what version
you're using).
regards, tom lane
