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

Sorry, there was a bit more that after reviewing again what I sent, I misse=
d copying from the "CREATE TABLE" 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 a=
t 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...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "mysc=
hema.my_view" does not exist


Sent with ProtonMail Secure Email.

=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90 Original Me=
ssage =E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90
On Wednesday, October 9, 2019 5:32 PM, Alex Williams <valenceshell@protonma=
il.com> wrote:

> Hi Tom,
>
> Thanks for your reply, we appreciate it. This is a long reply, sorry abou=
t that, but if there's any specific I can provide you that helps, please le=
t me know.
>
> OK, for the log, we do this when restoring:
>
> pg_restore -d my_database -U postgres my_database.dump >restore_result.tx=
t 2>&1
>
> but our log file only records the following (I've added more detail below=
 using the cmds below.) The view name/column names have changed for the mai=
ling list:
>
> "CREATE TABLE" - cat restore_result.txt | grep -A 10 -B 10 -i "CREATE TAB=
LE" | more
>
> pg_restore: [archiver (db)] Error from TOC entry 11240; 1259 42703182 TAB=
LE 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..
>
> pg_restore: [archiver (db)] could not execute query: ERROR: relation "mys=
chema.my_view " does not exist
>
> "CREATE RULE" - cat restore_result.txt | grep -A 10 -B 10 -i "CREATE RULE=
" | more
> pg_restore: [archiver (db)] Error from TOC entry 87618; 2618 42703185 RUL=
E _RETURN postgres
> pg_restore: [archiver (db)] could not execute query: ERROR: relation "my_=
view" does not exist
> Command was: CREATE RULE "_RETURN" AS
> ON SELECT TO my_view DO INSTEAD SELECT DISTINCT d.name AS p...
>
> We assumed it was the create rule but also looked at "REPLICA ID" and cou=
ldn't find anything on the properties that it had such a property ... we us=
ed the query from here: https://stackoverflow.com/questions/55249431/find-r=
eplica-identity-for-a-postgres-table
>
> SELECT CASE relreplident
> WHEN 'd' THEN 'default'
> WHEN 'n' THEN 'nothing'
> WHEN 'f' THEN 'full'
> WHEN 'i' THEN 'index'
> END AS replica_identity
> FROM pg_class
> WHERE oid =3D 'my_view'::regclass;
>
> and it returned nothing. But I'm wondering could it be any of the tables =
that the view uses that may have that id; I'm not sure what REPLICA ID is u=
sed for, but our source DB for the dump has the the wal_level set to hot st=
andby to sync with another server (same version) without using a dump (for =
failover/readonly report queries.)
>
> Reading this:
> https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-replica-i=
dentity-logical-replication/
>
> and this
>
> https://www.postgresql.org/docs/devel/sql-altertable.html
>
> I'm not sure what config param would set that other than the wal_level, w=
hich in our case is hot standby not logical, but it looks like 9.2 doesn't =
support that property and that could be causing the issue? Also, I see the =
replication settings in the conf file, but they are all defaulted to being =
commented out.
>
> So I'm still not sure what it could be. I'm in process of restoring the d=
b from 9.5.5 to 9.5.18 at the moment to see if it works (currently "my_view=
" is still a table, I'm waiting for the restore to complete to see if when =
the rule is applied, if it hasn't yet, that it shows as a view and returns =
records.")
>
> I'll see if I can extract the statements from another dump that doesn't u=
se the Fc switches that we normally use, and try running them manually.
>
> Thanks again for your help!
>
> Alex
>
> Sent with ProtonMail Secure Email.
>
> =E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90 Original =
Message =E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90=E2=80=90
> On Wednesday, October 9, 2019 1:01 AM, Tom Lane [email protected] wrote:
>
> > Alex Williams [email protected] writes:
> >
> > > Can someone let me know when you're doing a pg_dump, can you specify =
not to use the view rules so that the statement in the pg_dump file uses cr=
eate view instead of create table/create rule?
> >
> > No.
> >
> > > We dump from 9.5.5 and restore to one 9.5.18 server and two 9.2 serve=
rs....we've been doing this for awhile and had no issues until recently wit=
h certain views that are trying to be restored with rule views (some views =
in the pg_dump file are created with create view and some by create table /=
 create rule)
> >
> > In general, we don't promise that pg_dump output from major version N
> > can be loaded into previous major versions. Having said that, 9.2
> > should not have a problem with either the CREATE VIEW or CREATE TABLE-
> > plus-CREATE RULE approaches per se, so there's some critical detail
> > that you haven't told us about. You didn't show the actual error
> > messages, either.
> > regards, tom lane