Re: Programmatically duplicating a schema

> On 13 Mar 2018, at 4:23, matt.figg@internode.on.net wrote:
>
> Hi all,
>
> What is a reliable way to programmatically & generically populate an empty schema with all the objects in the public
schemaas a template? 
>
> We are using the multi tenancy ruby gem Apartment ( https://github.com/influitive/apartment ), which was recently
brokenby the changes made to pg_dump to address CVE-2018-1058 https://nvd.nist.gov/vuln/detail/CVE-2018-1058 
>
> Apartment attempts to duplicate the public schema whenever creating a new schema by running:
>
> pg_dump -s -x -0 -n public
>
> to get the SQL statements needed to recreate the public schema & then executes the pg_dump's sql output after
creating& switching to the new schema ( via set search_path to <new schema>; ) 
>
> After the fix to CVE-2018-1058, all table references in pg_dump's output (including within SQL of stored procedures)
areprefixed by the public. schema, which means you cannot just reuse this output in a different schema context without
firstmanually changing the sql. 
> As a temporary fix so we can handle new customers in production, we are using a regex search/replace for public. in
thepg_dump output, but clearly this is not a reliable solution for a generic gem such as Apartment. 

In my opinion, it makes sense that if you have the option of dumping the contents of a specific schema, it should be
possibleto restore that dump into a different schema. Unfortunately, looking at pg_restore, there does not appear to be
suchan option (yet). 

I'd even go so far to suggest that every single object type that can be dumped with pg_dump (single database, single
schema,single table, single function, etc) should be restorable under a different name. I realise that this could make
pg_restoreoptions potentially more confusing. 

I suppose people currently manually edit the dumps to this effect, but that risks silent corruption of data when for
examplea data value contains a string such as 'The building is now open to public.'. Regular expressions don't know the
differencebetween data and identifiers in a dump file - pg_restore does. 

Whether psql needs the same treatment? I'd qualify this as "advanced" use and limit it to pg_restore.

But then, I'm just a list-lurker, I currently have but the option of voicing my opinion.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.