Creating and editing a foreign server

PostgreSQL allows you to modify a foreign server with a few simple commands. For example, let's say your foreign database has been moved to a new host. You can simply change the hostname of the foreign server and all of your foreign tables will automatically point to the new host. Here is an example of a modification I ran on a MySQL foreign server. First let's look at the current setup of my foreign servers with this query.

select * from pg_foreign_server;

This may produce an output like this.

+-[ RECORD 1 ]------------------------------------------------------------+
| srvname    | some_mysql_server                                          |
| srvowner   | 10                                                         |
| srvfdw     | 18241                                                      |
| srvtype    | [NULL]                                                     |
| srvversion | [NULL]                                                     |
| srvacl     | {postgres=U/postgres,dwc=U/postgres}                       |
| srvoptions | {host=mysql1.myhost.com,port=3309}                       |
+-[ RECORD 2 ]------------------------------------------------------------+
| srvname    | some_pg_server                                                     |
| srvowner   | 16389                                                      |
| srvfdw     | 18242                                                      |
| srvtype    | [NULL]                                                     |
| srvversion | [NULL]                                                     |
| srvacl     | [NULL]                                                     |
| srvoptions | {dbname=dfdata,host=prod-pg.hostanme.com,port=5432} |
+------------+------------------------------------------------------------+

Now let's change the hostname for the some_mysql_server foreign server with this SQL.

alter server some_mysql_server options (set host 'mysql2.myhost.com');
Talk PostgreSQL is a site designed to help you understand PostgreSQL, get PostgreSQL news, other helpful information. 
© Copyright 2019 - TalkPostgreSQL - All Rights Reserved
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram