CREATE SERVER
CREATE SERVER — define a new foreign server
CREATE SERVER [ IF NOT EXISTS ]server_name
[ TYPE 'server_type
' ] [ VERSION 'server_version
' ]
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS (option
'value
' [, ... ] ) ]
CREATE SERVER
defines a new foreign server. The user who defines the server becomes its owner.
A foreign server typically encapsulates connection information that a foreign-data wrapper uses to access an external data resource. Additional user-specific connection information may be specified by means of user mappings.
The server name must be unique within the database.
Creating a server requires USAGE
privilege on the foreign-data wrapper being used.
Parameters
IF NOT EXISTS
Do not throw an error if a server with the same name already exists. A notice is issued in this case. Note that there is no guarantee that the existing server is anything like the one that would have been created.
server_name
The name of the foreign server to be created.
server_type
Optional server type, potentially useful to foreign-data wrappers.
server_version
Optional server version, potentially useful to foreign-data wrappers.
fdw_name
The name of the foreign-data wrapper that manages the server.
OPTIONS ( option 'value' [, ... ] )
This clause specifies the options for the server. The options typically define the connection details of the server, but the actual names and values are dependent on the server’s foreign-data wrapper.
When using the dblink module, a foreign server’s name can be used as an argument of the dblink_connect function to indicate the connection parameters. It is necessary to have the USAGE
privilege on the foreign server to be able to use it in this way.
Examples
Create a server myserver
that uses the foreign-data wrapper postgres_fdw
:
CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'foo', dbname 'foodb', port '5432');
ALTER SERVER
ALTER SERVER — change the definition of a foreign server
ALTER SERVERname
[ VERSION 'new_version
' ]
[ OPTIONS ( [ ADD | SET | DROP ]option
['value
'] [, ... ] ) ]
ALTER SERVERname
OWNER TO {new_owner
| CURRENT_USER | SESSION_USER }
ALTER SERVERname
RENAME TOnew_name
ALTER SERVER
changes the definition of a foreign server. The first form changes the server version string or the generic options of the server (at least one clause is required). The second form changes the owner of the server.
To alter the server you must be the owner of the server. Additionally to alter the owner, you must own the server and also be a direct or indirect member of the new owning role, and you must have USAGE
privilege on the server’s foreign-data wrapper. (Note that superusers satisfy all these criteria automatically.)
Parameters
name
The name of an existing server.
new_version
New server version.
OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] )
Change options for the server. ADD
, SET
, and DROP
specify the action to be performed. ADD
is assumed if no operation is explicitly specified. Option names must be unique; names and values are also validated using the server’s foreign-data wrapper library.
new_owner
The user name of the new owner of the foreign server.
new_name
The new name for the foreign server.
Examples
Alter server foo
, add connection options:
ALTER SERVER foo OPTIONS (host 'foo', dbname 'foodb');
Alter server foo
, change version, change host
option:
ALTER SERVER foo VERSION '8.4' OPTIONS (SET host 'baz');
DROP SERVER
DROP SERVER — remove a foreign server descriptor
DROP SERVER [ IF EXISTS ] name
[, ...] [ CASCADE | RESTRICT ]
DROP SERVER
removes an existing foreign server descriptor. To execute this command, the current user must be the owner of the server.
Parameters
IF EXISTS
Do not throw an error if the server does not exist. A notice is issued in this case.
name
The name of an existing server.
CASCADE
Automatically drop objects that depend on the server (such as user mappings), and in turn all objects that depend on those objects.
RESTRICT
Refuse to drop the server if any objects depend on it. This is the default.
Examples
Drop a server foo
if it exists:
DROP SERVER IF EXISTS foo;