Ctrl+K

Connecting to your database

Connecting to your database#

Connecting your application or remotely connecting to your new EDB Postgres Distributed cluster involves:

  • Getting credentials and optionally creating a .pgpass file

  • Establishing the IP address of any PGD Proxy hosts you want to connect to

  • Ensuring that you can connect to that IP address

  • Getting an appropriate Postgres client

  • Connecting the client to the cluster

Getting credentials#

The default user, enterprisedb, was created in the cluster by tpaexec. It also generated passwords for that user as part of the provisioning. To get the password, run:

tpaexec show-password democluster enterprisedb

This command returns a string that’s the password for the enterprisedb user. If you want, you can use that string when prompted for a password.

Creating a .pgpass file#

You can avoid entering passwords for psql and other Postgres clients by creating a `.pgpass file <Monitoring through SQL>` in your home directory. It contains password details that applications can look up when connecting. After getting the password (see Getting credentials ), you can open the .pgpass file using your preferred editor.

In the file, enter:

*:*:bdrdb:enterprisedb:<your password>

Save the file and exit the editor. To secure the file, run the following command. This command gives read and write access only to you.

chmod 0600 ~/.pgpass

Establishing the IP address#

Docker#

Your Docker quick start cluster is by default accessible on the IP addresses 172.17.0.2 (kaboom), 172.17.0.3 (kaftan), 172.17.04 (kaolin), and 172.17.0.5 (kapok). Docker generates these addresses.

AWS#

You can refer to the IP addresses in democluster/ssh_config . Alternatively, run:

aws ec2 --region eu-west-1 describe-instances --query Reservations[*].Instances[*].{PublicIpAddress:PublicIpAddress,Name:Tags[?Key==`Name`]|[0].Value}
__OUTPUT__
[
    [
        {
            "PublicIpAddress": "54.217.130.13",
            "Name": "kapok"
        }
    ],
    [
        {
            "PublicIpAddress": "54.170.119.101",
            "Name": "kaolin"
        }
    ],
    [
        {
            "PublicIpAddress": "3.250.235.130",
            "Name": "kaftan"
        }
    ],
    [
        {
            "PublicIpAddress": "34.247.188.211",
            "Name": "kaboom"
        }
    ]
]

This command shows you EC2’s list of public IP addresses for the cluster instances.

Linux hosts#

You set IP addresses for your Linux servers when you configured the cluster in the quick start. Use those addresses.

Ensure you can connect to your IP addresses#

Linux hosts and Docker#

You don’t need to perform any configuration to connect these.

AWS#

AWS is configured to allow outside access only to its SSH endpoints. To allow Postgres clients to connect from outside the AWS cloud, you need to enable the transit of traffic on port 6432.

Get your own external IP address or the external IP address of the system you want to connect to the cluster. One way to do this is to run:

curl https://checkip.amazonaws.com
__OUTPUT__
89. 97.100.108

You also need the security groupid for your cluster. Run:

aws ec2 --region eu-west-1 describe-security-groups --filter Name=group-name,Values="*democluster*" | grep GroupId
__OUTPUT__
            "GroupId": "sg-072f996360ba20d5c",

Enter the correct region for your cluster, which you set when you configured it.

aws ec2 authorize-security-group-ingress --group-id <SECURITYGROUPID> --protocol tcp --port 6432 --cidr <YOURIP>/32 --region eu-west-1

Again, make sure you put in the correct region for your cluster.

You can read more about this command in Add rules to your security group in the AWS CLI guide.

Getting an appropriate Postgres client#

Unless you installed Postgres on your local system, you probably need to install a client application, such as psql , to connect to your database cluster.

On Ubuntu, for example, you can run:

sudo apt install postgresql-client

This command installs psql , along with some other tools but without installing the Postgres database locally.

Connecting the client to the cluster#

After you install psql or a similar client, you can connect to the cluster. Run:

  psql -h <ipaddressofnode> -p 6432 -U enterprisedb bdrdb
  __OUTPUT__
  psql (15.2, server 15.2.0 (Debian 15.2.0-2.buster))
  SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
  Type "help" for help.

  bdrdb=#

:ref:`Use the `.pgpass` file <#creating-a-pgpass-file>`  with clients that support it, or use the host, port, user,

password, and database name to connect with other applications.

Using proxy failover to connect the client to the cluster#

By listing all the addresses of proxies as the host, you can ensure that the client will always failover and connect to the first available proxy in the event of a proxy failing.

psql -h <ipaddressofnode1>,<ipaddressofnode2>,<ipaddressofnode3> -U enterprisedb -p 6432 bdrdb
__OUTPUT__
psql (15.2, server 15.2.0 (Debian 15.2.0-2.buster))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

bdrdb=#

Creating a Connection URL#

Many applications use a Connection URL to connect to the database. To create a Connection URL, you need to assemble a string in the format:

postgresql://<user>@<ipaddressofnode1>:6432,<ipaddressofnode2>:6432,<ipaddressofnode3>:6432/bdrdb

This format of string can be used with the psql command, so if our database nodes were on Ip addresses 192.168.9.10, 192.168.10.10 and 192.168.10.11, we could use:

psql postgresql://enterprisedb@192.168.9.10:6432,192.168.10.10:6432,192.168.11.10:6432/bdrdb

You can also embed the password in the created URL. If we are using the enterprisedb user and the password for the enterprisedb user is notasecret then the URL would look like:

psql postgresql://enterprisedb:notasecret@192.168.9.10:6432,192.168.10.10:6432,192.168.11.10:6432/bdrdb

Actual passwords are more complex than that and may contain special characters. You will need to urlencode the password to ensure that it doesn’t trip up the shell, the command or the driver you are using.

Making a Java Connection URL#

Finally, the URL we have created is fine for many Postgres applications and clients, but those based on Java require one change to allow Java to invoke the appropriate driver. Precede the URL with jdbc: to make a Java compatible URL.

jdbc:postgresql://enterprisedb@192.168.9.10:6432,192.168.10.10:6432,192.168.11.10:6432/bdrdb

Moving On#

You are now equipped to connect your applications to your cluster, with all the connection credentials and URLs you need.

Contents