A few questions about foreign tables
Hello,
I want to set up a few foreign tables (to access tables from one
PostgreSQL database in another one). However, I want to do it
"properly", using a database migration tool, so I need to have a "down"
migration to revert to the previous state.
How to revert the effect of "import foreign schema"? Is just dropping
the imported tables enough?
Also, how can I check the definitions (i.e., what tables on the server
they correspond to) of existing foreign tables?
(May I ask to be cc'd since I'm not subscribed to the list?)
TIA,
--
Marcin Borkowski
http://mbork.pl
On Wed, 2023-10-18 at 10:24 +0200, Marcin Borkowski wrote:
How to revert the effect of "import foreign schema"? Is just dropping
the imported tables enough?
Running DROP FOREIGN TABLE ... for all imported tables is enough.
Also, how can I check the definitions (i.e., what tables on the server
they correspond to) of existing foreign tables?
\d+ <table name>
(May I ask to be cc'd since I'm not subscribed to the list?)
But please reply to the list.
Yours,
Laurenz Albe
And SELECT relnamespace::regnamespace::text, relname FROM pg_class WHERE
relkind='f'; tells you all of the foreign tables.
Thus, this (untested) query generate all of the DROP FOREIGN TABLE statements:
SELECT format('DROP FOREIGN TABLE IF EXISTS %I.%I RESTRICT;'
, relnamespace::regnamespace::text, relname)
FROM pg_class WHERE relkind='f'
ORDER BY relkind;
On 10/18/23 10:56, Laurenz Albe wrote:
On Wed, 2023-10-18 at 10:24 +0200, Marcin Borkowski wrote:
How to revert the effect of "import foreign schema"? Is just dropping
the imported tables enough?Running DROP FOREIGN TABLE ... for all imported tables is enough.
Also, how can I check the definitions (i.e., what tables on the server
they correspond to) of existing foreign tables?\d+ <table name>
(May I ask to be cc'd since I'm not subscribed to the list?)
But please reply to the list.
Yours,
Laurenz Albe
--
Born in Arizona, moved to Babylonia.
On 2023-10-18, at 17:56, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
On Wed, 2023-10-18 at 10:24 +0200, Marcin Borkowski wrote:
How to revert the effect of "import foreign schema"? Is just dropping
the imported tables enough?Running DROP FOREIGN TABLE ... for all imported tables is enough.
Also, how can I check the definitions (i.e., what tables on the server
they correspond to) of existing foreign tables?\d+ <table name>
Thanks.
(May I ask to be cc'd since I'm not subscribed to the list?)
But please reply to the list.
Of course.
--
Marcin Borkowski
http://mbork.pl
On 2023-10-18, at 18:14, Ron <ronljohnsonjr@gmail.com> wrote:
And SELECT relnamespace::regnamespace::text, relname FROM pg_class
WHERE relkind='f'; tells you all of the foreign tables.Thus, this (untested) query generate all of the DROP FOREIGN TABLE statements:
SELECT format('DROP FOREIGN TABLE IF EXISTS %I.%I RESTRICT;'
, relnamespace::regnamespace::text, relname)
FROM pg_class WHERE relkind='f'
ORDER BY relkind;
Thanks, though happily I only have about a dozen of them, so I could
create these by hand (well, actually by some automation features of my
text editor).
Best,
--
Marcin Borkowski
http://mbork.pl