Help Center> Data Warehouse Service> SQL Syntax Reference> DDL Syntax> ALTER SERVER
View PDF

ALTER SERVER

Function

ALTER SERVER adds, modifies, or deletes the parameters of an existing server. You can query existing servers from the pg_foreign_server system catalog.

Precautions

Only the owner of a server or a system administrator can run this statement.

Syntax

  • Change the parameters for an external server.
1
2
 
ALTER SERVER server_name [ VERSION 'new_version' ]
    [ OPTIONS ( {[ ADD | SET | DROP ] option ['value']} [, ... ] ) ];

In OPTIONS, ADD, SET, and DROP are operations to be executed. If these operations are not specified, ADD operations will be performed by default. option and value are corresponding operation parameters.

Currently, only SET is supported on an HDFS server. ADD and DROP are not supported. The syntax for SET and DROP operations is retained for later use.

  • Change the owner of an external server.
1
2
 
ALTER SERVER server_name 
    OWNER TO new_owner;
  • Change the name of an external server.
1
2
 
ALTER SERVER server_name 
    RENAME TO new_name;

Parameter Description

The parameters for modifying the server are as follows:

  • server_name

    Specifies the name of the server to be modified.

  • new_version

    Indicates the new version of the server.

  • The server parameters in OPTIONS are as follows:
    • address

      Specifies the IP address and port number of the primary and standby nodes of the HDFS cluster.

      • address is mandatory for HDFS servers. Therefore, ADD and DROP operations are not supported.
      • address only supports IPv4 addresses in dot-decimal notation, and an address string cannot contain spaces. Groups of addresses are separated by commas (,). An IP address and a port number are separated by a colon (:). You are advised to configure two IP address and port pairs in an HDFS cluster. One is used as the socket address of the primary HDFS NameNode and another is used as that of the secondary HDFS NameNode.
    • hdfscfgpath

      Specifies the HDFS cluster configuration file.

      • If HDFS is in security mode, hdfscfgpath is mandatory.
      • If you set hdfscfgpath, you can only set one value for path.
    • region

      Indicates the IP address or domain name of the OBS server. This parameter is available only when type is OBS.

  • new_owner

    Indicates the new owner of the server. To change the owner, you must be the owner of the external server and a direct or indirect member of the new owner role, and must have the USAGE permission on the encapsulator of the external server.

  • new_name

    Indicates the new name of the server.

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
Create the hdfs_server server, and hdfs_fdw is the built-in foreign data wrapper.
CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000', hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS');

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server';
   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                              srvoptions                                           
    
-------------+----------+--------+---------+------------+--------+---------------------------------------------------------------------------------------------------
----
 hdfs_server |       10 |  13332 |         |            |        | {"address= 10.10.0.100:25000,10.10.0.101:25000",hdfscfgpath=/opt/hadoop_client/HDFS/hadoop/etc/hadoop}
(1 row)
Change the current name to the IP address of the HDFS server.
ALTER SERVER hdfs_server OPTIONS ( SET address '10.10.0.110:25000,10.10.0.120:25000');

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server';
   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                              srvoptions
-------------+----------+--------+---------+------------+--------+-------------------------------------------------------------------------------------------------------
 hdfs_server |       10 |  13167 |         |            |        | {"address=10.10.0.110:25000,10.10.0.120:25000",hdfscfgpath=/opt/hadoop_client/HDFS/hadoop/etc/hadoop}
(1 row)
--Change the current name to hdfscfgpath of the HDFS server.
ALTER SERVER hdfs_server OPTIONS ( SET hdfscfgpath '/opt/bigdata/hadoop');

SELECT * FROM pg_foreign_server WHERE srvname='hdfs_server';
   srvname   | srvowner | srvfdw | srvtype | srvversion | srvacl |                                   srvoptions                                    
-------------+----------+--------+---------+------------+--------+---------------------------------------------------------------------------------
 hdfs_server |       10 |  13332 |         |            |        | {"address=10.10.0.110:25000,10.10.0.120:25000",hdfscfgpath=/opt/bigdata/hadoop}
(1 row)
-- Delete hdfs_server.
DROP SERVER hdfs_server;

Helpful Links

CREATE SERVER DROP SERVER

Parent topic: DDL Syntax