Create a PostgreSQL Data Source

Add a PostgreSQL data source to NineData. You can then use it for Database DevOps, backup and restore, data replication, database comparison, and other supported features. This guide covers connection mode, credentials, client settings, environment settings, SSL settings, and the connection test.

Before you begin

• The server IP address of NineData has been added to the data source allowlist. The server IP address can be viewed by selecting the target region in Access Region on the Create Datasource page.

  

• 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.

Procedure

1. Log in to the NineData Console.

2. On the left navigation pane, click Datasource > Datasource.

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

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

4. Configure the data source parameters:

   Parameter	Description
   Name	Enter a data source name. Use a meaningful name so you can find and manage it later.
   Configuration items when Connection is IP Address	Host: The public connection address and port of the data source.
   Configuration items when Connection is Gateway	Gateway: Select the NineData gateway installed on the host where the data source is located. Host: Enter localhost if the data source is on the same host as the gateway, or enter the internal IP address of the host where the data source is located.
   Connection	Select how NineData connects to the data source. Supported methods are IP Address and SSH Tunnel.It also supports Gateway. NineData gateway provides private-network access. For instructions, see Add Gateway. IP Address: Connect through the public network address. Gateway: Connect through a NineData gateway. SSH Tunnel: Connect through an SSH tunnel.
   Configuration items when Connection is SSH Tunnel	SSH Host: Enter the public IP address or domain name and SSH port of the server where the target data source is located. The default SSH port is 22. SSH Authentication Method: Select the SSH authentication method. Password: Connect with SSH Username and Password. SSH Username: Enter the login username of the server where the target data source is located. Password: Enter the login password of the server where the target data source is located. Key (recommended): Connect with SSH Username and Key File. SSH Username: Enter the login username of the server where the target data source is located. Key File: Click Upload to upload the private key file, which is a key file without a suffix.If you have not created one yet, see Generate SSH Tunnel Key File. Password: Enter the password set when the key file was generated. If no password was set during key generation, leave this field blank. Note: After configuring SSH, click Connection Test to test the tunnel. Possible results: Connection Successfully: The SSH tunnel is established. Error message: The connection failed. Troubleshoot the cause and try again. Host: Enter localhost if the data source is on the same host as the SSH server, or enter the internal IP address of the host where the data source is located.
   DB Account	Enter the login username for PostgreSQL.
   DB Password	Enter the login password for PostgreSQL.
   client character set	Define the character encoding that the application uses to generate and parse text data, such as GBK or UTF8. NineData uses this encoding for bidirectional transcoding between the application and the database to prevent garbled text during reads and writes.
   client timezone	Define the application time zone. When NineData reads date and time fields that do not include time zone information, it converts the stored values to this time zone before returning them. For example, if your application is located in Shanghai, enter +08:00.
   Access Region	Select the region closest to the location of your PostgreSQL host to effectively reduce network latency.
   Environment	Choose an environment based on the business purpose of the data source. This serves as an environment identifier of the data source. The default environments are PROD and DEV.You can also create a custom environment. Note: Under organization mode, the database environment can also be applied to permission policy management. For example, the default Prod Admin role can access only data sources in the PROD environment and cannot access data sources in other environments. For more information, see Manage Roles.
   Encryption	Configure SSL encryption for access to the data source (default: off). If the data source requires SSL-encrypted connections, enable this option; otherwise, the connection fails. Use the switch to enable or disable encrypted transmission. Click the > to the left of Encryption to expand detailed configuration. SSL Options: Supports the following four methods. Prefer: NineData checks the server SSL status. If SSL is enabled, NineData connects through SSL first. If SSL is not enabled, NineData uses a non-SSL connection. 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 fails. Verify-CA: Upload the CA certificate to verify that the server certificate is issued by a trusted authority and to help prevent man-in-the-middle attacks. If needed, also upload the client user certificate and key to verify the client identity and encrypt communication with the server. Verify-full: Based on Verify-CA, also check whether the subject of the server certificate, such as the hostname or IP address, matches the server being connected to. CA Cert: When SSL Options is Verify-CA or Verify-Full, select this option and specify the CA certificate used to verify the server certificate. Client Certificate (optional): If the server requests a client certificate, upload the client certificate to verify your identity. Client Certificate includes the user certificate (.pem) and key (.pk8). Key Password: If the uploaded client key file is password-protected, enter the password here. If no password is set, leave it blank. For more SSL configuration options, see the official documentation: Configure PostgreSQL encrypted connections.

5. After you configure all parameters, click Connection Test next to Create Datasource to test connectivity. When Connection Successfully is displayed, click Create Datasource to create the data source. Otherwise, review the connection settings and run the test again.

Result

After the connection test succeeds and you create the data source, it appears in the data source list. You can use it in NineData features that support PostgreSQL.

Appendix: Add NineData IP addresses to the PostgreSQL database whitelist

When you add a data source located in On-Premise/Other Cloud, add the NineData service IP address to the PostgreSQL database whitelist so NineData can access the database.

The following example uses PostgreSQL 10.9.

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 allowed IP address range below the ADDRESS column. For example, to allow access from 192.168.1.0 through 192.168.1.255, use the following configuration.

   # 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 close the pg_hba.conf file, and then restart the PostgreSQL service.