Connecting to your database v5

Suggest edits

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 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=#

Use the .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.

Passwords should not be embedded

While we have shown you how to embed a password, we recommend that you do not do this. The password is easily extracted from the URL and can easily be saved in insecure locations. Consider other ways of passing the password.

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.

← Prev

Deploying an EDB Postgres Distributed example cluster on Azure and Google cloud platforms

Next →

Exploring failover handling with PGD