Thread

pg_restore remap schema

Fabrice Chapuis <fabrice636861@gmail.com> — 2022-08-08T16:27:46Z

Hello,
I can't find an option with pg_restore to rename an exported schema
schema1 -> schema2
Is a development in progress to add this option

Regards,

Fabrice

Re: pg_restore remap schema

Guillaume Lelarge <guillaume@lelarge.info> — 2022-08-08T18:16:08Z

Hi,

Le lun. 8 août 2022 à 18:28, Fabrice Chapuis <fabrice636861@gmail.com> a
écrit :

> Hello,
> I can't find an option with pg_restore to rename an exported schema
> schema1 -> schema2
>

That's because it doesn't exist :)


> Is a development in progress to add this option
>
>
Nope, never heard of someone working on this.

Regards.


-- 
Guillaume.

Re: pg_restore remap schema

Tom Lane <tgl@sss.pgh.pa.us> — 2022-08-08T18:22:42Z

Guillaume Lelarge <guillaume@lelarge.info> writes:
> Le lun. 8 août 2022 à 18:28, Fabrice Chapuis <fabrice636861@gmail.com> a
>> Is a development in progress to add this option

> Nope, never heard of someone working on this.

People have asked for such a thing before, but it'd be quite difficult
to do reliably --- particularly inside function bodies, which aren't
normally parsed at all during a dump/restore.  If you're willing to
accept a 95% solution, running the pg_restore output through "sed"
would likely work fairly well.  I'd personally want to diff the
before-and-after scripts before applying though :-)

			regards, tom lane

Re: pg_restore remap schema

Marcos Pegoraro <marcos@f10.com.br> — 2022-08-08T19:26:36Z

>
> People have asked for such a thing before, but it'd be quite difficult
> to do reliably --- particularly inside function bodies, which aren't
> normally parsed at all during a dump/restore.  If you're willing to
> accept a 95% solution, running the pg_restore output through "sed"
> would likely work fairly well.  I'd personally want to diff the
> before-and-after scripts before applying though :-)
>

Another 95% solution, if both schemas are on the same server, google for
function clone schema, you´ll find some of them. They create tables,
functions, sequences, views, ...

Re: pg_restore remap schema

Fabrice Chapuis <fabrice636861@gmail.com> — 2022-08-09T06:39:17Z

Thank you for your reply.
sed is a solution for making substitutions on plain text. But if we work
with directory mode to use parallelism, I don't see how to proceed to make
a schema remap. Editing the toc file in text mode does not work neither.

Regards,

Fabrice

On Mon, Aug 8, 2022 at 9:27 PM Marcos Pegoraro <marcos@f10.com.br> wrote:

> People have asked for such a thing before, but it'd be quite difficult
>> to do reliably --- particularly inside function bodies, which aren't
>> normally parsed at all during a dump/restore.  If you're willing to
>> accept a 95% solution, running the pg_restore output through "sed"
>> would likely work fairly well.  I'd personally want to diff the
>> before-and-after scripts before applying though :-)
>>
>
> Another 95% solution, if both schemas are on the same server, google for
> function clone schema, you´ll find some of them. They create tables,
> functions, sequences, views, ...
>
>

Re: pg_restore remap schema

Fabrice Chapuis <fabrice636861@gmail.com> — 2022-11-16T12:08:13Z

Hi,
I worked on the pg_dump source code to add remap schema functionality to
use it internally where I work. This is a first version that allows to
remap tables, views and sequences (only to export schema). Is this
development likely to interest the PG community and to continue this
development further?

Regards,
Fabrice

On Mon, Aug 8, 2022 at 8:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Guillaume Lelarge <guillaume@lelarge.info> writes:
> > Le lun. 8 août 2022 à 18:28, Fabrice Chapuis <fabrice636861@gmail.com> a
> >> Is a development in progress to add this option
>
> > Nope, never heard of someone working on this.
>
> People have asked for such a thing before, but it'd be quite difficult
> to do reliably --- particularly inside function bodies, which aren't
> normally parsed at all during a dump/restore.  If you're willing to
> accept a 95% solution, running the pg_restore output through "sed"
> would likely work fairly well.  I'd personally want to diff the
> before-and-after scripts before applying though :-)
>
>                         regards, tom lane
>

Re: pg_restore remap schema

Guillaume Lelarge <guillaume@lelarge.info> — 2022-11-16T13:36:15Z

Hi,

Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis <fabrice636861@gmail.com> a
écrit :

> Hi,
> I worked on the pg_dump source code to add remap schema functionality to
> use it internally where I work. This is a first version that allows to
> remap tables, views and sequences (only to export schema). Is this
> development likely to interest the PG community and to continue this
> development further?
>
>
If it doesn't work on functions, that will be a big issue. And to be
honest, I don't think you can do it reliably on functions, especially with
dynamic queries in PL/pgsql.

Though it would still be interesting to see your patch.


-- 
Guillaume.

Re: pg_restore remap schema

Tom Lane <tgl@sss.pgh.pa.us> — 2022-11-16T14:42:28Z

Guillaume Lelarge <guillaume@lelarge.info> writes:
> Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis <fabrice636861@gmail.com> a
> écrit :
>> I worked on the pg_dump source code to add remap schema functionality to
>> use it internally where I work. This is a first version that allows to
>> remap tables, views and sequences (only to export schema). Is this
>> development likely to interest the PG community and to continue this
>> development further?

> If it doesn't work on functions, that will be a big issue. And to be
> honest, I don't think you can do it reliably on functions, especially with
> dynamic queries in PL/pgsql.

Yeah --- I fear there is no hope of making a feature like this that
works reliably enough that we'd accept it.  pg_restore is just not
that smart about what is in the chunks of DDL that it processes,
and trying to make it smart enough is a losing game.

			regards, tom lane

Re: pg_restore remap schema

Stéphane Tachoires <stephane.tachoires@gmail.com> — 2022-11-16T14:58:29Z

Hi
And could it be at pg_dump level ?
It seems more aware of what is doing...

Stephane

Le mer. 16 nov. 2022 à 15:42, Tom Lane <tgl@sss.pgh.pa.us> a écrit :

> Guillaume Lelarge <guillaume@lelarge.info> writes:
> > Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis <fabrice636861@gmail.com>
> a
> > écrit :
> >> I worked on the pg_dump source code to add remap schema functionality to
> >> use it internally where I work. This is a first version that allows to
> >> remap tables, views and sequences (only to export schema). Is this
> >> development likely to interest the PG community and to continue this
> >> development further?
>
> > If it doesn't work on functions, that will be a big issue. And to be
> > honest, I don't think you can do it reliably on functions, especially
> with
> > dynamic queries in PL/pgsql.
>
> Yeah --- I fear there is no hope of making a feature like this that
> works reliably enough that we'd accept it.  pg_restore is just not
> that smart about what is in the chunks of DDL that it processes,
> and trying to make it smart enough is a losing game.
>
>                         regards, tom lane
>
>
>

-- 
"Où se posaient les hirondelles avant l'invention du téléphone ?"
  -- Grégoire Lacroix

Re: pg_restore remap schema

Tom Lane <tgl@sss.pgh.pa.us> — 2022-11-16T15:10:20Z

=?UTF-8?Q?St=C3=A9phane_Tachoires?= <stephane.tachoires@gmail.com> writes:
> And could it be at pg_dump level ?
> It seems more aware of what is doing...

pg_dump would be in a slightly better position, but only slightly.
It still has no idea about the contents of function bodies.

Also, if it's acceptable to do this at dump time, you could just
temporarily rename the schema on the server while making the dump.
(Not that that would fix function bodies either.)

			regards, tom lane

Re: pg_restore remap schema

shammat@gmx.net — 2022-11-16T15:14:39Z

Tom Lane schrieb am 08.08.2022 um 20:22:
> Guillaume Lelarge <guillaume@lelarge.info> writes:
>> Le lun. 8 août 2022 à 18:28, Fabrice Chapuis <fabrice636861@gmail.com> a
>>> Is a development in progress to add this option
>
>> Nope, never heard of someone working on this.
>
> People have asked for such a thing before, but it'd be quite difficult
> to do reliably --- particularly inside function bodies, which aren't
> normally parsed at all during a dump/restore.  If you're willing to
> accept a 95% solution, running the pg_restore output through "sed"
> would likely work fairly well.  I'd personally want to diff the
> before-and-after scripts before applying though :-)

FWIW Oracle's "remap_schema" for their DataPump tool doesn't do this inside triggers or functions either.

It does not even adjust the schema for sequences used for default values of columns.

So for users migrating from Oracle, this wouldn't be a surprise ;)

Re: pg_restore remap schema

Fabrice Chapuis <fabrice636861@gmail.com> — 2022-11-17T15:49:31Z

Postgres allows us to rename a schema with the command *alter schema
schema_orig rename to schema_dest*. however the definition of functions
belonging to the original schema are not modified.
CREATE OR REPLACE FUNCTION foo.fcount() RETURNS integer AS $$
declare
   v_count integer;
        BEGIN
    select count(*)
   into v_count

>    from foo.test;

  return v_count;
   -- raise notice 'The number is: %', v_count;
        END;
$$ LANGUAGE plpgsql;

select fcount();
 fcount
      2
(1 row)

alter schema foo rename to bar;
select fcount();
ERROR:  relation "foo.test" does not exist
LINE 2:                         from foo.test
                                     ^
Implementing the remap feature seemed to me feasible since this logic was
similar to that of the native rename function.
Oracle provides this option for its export tool.
But I will not go further in this way I don't want to go against the advice
of experts who have been working on the postgres code for years.

Thank you for your comments

Regards,

Fabrice




On Wed, Nov 16, 2022 at 3:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Guillaume Lelarge <guillaume@lelarge.info> writes:
> > Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis <fabrice636861@gmail.com>
> a
> > écrit :
> >> I worked on the pg_dump source code to add remap schema functionality to
> >> use it internally where I work. This is a first version that allows to
> >> remap tables, views and sequences (only to export schema). Is this
> >> development likely to interest the PG community and to continue this
> >> development further?
>
> > If it doesn't work on functions, that will be a big issue. And to be
> > honest, I don't think you can do it reliably on functions, especially
> with
> > dynamic queries in PL/pgsql.
>
> Yeah --- I fear there is no hope of making a feature like this that
> works reliably enough that we'd accept it.  pg_restore is just not
> that smart about what is in the chunks of DDL that it processes,
> and trying to make it smart enough is a losing game.
>
>                         regards, tom lane
>