Re: Cloning schemas

On 07/09/2018 03:23 PM, Adrian Klaver wrote: On 07/09/2018 02:50 PM, Melvin Davidson wrote: Adrian, The problem is that the relname/object has changed in the new schema. In this case from text_idx --> idx_test_id_idx. So this happens: test_(postgres)# comment on index sch_test.test_idx i

Re: Cloning schemas

Dias, As an experiment, I commented out the code that creates the comment on indexes and it still works flawlessly, so that part is redundant. I have attached the modified function below, Please retry and see if the problem still exists. If it does, then please do a schema only pg_dump of the sour

Re: Cloning schemas

On 07/09/2018 02:50 PM, Melvin Davidson wrote: Adrian, The code that CREATES the TABLE is EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object)     || ' INCLUDING ALL)'; The schema names are supposed to be changed! This function HAS

Re: Cloning schemas

Hi Melvin, I followed your recommendation and it did not work. Since I was in a rush I did try to understand where the function crashed and commenting in the function the creation of comments for indexes, as follows, was sufficient for the function work.     IF FOUND   THEN --    EXEC

Re: Cloning schemas

Adrian, The code that CREATES the TABLE is EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) || ' INCLUDING ALL)'; The schema names are supposed to be changed! This function HAS been tested and does WORK. Please do not muddle the p

Re: Cloning schemas

On 07/09/2018 09:49 AM, Melvin Davidson wrote: On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta > wrote: Hi Melvin, Trying run 9.6 clone_schema on a different schema and I get the following error: NOTICE:  search path = {public,pg_catalog} CONTE

Re: Cloning schemas

On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta wrote: > Hi Melvin, > > Trying run 9.6 clone_schema on a different schema and I get the following > error: > > NOTICE: search path = {public,pg_catalog} > CONTEXT: PL/pgSQL function clone_schema(text,text,boolean) line 79 at > RAISE > ERROR: relation

Re: Cloning schemas

Hi Melvin, Trying run 9.6 clone_schema on a different schema and I get the following error: NOTICE:  search path = {public,pg_catalog} CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean) line 79 at RAISE ERROR:  relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does not exist

Re: Cloning schemas

On Mon, Jul 9, 2018 at 5:14 AM, Łukasz Jarych wrote: > Hi Melvin, > > i am trying to run postgresql 10 cloning schema function but still i am > getting error... > > [image: image.png] > > Error: Error in syntax near "SYSTEM" > Context: Function PL/pgSQL, row 212 in EXECUTE > > What is happening?

Re: Cloning schemas

Hi Melvin, i am trying to run postgresql 10 cloning schema function but still i am getting error... [image: image.png] Error: Error in syntax near "SYSTEM" Context: Function PL/pgSQL, row 212 in EXECUTE What is happening? Best, Jacek sob., 7 lip 2018 o 22:20 Melvin Davidson napisał(a): > >

Re: Cloning schemas

2018-07-07 4:32 GMT-04:00 DiasCosta : > Hi Melvin, > > Thank you. > > Dias Costa > > On 04-07-2018 23:38, Melvin Davidson wrote: > > > > On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta wrote: > >> Hi Melvin, >> >> I'm new to clone_schema. >> Can I use it on PostgreSQL 9.6? >> >> TIA >> DCostaployment b

Re: Cloning schemas

Hi Melvin, Thank you. Dias Costa On 04-07-2018 23:38, Melvin Davidson wrote: On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta > wrote: Hi Melvin, I'm new to clone_schema. Can I use it on PostgreSQL 9.6? TIA DCostaployment by invitation only!

Re: Cloning schemas

Melvin, thank you once again ! Yes and this is working like a charm, I love your function and file ! Best, Jacek czw., 5 lip 2018 o 16:53 Melvin Davidson napisał(a): > > > > On Thu, Jul 5, 2018 at 10:38 AM, Łukasz Jarych wrote: > >> You gave me working example. >> >> the function from here is

Re: Cloning schemas

On Thu, Jul 5, 2018 at 10:38 AM, Łukasz Jarych wrote: > You gave me working example. > > the function from here is not working: > > https://www.postgresql.org/message-id/CANu8FiyJtt-0q% > 3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com > > Best, > Jacek > > czw., 5 lip 2018 o 16:02 Melvin

Re: Cloning schemas

You gave me working example. the function from here is not working: https://www.postgresql.org/message-id/CANu8FiyJtt-0q%3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com Best, Jacek czw., 5 lip 2018 o 16:02 Melvin Davidson napisał(a): > > > On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych

Re: Cloning schemas

On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych wrote: > From link function is not working. > There is no " From link" in PostgreSQL, Would you please be more specific. Please provide a working example.

Re: Cloning schemas

>From link function is not working. czw., 5 lip 2018 o 07:49 Łukasz Jarych napisał(a): > Hi Melvin. > > folks wrote only that it is old version and didnt care :) > > Tahnk you very much, > Best, > Jacek > > czw., 5 lip 2018 o 01:09 Melvin Davidson > napisał(a): > >> >> >The folks that wanted tr

Re: Cloning schemas

Hi Melvin. folks wrote only that it is old version and didnt care :) Tahnk you very much, Best, Jacek czw., 5 lip 2018 o 01:09 Melvin Davidson napisał(a): > > >The folks that wanted transactional ALTER SEQUENCE might disagree:): > Ah, so you mean the previous version was not working or suffici

Re: Cloning schemas

>The folks that wanted transactional ALTER SEQUENCE might disagree:): Ah, so you mean the previous version was not working or sufficient? https://www.postgresql.org/docs/9.6/static/sql-altersequence.html -- *Melvin Davidson* *Maj. Database & Exploration Specialist* *Universe Exploration Command

Re: Cloning schemas

On 07/04/2018 03:53 PM, Melvin Davidson wrote: The problem is, AFAICS, none of the changes induced were really necessary or increased performance. The folks that wanted transactional ALTER SEQUENCE might disagree:): https://www.postgresql.org/docs/10/static/release-10.html "Move sequen

Re: Cloning schemas

On Wed, Jul 4, 2018 at 6:48 PM, Adrian Klaver wrote: > On 07/04/2018 03:38 PM, Melvin Davidson wrote: > >> >> >> On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta > > wrote: >> >> Hi Melvin, >> >> I'm new to clone_schema. >> Can I use it on PostgreSQL 9.6? >> >

Re: Cloning schemas

On 07/04/2018 03:38 PM, Melvin Davidson wrote: On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta > wrote: Hi Melvin, I'm new to clone_schema. Can I use it on PostgreSQL 9.6? TIA DCostaployment by invitation only! > Can I use it on PostgreSQL 9.

Re: Cloning schemas

On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta wrote: > Hi Melvin, > > I'm new to clone_schema. > Can I use it on PostgreSQL 9.6? > > TIA > DCostaployment by invitation only! > > Can I use it on PostgreSQL 9.6? Yes, but because the developer(s) once again monkeyed with the system catalogs, there are

Re: Cloning schemas

Hi Melvin, I'm new to clone_schema. Can I use it on PostgreSQL 9.6? TIA DCosta On 03-07-2018 14:34, Melvin Davidson wrote: ERROR: BŁĄD: you cannot add to column "TopoToVersion_ID" DETAIL: Column "TopoToVersion_ID" is an identity column defined as GENERATED ALWAYS. HINT: Use OV

Re: Cloning schemas

On Wed, Jul 4, 2018 at 2:27 PM, Łukasz Jarych wrote: > Hi Melvin, > > > > Best, > Jacek > > > wt., 3 lip 2018 o 15:34 Melvin Davidson napisał(a): > >> >> >> >>> ERROR: BŁĄD: you cannot add to column "TopoToVersion_ID" DETAIL: Column >>> "TopoToVersion_ID" is an identity column defined as GENERAT

Re: Cloning schemas

Hi Melvin, thank you very much. Awesome!!! Best, Jacek wt., 3 lip 2018 o 15:34 Melvin Davidson napisał(a): > > > >> ERROR: BŁĄD: you cannot add to column "TopoToVersion_ID" DETAIL: Column >> "TopoToVersion_ID" is an identity column defined as GENERATED ALWAYS. HINT: >> Use OVERRIDING SYSTEM V

Re: Cloning schemas

> ERROR: BŁĄD: you cannot add to column "TopoToVersion_ID" DETAIL: Column > "TopoToVersion_ID" is an identity column defined as GENERATED ALWAYS. HINT: > Use OVERRIDING SYSTEM VALUE to override. CONTEXT: wyrażenie SQL "INSERT > INTO version4.t_topotoversions SELECT * FROM public.t_topotoversions;"

Re: Cloning schemas

Hi Melvin, I understand this but i can not update function by myself. Thnak you very much ! Still errors here. Something like: ERROR: BŁĄD: you cannot add to column "TopoToVersion_ID" DETAIL: Column "TopoToVersion_ID" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTE

Re: Cloning schemas

On Mon, Jul 2, 2018 at 10:14 AM, Łukasz Jarych wrote: > > Hi, > > i see. thank you > > I am using: > > [image: image.png] > > Best, > Jacek > > pon., 2 lip 2018 o 16:03 Adrian Klaver > napisał(a): > >> On 07/02/2018 06:57 AM, Łukasz Jarych wrote: >> > Hi, >> > >> > " >> > Strange. "audit_sq" loo

Re: Cloning schemas

Hi, i see. thank you I am using: [image: image.png] Best, Jacek pon., 2 lip 2018 o 16:03 Adrian Klaver napisał(a): > On 07/02/2018 06:57 AM, Łukasz Jarych wrote: > > Hi, > > > > " > > Strange. "audit_sq" looks like an invalid sequence table. I went > > here,https://www.postgresql.org/docs/9.

Re: Cloning schemas

On 07/02/2018 06:57 AM, Łukasz Jarych wrote: Hi, " Strange. "audit_sq" looks like an invalid sequence table. I went here,https://www.postgresql.org/docs/9.6/static/sql-createsequence.html, and checked all the way back to version 7.1 and "maxvalue" has been a column since back then. What ver

Re: Cloning schemas

Hi, " Strange. "audit_sq" looks like an invalid sequence table. I went here, https://www.postgresql.org/docs/9.6/static/sql-createsequence.html, and checked all the way back to version 7.1 and "maxvalue" has been a column since back then. Maybe skip that table for now? It even says the last value

Re: Cloning schemas

=?UTF-8?Q?=C5=81ukasz_Jarych?= writes: > I am trying to use : > "select * from clone_schema('public','Version8',true) but i am getting > error: > "Column "max_value" does not exist. > LINE 1: SELECT last_value, max_value, start_value, increment_by, min... > HINT: Maybe you wanted to point to col

Re: Cloning schemas

Hi , thank you ! You have right: [image: image.png] Hmm i thought that i am creating this table " audit_sq " within clone schema and this function is complete. How can i fix this? Best, Jacek pon., 2 lip 2018 o 13:51 Victor Noagbodji napisał(a): > Hello, > > Can you check if the table "

Cloning schemas

Hi, i am trying to use postgresql clone schema function: https://www.postgresql.org/message-id/CANu8FiwiBiAjYgdehYBkJcSRFd6ZFFnN5kDJE7TG4rad5BNXZQ%40mail.gmail.com I created function clone_schema in public schema: [image: image.png] I am trying to use : "select * from clone_schema('public','

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

On Tue, Mar 13, 2018 at 1:47 PM, Melvin Davidson wrote: > > > >> Thank you Melvin, I forgot to mention I've already found your script >> before I asked here, but I didn’t think it was robust enough (please don't >> offend :-). Particularly, it didn't work well on PostgreSQL 10. >> >> > Aldrin, >

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

> Thank you Melvin, I forgot to mention I've already found your script > before I asked here, but I didn’t think it was robust enough (please don't > offend :-). Particularly, it didn't work well on PostgreSQL 10. > > Aldrin, I apologize. I just tested and found that the reason it is failing is be

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

> On Mar 9, 2018, at 12:15 PM, Melvin Davidson wrote: > On Fri, Mar 9, 2018 at 10:00 AM, Aldrin Martoq Ahumada > mailto:aldrin.mar...@gmail.com>> wrote: > Yes, here is the issue: https://github.com/influitive/apartment/issues/532 > > It happ

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

On Fri, Mar 9, 2018 at 10:00 AM, Aldrin Martoq Ahumada < aldrin.mar...@gmail.com> wrote: > Hi Andre, > > Yes, here is the issue: https://github.com/influitive/apartment/issues/532 > > It happens if you configured apartment with use_sql=true, which means it > clones the schema from pg_dump. My firs

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

Hi Andre, Yes, here is the issue: https://github.com/influitive/apartment/issues/532 It happens if you configured apartment with use_sql=true, which means it clones the schema from pg_dump. My first attempt was to “fix” the script generated b

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

Hello Aldrin, I'm also using apartment with postgresql 9.6.6, and I don't see any issue with it. Are you using Apartment::Tenant.create? 2018-03-09 10:26 GMT-03:00 Aldrin Martoq Ahumada : > Hi, > > For a multi tenant system, we are using the following command to blindly > clone a schema into anot

Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

On Fri, Mar 9, 2018 at 6:26 AM, Aldrin Martoq Ahumada < aldrin.mar...@gmail.com> wrote: > Thinking in the long term, how could be the best way to clone a schema > into another? > Depends on why you are cloning schemas. Generally not cloning is the best bet - instead place the r

Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

Hi, For a multi tenant system, we are using the following command to blindly clone a schema into another: pg_dump -s -x -O -n #{default_tenant} #{dbname} This is done for us by a rails gem, which then feeds that script into the new created schema for the new tenant. https://github.com/influiti