Re: pg_dump compatibility level / use create view instead of create table/rule

Alex Williams <valenceshell(at)protonmail(dot)com> writes:
> Ugh, sorry again, missed one more part, here is the full error for the create table in the log:
> pg_restore: [archiver (db)] Error from TOC entry 11240; 1259 42703182 TABLE my_view postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "REPLICA"
> LINE 19: ...E ONLY my_view REPLICA ID...
> ^
> Command was: CREATE TABLE my_view (
> product character varying(255),
> product_id integer,
> payer...

This seems to be a chunk of a command like

ALTER TABLE ONLY my_view REPLICA IDENTITY FULL;

(or possibly REPLICA IDENTITY NOTHING), which pg_dump will emit if the
table has a non-default relreplident setting. I do not, however,
understand your statement that this is a view. AFAIK views should never
have non-default relreplident settings, and besides that, the TOC entry
description says it's a table not a view. (If it's a materialized view,
it could have relreplident, but its TOC entry still shouldn't say TABLE.)

Anyway it's hardly surprising that 9.2 is choking on that syntax; it
doesn't have the REPLICA IDENTITY feature.

pg_dump actually is taking some pity on you here, in that it's emitting
this as a separate ALTER TABLE command, not as part of CREATE TABLE
directly. This means you just need to get 9.2 to ignore the error
on the ALTER TABLE and keep plugging. I think what you need to do
is something like pg_restore to stdout and then pipe stdout to psql,
rather than connecting directly to the target server.

Another fix, if this table was only accidentally labeled with
a replica identity (which I'm suspecting because you don't seem
to recognize the feature), is to get rid of the marking in the
source database:

ALTER TABLE ONLY my_view REPLICA IDENTITY DEFAULT;

regards, tom lane