PostgreSQL in Digitalocean droplet is an object-relational database that’s open source and focused on extensibility, data integrity, and speed.

Bobcares, as part of our DigitalOcean Managed Service, responds to all inquiries, big or small.

Let’s have a look at how our Support team demonstrated how to create PostgreSQL database, where to find connection details and how to use them to configure tools and clients.

PostgreSQL in Digitalocean droplet

PostgreSQL is fully ACID compliant thanks to its concurrency support, and it also supports dynamic loading and catalog-driven operations, allowing us to customise data types, functions, and more.

Create PostgreSQL Database Clusters

1. Firstly, login to the Digitalocean control panel.
2. Then, click Create and select Database. This takes us to the page where we can create a database.
3. Select the database cluster’s datacenter in the Choose a datacenter section.
4. Create the database in the same datacenter as our other DigitalOcean resources for optimal performance.
5. Then, choose PostgreSQL as the database engine in the Choose a database engine section.
6. Major versions 10, 11, and 12 are currently supported by Digitalocean, with the most recent minor version being selected by default. After creating a cluster, we are unable to change the PostgreSQL version.
7. Then, select the machine type, as well as the number and size of database nodes, in the Choose a cluster configuration section.
8. At any time, we can increase the number or size of database nodes. We are unable to downsize nodes due to data integrity concerns.
9. Then, choose a name for the cluster, the project to which it will be added, and any tags we want to use in the final section, Finalize and Create.
10. Finally, click the Create a Database Cluster button.

Connect to PostgreSQL Database Clusters

Use the connection details for our database to configure tools, applications, and resources that connect to it. Click the name of the cluster on the Databases page to go to its Overview page to see the connection details for our database. Based on how we want to connect to the database, we can see customised connection details:

• Depending on whether we want to connect via the cluster’s public or private hostname, the Public network and Private network (VPC) options generate connection details. The cluster’s private hostname can only be accessed by other resources in the same VPC network as it.
• Based on which database we want to connect to, the Database/Pool field updates the connection details.
• The User field updates the default connection details with the user credentials we want to use.

We can also choose between three different formats for viewing the connection details.

• Connection parameters: Database information for application configuration, such as DataGrip and pgAdmin connection configuration.
• Connection string:A condensed string that we can use on the command line to pass to a client.
• Flags:The connection variables are supplied as individual flags in this complete psql command.

We recommend the flags format because it is easy to read and can help us customise how we connect. Although we require only sslmode, PostgreSQL provides many options for customising connections.

For security reasons, the control panel does not reveal the cluster’s password by default. Copy the connection details, including the password, or show-password to reveal the password.

Download the SSL Certificate

Each managed database includes an SSL certficate. We can encrypt connections between our client applications and the database with this SSL certificate. Click the name of the cluster on the Databases page to go to its Overview page and download the database’s SSL certificate. Click Download CA certificate in the Connection Details section.

We can use the certificate’s location on our local system when configuring our client applications. Each client application is configured differently, so for more information on setting up SSL connections, consult the documentation for the tool we’re using.

Connect to the Database

A command line tool or a graphical user interface application can be used to connect to and manage the database (GUI). We will use the command line tool psql to connect to the database.

1. Paste the entire command from the control panel into the terminal to connect using the flags format: PGPASSWORD=our_password psql -U bcadmin -h cluster-bc-user-1357-0.db.domainname.com -p 25060 -d defaultdb --set=sslmode=require
2. When we connect successfully, our terminal switches to the psql prompt, which shows the database name:
   dbname=>

We can change databases or users from here, run SQL queries, and perform other database management tasks.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our Support team demonstrated how to create a PostgreSQL in digitalocean droplet and locate database connection information, and use it to configure tools and clients.