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
provideyou 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 using the cmds below.) The view name/column
nameshave changed for the mailing list: 

"CREATE TABLE" -  cat restore_result.txt | grep -A 10 -B 10 -i "CREATE TABLE" | 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 at or near "REPLICA"
LINE 19: ...E ONLY my_view REPLICA ID..

pg_restore: [archiver (db)] could not execute query: ERROR:  relation "myschema.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_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 couldn't find anything on the properties that it
hadsuch 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 = '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
notsure what REPLICA ID is used for, but our source DB for the dump has the the wal_level set to hot standby to sync
withanother 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-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,
butit looks like 9.2 doesn't support that property and that could be causing the issue? Also, I see the replication
settingsin 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 db from 9.5.5 to 9.5.18 at the moment to see if
itworks (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 use the Fc switches that we normally use, and
tryrunning them manually. 

Thanks again for your help!

Alex


Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Wednesday, October 9, 2019 1:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alex Williams valenceshell@protonmail.com 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
statementin 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 servers....we've been doing this for awhile and had
noissues until recently with certain views that are trying to be restored with rule views (some views in the pg_dump
fileare 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