PostgreSQL

PostgreSQL is one of the most popular and advanced free and open-source relational database management systems.

Skyvia supports usual TCP/IP PostgreSQL connections and secure SSL and SSH connections. Skyvia supports synchronization of PostgreSQL servers of versions 8.3 and higher and Amazon Aurora PostgreSQL.

To connect to a PostgreSQL server, you need to specify the database server host name or IP address, the port the PostgreSQL server is running on, the user name and password to log in with, and the database name.

For connecting to local PostgreSQL servers, Skyvia offers two options: direct connections and agent connections.

In order to use a direct connection, your PostgreSQL server must be available through the Internet. You can find some advice on configuring your PostgreSQL server in order to connect to it from Skyvia directly in the How to Configure Local Database Server to Access It from Skyvia topic.

Agent connections don’t have such requirements, but you need to install the Skyvia Agent application in order to use them.

Connection Editor window

You need to specify the following parameters for TCP/IP PostgreSQL connection:

  • Name — connection name that will be used to identify the connection in the objects list and when selecting a connection for an integration.
  • Server — name or IP address of the PostgreSQL Server host to connect to.
  • Port — PostgreSQL Server connection port; default value is 5432.
  • User Id — user name to log in with.
  • Password — password to log in with.
  • Database — name of the PostgreSQL database you want to connect to.
  • Schema — name of the PostgreSQL schema you want to connect to.

If you need to pass UTF8 characters to a PostgreSQL database, click Advanced Settings and select the Unicode checkbox.

If you want to use SSL or SSH connection, additionally you need to click Advanced Settings and select the required Protocol.

After this, for SSL connection you need to specify:

  • SSL Mode — this mode determines the priority of using secure SSL connection. You can select any of the following modes:
    • Allow — try first a non-SSL connection, then if that fails, try an SSL connection.
    • Disable — establish only an unencrypted SSL connection. If this mode is selected, SSL is not used, and other SSL parameters are not available. This mode is selected by default.
    • Prefer — try first an SSL connection, then if that fails, try a none-SSL connection.
    • Require — establish only a secure SSL connection.
  • SSL CA Cert — authority certificate. Paste the content of the certificate file into the box or click the button to open a multiline editor and paste the content of the certificate file there.
  • SSL Cert — client certificate. Paste the content of the certificate file into the box or click the button to open a multiline editor and paste the content of the certificate file there.
  • SSL Key — client’s private key. Paste the content of the key file into the box or click the button to open a multiline editor and paste the content of the key file there.
  • SSL TLS Protocol — preferred TLS protocol version reported to a server when establishing an SSL connection. The default value is 1.1 to avoid errors with older server versions, not supporting TLS 1.2. Acceptable values are 1.0, 1.1, 1.2 (without quotes).

For SSH connection specify the following:

  • SSH Authentication Type — type of SSH authentication to use: Password or PublicKey. When using the password authentication, you need to specify the SSH password. For public key authentication, you need to specify the passphrase for the private key and the private key.
  • SSH Host — name or IP address of the SSH server.
  • SSH Port — TCP/IP port to connect to the SSH Server. By default, it is 22.
  • SSH User — user name on the machine where the SSH Server is running. It is a Windows user, not a user of the MySQL Server.
  • SSH Password — password of a user account on the SSH Server. It is available if Password SSH Authentication Type is selected.
  • SSH Passphrase — passphrase for a private key. You can set it while generating public and private key files through a key generator tool, for example PuTTygen. It is available if PublicKey SSH Authentication Type is selected.
  • SSH Private Key — private key. Paste the content of the key file into the box or click the button to open a multiline editor and paste the content of the key file there. It is available if PublicKey SSH Authentication Type is selected.

In Advanced Settings, you can also select the Unicode checkbox to use Unicode encoding for the connection.

Additionally, Advanced Settings include the Connection Timeout and Command Timeout parameters:

  • Connection Timeout parameter determines the time (in seconds) to wait while trying to establish a connection before terminating the attempt and reporting an error.
  • Command Timeout parameter specifies the wait time in seconds before terminating the attempt to execute a command and produce an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it does not include the time necessary to fetch data if the command selects some data.

Generally, you do not need to modify these parameters, but in some specific cases, when a connection to the database server is not good or a command may take significant time to be executed, you may try increasing their values.

Supported Actions and Actions Specifics

PostgreSQL connector supports the following actions: