Create PostgreSQL Data Source

NineData supports adding various types and environments of data sources to the console for unified management. You can use database DevOps, backup and recovery, data replication, and database comparison features for data sources that have been added. This article introduces how to add a PostgreSQL data source to NineData.

Prerequisites

• The server IP address of NineData has been added to the data source allowlist. Please refer to the image below for instructions on how to obtain the server IP address.

  

• Make sure you have available data source quota; otherwise, the data source cannot be added. You can quickly check your remaining quota at the top-right corner of the NineData console.

Operation Steps

1. Log in to the NineData Console.

2. On the left navigation pane, click > .

3. Click  tab, and click  on the page. In the popup window for selecting the data source type, choose > (the type of data source to be added), and configure the parameters based on the table below on the page.
   tip

   If you make a mistake during the operation, you can click the icon at the top of the page to make a new selection.

4. Configure the parameters of the data source:

   Parameter	Description
   	Enter the name of the data source. To facilitate subsequent search and management, please use meaningful names.
   Select configuration item	: The public connection address and port of the data source.
   Select configuration item	: Select the NineData gateway installed on the host where the data source is located. : Can be written as localhost (data source is on the local machine) or the intranet IP of the host where the data source is located.
   	Select the access method of the data source. Supports access through , , SSH Tunnel three methods. : Access the data source through the public network address. : A secure and fast intranet access method provided by NineData, which requires the host where the data source is located to be connected first. For connection methods, please refer to Add Gateway. SSH Tunnel: Access the data source through an SSH tunnel.
   Select SSH Tunnel configuration item	: Enter the public IP or domain name of the server where the target data source is located, as well as the corresponding port number (the default port number for SSH service is 22). : Select the SSH authentication method. : Connect through (i.e., the server's login name) and (i.e., the server's login password). : Enter the login username of the server where the target data source is located. : Enter the login password of the server where the target data source is located. (recommended): Connect through and . : Enter the login username of the server where the target data source is located. : Click to upload the private key file, which is a key file without a suffix. If you have not created one yet, please refer to Generate SSH Tunnel Key File. : Enter the password set when the key file was generated. If you did not set a password during the key generation process, leave this field blank. Note: After the SSH configuration is completed, you need to click the on the right, and there may be the following two results: Prompt : Indicates that the SSH Tunnel has been established. Prompt error message: Indicates a connection failure, you need to troubleshoot the cause of the error and retry. : Can be written as localhost (data source is on the local machine) or the intranet IP of the host where the data source is located.
   	Enter the login username for PostgreSQL.
   	Enter the login password for PostgreSQL.
   	Defines the character encoding (e.g., GBK, UTF8) used by the application to generate and parse text data. NineData will perform bidirectional transcoding between the application and the database based on this encoding to prevent garbled text during reading and writing.
   	Defines the time zone in which the application is located. When reading date and time fields that do not include time zone information, NineData will convert the stored data to the defined time zone before returning it. For example, if your application is in Shanghai, enter +08:00.
   	Select the region closest to your PostgreSQL host to effectively reduce network latency.
   	Choose according to the actual business purpose of the data source, as an environmental identifier for the data source. Default provides and environments, and supports you to create a custom environment. Note: Under the organization mode, the database environment can also be applied to permission policy management, for example, the default role only supports access to data sources in the environment and cannot access data sources in other environments. For more information, please refer to Manage Roles.
   	Whether to use SSL encryption to access the data source (default off). If the data source enforces SSL encrypted connections, this switch must be turned on, otherwise, the connection will fail. Click the switch on the right to turn on or off encrypted transmission. Click the > to the left of to expand detailed configuration. : Supports the following four methods. Prefer: Automatically detect the SSL status of the server, if the server has SSL enabled, then connect through SSL first, if the server does not have SSL enabled, then connect through non-SSL. Require: Always use SSL to connect to the data source, if the server does not support this method or cannot establish an SSL connection for other reasons, the connection will fail. Verify-CA: Upload the CA certificate to verify whether the server's service certificate is issued by a trusted institution to prevent man-in-the-middle attacks. At the same time, according to the needs, upload the client user certificate and key to verify your own identity and encrypt the communication with the server. Verify-full: On the basis of Verify-CA, check whether the subject of the server certificate (such as hostname, IP address) matches the actual server you are connecting to, to ensure the security of the connection. : When is Verify-CA or Verify-Full, you need to select it, specifying the CA certificate to verify the server's certificate. (optional): If the server requests a client certificate, you must upload the client certificate to verify your own identity. includes the user certificate (.pem) and key (.pk8). : If the uploaded client key file is password-protected, you need to enter the password here, if not, leave it blank. For more SSL configuration methods, please refer to the official documentation: Configure PostgreSQL encrypted links.

5. After all configurations are completed, click the next to to test whether the data source can be accessed normally. If prompted with , you can click to complete the addition of the data source. Otherwise, please recheck the connection settings until the connection test is successful.

Appendix: Add NineData's IP address to the PostgreSQL database whitelist

When adding a data source located in , you need to add the IP address of the NineData service to the PostgreSQL database whitelist to allow NineData to provide services.

This section takes PostgreSQL version 10.9 as an example to introduce how to add an IP whitelist.

1. Open the PostgreSQL configuration file postgresql.conf, which is usually located at: <PostgreSQL installation directory>/data/postgresql.conf.

2. Find the listen_addresses parameter, remove the comment symbol (#) in front of it, and set its value to '*', to allow connection requests from all IP addresses, then save and exit the postgresql.conf file.

3. Open the pg_hba.conf configuration file, which is usually located at: <PostgreSQL installation directory>/data/pg_hba.conf.

4. Find the IPv4 local connections or IPv6 local connections line, and add the IP address range that needs to be allowed to access below the ADDRESS column, for example, if you need to allow access from the 192.168.1.0 ~ 192.168.1.255 address range, configure as shown below.

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
   
   # "local" is for Unix domain socket connections only
   #local   all             all                                     trust
   # IPv4 local connections:
   #host    all             all             127.0.0.1/32            trust
   host    all             all             192.168.1.0/24            md5

5. Save and exit the pg_hba.conf configuration file, then restart the PostgreSQL service to take effect.