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 using the cmds below.) The view name/column names have 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 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 = '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 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 (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, 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 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 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 try running 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Alex Williams valenceshell(at)protonmail(dot)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 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 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 / 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