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

From: Alex Williams <valenceshell(at)protonmail(dot)com>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: pg_dump compatibility level / use create view instead of create table/rule
Date: 2019-10-09 01:01:39
Message-ID: NFqxoEi7-8Rw9OW0f-GwHcjvS2I4YQXov4g9OoWv3i7lVOZdLWkAWl9jQQqwEaUq6WV0vdobromhW82e8y5I0_59yZTXcZnXsrmFuldlmZc=@protonmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-sql

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Masahiko Sawada 2019-10-09 01:07:05 Re: [HACKERS] Block level parallel vacuum
Previous Message Masahiko Sawada 2019-10-09 00:42:57 Re: [HACKERS] Block level parallel vacuum

Browse pgsql-sql by date

  From Date Subject
Next Message Alex Williams 2019-10-09 01:11:42 Re: pg_dump compatibility level / use create view instead of create table/rule
Previous Message David G. Johnston 2019-10-08 19:26:30 Re: Qusetion re regexexp_split_to_array and last occurence