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

One quick note, on the 9.5.18, while it created a table, it possibly didn't convert it into a view (that pg admin shows it as) as it probably didn't reach the end to apply the rule (I killed the restore manually when I was checking specifically for the view in question and noticed that it created a table instead of a view; unlike 9.2.9 which generated an error.)

Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Tuesday, October 8, 2019 9:01 PM, Alex Williams <valenceshell(at)protonmail(dot)com> wrote:

> Hi,
>
> 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? We are not using anything specific to 9.5 like jsonb columns, so the DDL should be compatible between versions when creating / defining objects, it just the way it's creating them that has changed which is causing us an issue.
>
> 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) I've read this: https://www.postgresql.org/docs/9.5/rules-views.html but haven't fully understood it yet as to when it applies the create view vs create table/rule syntax, as the pg_dump has a combination of both.
>
> On the 9.5.18 server where it has the create table syntax for a view, it creates a table instead of a view.
>
> For the 9.2.9 servers, it generates errors:
>
> pg_restore: [archiver (db)] Error from TOC entry 11240; 1259 42703182 TABLE v_my_view postgres
> LINE 19: ...E ONLY v_my_view REPLICA ID...
> Command was: CREATE TABLE v_my_view(
>
> pg_restore: [archiver (db)] Error from TOC entry 87613; 2618 42703185 RULE _RETURN postgres
> Command was: CREATE RULE "_RETURN" AS
>
> Aside from an upgrade to all the servers, is there anyway in pg_dump to set a compatibility level when dumping the database? I checked here, and I don't think there is: https://www.postgresql.org/docs/9.5/app-pgdump.html
>
> Many thanks in advance.
>
> Alex
>
> Our setup is the following:
> 1. Source Postgresql 9.5 server (pg_dump source)
> PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit
>
> 2. Two 9.2.9 servers (we restore to)
> PostgreSQL 9.2.9 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
>
> 3. One 9.5 (we restore to)
> PostgreSQL 9.5.18 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313
> (Red Hat 4.4.7-23), 64-bit
>
> Sent with [ProtonMail](https://protonmail.com) Secure Email.