search

denishpatel / pg-clone-schema

Postgres clone schema utility without need of going outside of database. Makes developers life easy by running single function to clone schema with all objects. It is very handy on Postgres RDS. Utility is sponsored by http://elephas.io/
MIT License
172 stars 44 forks source link

Multiple issues cloning views #133

Open samodadela opened 2 weeks ago

samodadela commented 2 weeks ago

Hi,

Found some problems when running pg-clone-schema on my project:

I made the script 'work' on my schema. But I had to comment out the whole 'Create Materialized views' part - probably loosing a lot of needed functionality. I don't know how to fix it properly.

I'm attaching the modified script. Please compare ignoring white space changes, I messed it up. It was based on rev 164279a. clone_schema_mod.sql.gz

samodadela commented 2 weeks ago

Note: I removed the 'Create Materialized views' because the materialized view is created in the same place as are normal views. If i don't comment it out, that code will try to create it again and fail because the materialized view already exists.

MichaelDBA commented 1 week ago

@samodadela I think I have fixed it. Please try the latest code. Not understanding the 2nd bullet about some views are not being enumerated at all. So you are saying some view defs in the source schema are not being cloned?

samodadela commented 4 days ago

Thanks @MichaelDBA I'll give it a spin.

Regarding the 2nd bullet, I was referring to this change:

image

Some of my views have deptype 'i' and so are missed by pg-clone-schema.

samodadela commented 4 days ago

I tested the latest version.

I had to change the code as in my latest post (add IN ('n', 'i'), line 2819). Then the cloning of the schema works using 'NODATA'.

If I clone the schema using 'DATA' it fails, complaining that the materialized view does not exist. Here are the logs:

db.public> select clone_schema('t_src', 't_clone_schema_data', 'DATA', 'VERBOSE')
                           clone_schema version 2.2 March 05, 2024
Linux: PostgreSQL 16.4 (Ubuntu 16.4-0ubuntu0.24.04.2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
                    COLLATIONS cloned:     0
                    DOMAINS cloned:     0
                    TYPES cloned:     2
                    SEQUENCES cloned:   154
                    TABLES cloned:   246
                    [2024-10-07 10:35:09] [P0001] ERROR: Version: 2.2 March 05, 2024  Action: Mat. Views  SearchPath: public  oldSP=public  newSP=t_logineko  Diagnostics: line=PL/pgSQL function clone_schema(text,text,cloneparms[]) line 2029 at EXECUTE. 42P01. relation "t_clone_schema_data.materialized_view" does not exist
    [2024-10-07 10:35:09] Where: PL/pgSQL function clone_schema(text,text,cloneparms[]) line 2965 at RAISE
    SEQUENCES set:     154
    IDENTITIES set:       0
    FUNCTIONS cloned:     1
dependent view count=0 for view, view1
dependent view count=0 for view, view2
...
dependent view count=0 for view, view22
VIEWS cloned:     0
MAT VIEWS cloned:     1
MichaelDBA commented 1 hour ago

Validated it is fixed for NODATA, but still needs to be fixed for DATA case...

MichaelDBA commented 14 minutes ago

@samodadela , please try the latest code, think i got it fixed for NODATA and DATA cases...