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: .. code:: shell 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 :ref:`a `.pgpass` file ` in your home directory. It contains password details that applications can look up when connecting. After getting the password (see :ref:`Getting credentials ` ), you can open the ``.pgpass`` file using your preferred editor. In the file, enter: .. code:: plain *:*:bdrdb:enterprisedb: 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. .. code:: shell 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: .. code:: shell 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-1: 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: .. code:: shell curl https://checkip.amazonaws.com __OUTPUT__ 89. 97.100.108 You also need the security groupid for your cluster. Run: .. code:: shell 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 --protocol tcp --port 6432 --cidr /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: .. code:: shell 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: .. code:: shell psql -h -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. .. code:: shell psql -h ,, -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://@:6432,:6432,: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. .. Warning 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.