Eugen Konkov
Eugen Konkov

Reputation: 25207

May I alter SERVER for foreign table?

According to the DOC I can not do that. But fully recreate table force me to do huge work instead of simple:

ALTER FOREIGN TABLE table_name ALTER SERVER new_server_name;

Upvotes: 7

Views: 7957

Answers (3)

Igor Iris
Igor Iris

Reputation: 106

In Postgres 12 this command worked fine in order to change the server IP address that was used for foreign DB wrapper

ALTER SERVER your_server_name OPTIONS (SET host 'XX.XXX.XX.X');

Upvotes: 6

Timur Luchkin
Timur Luchkin

Reputation: 1

Don't change server using this way, because it will not update dependency table pg_depend and cause many problems later.

Upvotes: 0

JustMe
JustMe

Reputation: 2383

List your foreign data servers and note it oid:

select oid, * from pg_foreign_server 

Find your foreign table:

select oid, * from pg_class where relkind = 'f'

Then modify system catalog pg_foreign_table like:

update pg_foreign_table set ftserver = 11573931 where ftserver = 11573932 -- and ftrelid = YOUR_OID_RELID_FROM_PG_CLASS

Upvotes: 6

Related Questions