From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Williams <valenceshell@protonmail.com>
cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Re: pg_dump compatibility level / use create view instead of create
 table/rule
In-reply-to: 
 <18dWrRFcmR7qHhsVTvYfGuRT8Tx5LijYQ72SeMWaya_0FiBkS5BFiYTa47R5WVTlADzXAqI85sZgkKPFVdJP2UWsUwJty3KuZ4eZvlVXO-I=@protonmail.com>
References: 
 <NFqxoEi7-8Rw9OW0f-GwHcjvS2I4YQXov4g9OoWv3i7lVOZdLWkAWl9jQQqwEaUq6WV0vdobromhW82e8y5I0_59yZTXcZnXsrmFuldlmZc=@protonmail.com>
 <30565.1570597261@sss.pgh.pa.us>
 <KAJrgQ84Kmh-_zwP7MtMPVbx32uE78hQaiAhxpdylcsiISMqRSL3UUGiJQZlbpkT4L5R6BMj-cCpBqUXK1R34HgUSq1Y3I8QjMpLBACQKDo=@protonmail.com>
 <MzQZmmoeor-gQY3YlRIVMCXpdWHT8oohKzczWGjPrm4tvY8o8S74QYm_kOcKMztQvME5vhi6MQy8eVZYxtRPAE9uSe30gjTdr2MOYy-L6wE=@protonmail.com>
 <18dWrRFcmR7qHhsVTvYfGuRT8Tx5LijYQ72SeMWaya_0FiBkS5BFiYTa47R5WVTlADzXAqI85sZgkKPFVdJP2UWsUwJty3KuZ4eZvlVXO-I=@protonmail.com>
Comments: In-reply-to Alex Williams <valenceshell@protonmail.com>
	message dated "Wed, 09 Oct 2019 21:39:52 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <17814.1570661198.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 09 Oct 2019 18:46:38 -0400
Message-ID: <17815.1570661198@sss.pgh.pa.us>
Precedence: bulk

Alex Williams <valenceshell@protonmail.com> writes:
> Ugh, sorry again, missed one more part, here is the full error for the c=
reate table in the log:
> pg_restore: [archiver (db)] Error from TOC entry 11240; 1259 42703182 TA=
BLE my_view postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  syntax erro=
r 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