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

Hi Tom,

Thanks for your reply, we appreciate it. This is a long reply, sorry about =
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 below u=
sing the cmds below.) The view name/column names have changed for the maili=
ng list:

"CREATE TABLE" -  cat restore_result.txt | grep -A 10 -B 10 -i "CREATE TABL=
E" | more

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..

pg_restore: [archiver (db)] could not execute query: ERROR:  relation "mysc=
hema.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 RULE =
_RETURN postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "my_v=
iew" 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 could=
n't find anything on the properties that it had such a property ... we used=
 the query from here: https://stackoverflow.com/questions/55249431/find-rep=
lica-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 th=
at the view uses that may have that id; I'm not sure what REPLICA ID is use=
d for, but our source DB for the dump has the the wal_level set to hot stan=
dby to sync with another server (same version) without using a dump (for fa=
ilover/readonly report queries.)

Reading this:
https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-replica-ide=
ntity-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, whi=
ch in our case is hot standby not logical, but it looks like 9.2 doesn't su=
pport that property and that could be causing the issue? Also, I see the re=
plication settings in the conf file, but they are all defaulted to being co=
mmented 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_view" =
is still a table, I'm waiting for the restore to complete to see if when th=
e rule is applied, if it hasn't yet, that it shows as a view and returns re=
cords.")

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 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 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 no=
t to use the view rules so that the statement in the pg_dump file uses crea=
te 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 servers=
....we've been doing this for awhile and had no issues until recently with =
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 / c=
reate 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