pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pg_restore remap schema
12+ messages / 6 participants
[nested] [flat]

* pg_restore remap schema
@ 2022-08-08 16:27 Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  0 siblings, 1 reply; 12+ messages in thread

From: Fabrice Chapuis @ 2022-08-08 16:27 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
@ 2022-08-08 18:16 ` Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 12+ messages in thread

From: Guillaume Lelarge @ 2022-08-08 18:16 UTC (permalink / raw)
  To: Fabrice Chapuis <fabrice636861@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
@ 2022-08-08 18:22   ` Tom Lane <tgl@sss.pgh.pa.us>
  2022-08-08 19:26     ` Re: pg_restore remap schema Marcos Pegoraro <marcos@f10.com.br>
  2022-11-16 12:08     ` Re: pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-11-16 15:14     ` Re: pg_restore remap schema Thomas Kellerer <shammat@gmx.net>
  0 siblings, 3 replies; 12+ messages in thread

From: Tom Lane @ 2022-08-08 18:22 UTC (permalink / raw)
  To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Fabrice Chapuis <fabrice636861@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

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





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-08 19:26     ` Marcos Pegoraro <marcos@f10.com.br>
  2022-08-09 06:39       ` Re: pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2 siblings, 1 reply; 12+ messages in thread

From: Marcos Pegoraro @ 2022-08-08 19:26 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Fabrice Chapuis <fabrice636861@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  2022-08-08 19:26     ` Re: pg_restore remap schema Marcos Pegoraro <marcos@f10.com.br>
@ 2022-08-09 06:39       ` Fabrice Chapuis <fabrice636861@gmail.com>
  0 siblings, 0 replies; 12+ messages in thread

From: Fabrice Chapuis @ 2022-08-09 06:39 UTC (permalink / raw)
  To: Marcos Pegoraro <marcos@f10.com.br>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Guillaume Lelarge <guillaume@lelarge.info>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

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

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-16 12:08     ` Fabrice Chapuis <fabrice636861@gmail.com>
  2022-11-16 13:36       ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2 siblings, 1 reply; 12+ messages in thread

From: Fabrice Chapuis @ 2022-11-16 12:08 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; Guillaume Lelarge <guillaume@lelarge.info>; Marcos Pegoraro <marcos@f10.com.br>

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
>

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-16 12:08     ` Re: pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
@ 2022-11-16 13:36       ` Guillaume Lelarge <guillaume@lelarge.info>
  2022-11-16 14:42         ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 12+ messages in thread

From: Guillaume Lelarge @ 2022-11-16 13:36 UTC (permalink / raw)
  To: Fabrice Chapuis <fabrice636861@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; Marcos Pegoraro <marcos@f10.com.br>

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.

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-16 12:08     ` Re: pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-11-16 13:36       ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
@ 2022-11-16 14:42         ` Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-16 14:58           ` Re: pg_restore remap schema Stéphane Tachoires <stephane.tachoires@gmail.com>
  2022-11-17 15:49           ` Re: pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  0 siblings, 2 replies; 12+ messages in thread

From: Tom Lane @ 2022-11-16 14:42 UTC (permalink / raw)
  To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Fabrice Chapuis <fabrice636861@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; Marcos Pegoraro <marcos@f10.com.br>

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





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-16 12:08     ` Re: pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-11-16 13:36       ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-11-16 14:42         ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-16 14:58           ` Stéphane Tachoires <stephane.tachoires@gmail.com>
  2022-11-16 15:10             ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 12+ messages in thread

From: Stéphane Tachoires @ 2022-11-16 14:58 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Fabrice Chapuis <fabrice636861@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; Marcos Pegoraro <marcos@f10.com.br>

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

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-16 12:08     ` Re: pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-11-16 13:36       ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-11-16 14:42         ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-16 14:58           ` Re: pg_restore remap schema Stéphane Tachoires <stephane.tachoires@gmail.com>
@ 2022-11-16 15:10             ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 12+ messages in thread

From: Tom Lane @ 2022-11-16 15:10 UTC (permalink / raw)
  To: Stéphane Tachoires <stephane.tachoires@gmail.com>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; Fabrice Chapuis <fabrice636861@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; Marcos Pegoraro <marcos@f10.com.br>

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





^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
  2022-11-16 12:08     ` Re: pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-11-16 13:36       ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-11-16 14:42         ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-17 15:49           ` Fabrice Chapuis <fabrice636861@gmail.com>
  1 sibling, 0 replies; 12+ messages in thread

From: Fabrice Chapuis @ 2022-11-17 15:49 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>; Marcos Pegoraro <marcos@f10.com.br>

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
>

^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: pg_restore remap schema
  2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
  2022-08-08 18:16 ` Re: pg_restore remap schema Guillaume Lelarge <guillaume@lelarge.info>
  2022-08-08 18:22   ` Re: pg_restore remap schema Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-11-16 15:14     ` Thomas Kellerer <shammat@gmx.net>
  2 siblings, 0 replies; 12+ messages in thread

From: Thomas Kellerer @ 2022-11-16 15:14 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2022-11-17 15:49 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-08 16:27 pg_restore remap schema Fabrice Chapuis <fabrice636861@gmail.com>
2022-08-08 18:16 ` Guillaume Lelarge <guillaume@lelarge.info>
2022-08-08 18:22   ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-08 19:26     ` Marcos Pegoraro <marcos@f10.com.br>
2022-08-09 06:39       ` Fabrice Chapuis <fabrice636861@gmail.com>
2022-11-16 12:08     ` Fabrice Chapuis <fabrice636861@gmail.com>
2022-11-16 13:36       ` Guillaume Lelarge <guillaume@lelarge.info>
2022-11-16 14:42         ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-16 14:58           ` Stéphane Tachoires <stephane.tachoires@gmail.com>
2022-11-16 15:10             ` Tom Lane <tgl@sss.pgh.pa.us>
2022-11-17 15:49           ` Fabrice Chapuis <fabrice636861@gmail.com>
2022-11-16 15:14     ` Thomas Kellerer <shammat@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox