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