Thread: pg_restore remap schema
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
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 schemaschema1 -> 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.
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
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, ...
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, ...
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
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.
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
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
=?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
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 ;)
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