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

Ugh, sorry again, missed one more part, here is the full error for the crea=
te 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 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
    Command was: ALTER TABLE myschema.my_view OWNER TO postgres;


But you can see, it doesn't show the whole statement, it uses an ellipses a=
fter a certain amount of lines/chars.

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:34 PM, Alex Williams <valenceshell@protonma=
il.com> wrote:

> Sorry, there was a bit more that after reviewing again what I sent, I mis=
sed copying from the "CREATE TABLE" log:
>
> 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...
> ^
> Command was: CREATE TABLE my_view (
> product character varying(255),
> product_id integer,
> payer...
> pg_restore: [archiver (db)] could not execute query: ERROR: relation "mys=
chema.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 =
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 5:32 PM, Alex Williams valenceshell@protonm=
ail.com wrote:
>
> > Hi Tom,
> > Thanks for your reply, we appreciate it. This is a long reply, sorry ab=
out that, but if there's any specific I can provide you that helps, please =
let me know.
> > OK, for the log, we do this when restoring:
> > pg_restore -d my_database -U postgres my_database.dump >restore_result.=
txt 2>&1
> > but our log file only records the following (I've added more detail bel=
ow using the cmds below.) The view name/column names have changed for the m=
ailing list:
> > "CREATE TABLE" - cat restore_result.txt | grep -A 10 -B 10 -i "CREATE T=
ABLE" | more
> > pg_restore: [archiver (db)] Error from TOC entry 11240; 1259 42703182 T=
ABLE 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..
> > pg_restore: [archiver (db)] could not execute query: ERROR: relation "m=
yschema.my_view " does not exist
> > "CREATE RULE" - cat restore_result.txt | grep -A 10 -B 10 -i "CREATE RU=
LE" | more
> > pg_restore: [archiver (db)] Error from TOC entry 87618; 2618 42703185 R=
ULE _RETURN postgres
> > pg_restore: [archiver (db)] could not execute query: ERROR: relation "m=
y_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 c=
ouldn't find anything on the properties that it had such a property ... we =
used the query from here: https://stackoverflow.com/questions/55249431/find=
-replica-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 table=
s that the view uses that may have that id; I'm not sure what REPLICA ID is=
 used for, but our source DB for the dump has the the wal_level set to hot =
standby to sync with another server (same version) without using a dump (fo=
r failover/readonly report queries.)
> > Reading this:
> > https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-replica=
-identity-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,=
 which 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 th=
e replication settings in the conf file, but they are all defaulted to bein=
g commented out.
> > So I'm still not sure what it could be. I'm in process of restoring the=
 db from 9.5.5 to 9.5.18 at the moment to see if it works (currently "my_vi=
ew" is still a table, I'm waiting for the restore to complete to see if whe=
n the rule is applied, if it hasn't yet, that it shows as a view and return=
s records.")
> > I'll see if I can extract the statements from another dump that doesn't=
 use 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 Origina=
l 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 specif=
y not to use the view rules so that the statement in the pg_dump file uses =
create 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 ser=
vers....we've been doing this for awhile and had no issues until recently w=
ith certain views that are trying to be restored with rule views (some view=
s 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