Vultr DocsLatest Articles

Vultr Managed Databases for PostgreSQL Reference Guide

Connection PoolingPostgreSQLManaged Databases
Updated on July 25, 2024
Vultr Managed Databases for PostgreSQL Reference Guide header image

Introduction

This reference guide for managed PostgreSQL covers all the essential information you need to administer and use your database. This guide assumes you've already deployed a PostgreSQL managed database by following our Quickstart guide and want to learn more.

To get started, navigate to the Databases section of my.vultr.com. Then, click your deployed database to open the administration area containing several tabs.

Overview Tab

The Overview tab contains several sections:

Connection Details

Use the Connection Details section to locate your database connection information. It looks like this:

Connection Details

Here, you'll find the username, password, host, port number, and database name. The password is hidden by default, but you can click the eye icon to reveal it.

Your other options are:

  • Databases: If you have multiple databases, choose one from the Databases/Pools drop-down.

  • Users: If you have multiple users, choose the user account from the Users drop-down.

  • Copy Connection String: Use this button to copy a connection string for psql to your clipboard. Here's an example:

      psql postgres://vultradmin:[YOUR_PASSWORD]@[YOUR_HOST]:18140/defaultdb

  • Copy PostgreSQL URL: Copies a PostgreSQL URL to your clipboard. It looks like this:

      postgres://vultradmin:[YOUR_PASSWORD]@[YOUR_HOST]:18140/defaultdb

  • Download Signed Certificate: If you need to download a signed certificate for your client, use this button.

Your connection string connects to the primary node and will automatically switch to the newly-elected primary node in case of a database failover.

You'll find many free and proprietary SQL clients at postgresql.org, including graphical, command-line, and web-based options for Linux, macOS, Windows, and mobile devices. If you enable connection pooling, note that some clients like pgAdmin do not support it. We recommend a client like TablePlus instead. You can learn more about connection pooling later in this article.

SSL Connections

To encrypt your connection with SSL, add ?sslmode=require to the end of your connection string like this:

$ psql postgres://vultradmin:[YOUR_PASSWORD]@[YOUR_HOST]:18140/defaultdb?sslmode=require

General Information

The General Information section of the Overview tab has two user-editable fields and other key information about your cluster. It looks like this:

General Information

  • To rename the cluster, click the Label and type a new name.
  • Click Tag to add a new tag to the cluster.

Trusted Sources

When you deploy a database cluster, it's open to all internet traffic. Use the Trusted Sources section to restrict access to your database cluster.

Trusted Sources

You should list allowed IP addresses in the Trusted Sources section. After you set a trusted IP address, all other traffic is blocked, similar to a firewall. Setting the trusted sources is a good security practice before you create your databases and add users.

To add a trusted source:

  1. Click Edit.
  2. Enter an IPv4 address. IPv6 is not supported.
  3. Click Save.

You can add multiple IP addresses at a time, separated by commas. When complete, your trusted sources list looks similar to:

Trusted Sources 2

Actions

In the Actions section of the Overview tab, you'll find:

  • Fork Database Cluster: When you fork a cluster, you create a second cluster from a snapshot of the original. You can change the number of nodes, the type, and the location of the new cluster.
  • Restore From Backup: Restoring from backup creates a new cluster without standby or read-only nodes.
  • Add Read-Only Replica Node: You can add a read-only replica in any of Vultr's locations for redundant data residency.

Resources

This section has links to documentation and support.

Usage Graphs Tab

Monitor the cluster's resource use in this tab.

Queries & Logs Tab

Use this tab to monitor the running queries, database statistics, and recent log entries.

Users & Databases Tab

Use this tab to manage the cluster's users, passwords, and databases. When you first deploy a cluster, it looks like this:

Users and Databases

  • The eye icon reveals the user's password.
  • The copy icon copies the password to your clipboard.
  • The Add New User button creates a new database user. Enter a username and password in the pop-up window. The system automatically generates a strong password if you leave the password blank.
  • Use the Reset Password button to reset a user's password.
  • The Add New Database button allows you to create a new database in the cluster.

Connection Pools Tab

PostgreSQL connection pooling allows you to support large numbers of connections while minimizing the consumption of database server resources. Vultr's managed PostgreSQL databases support connection pooling with PgBouncer. Use the following sections to learn more about connection pooling, why you might use it, and how to set it up.

What is Connection Pooling?

When you connect directly to a PostgreSQL server, each client connection requires a separate backend process on the server. With connection pooling, PgBouncer manages the client queries and only uses a limited number of actual backend connections, leading to lower resource usage on the server and better total performance.

Why Use Connection Pooling?

Because of how PostgreSQL manages client connections, many backend connections can consume a lot of resources. Without a connection pooler, the database connections are handled directly by PostgreSQL backend processes, with one process per connection. Adding a connection pooler allows the server to use fewer backend connections, freeing up server resources for essential operations like disk caching.

How to Enable Connection Pooling

To enable connection pooling, navigate to the managed database section of the customer portal.

  1. Click your PostgreSQL cluster to open the information page.
  2. Navigate to the Connection Pools tab.
  3. Click the Add New Connection Pool button.
  4. Enter a pool name.
  5. Select the database and user for the connection.
  6. Select a pool mode. See the next section to learn more about pool modes.
  7. Enter a number for the connection pool size.
  8. Click Create Pool.
  9. Click Check Your Connection to retrieve the connection string for your client application.
  • Pool names are alpha-numeric. Maximum length is 63 characters. No spaces are allowed.
  • Pool size is an integer value specifying the maximum number of simultaneous connections allowed by the pool.

Connection pooling modes

PgBouncer supports three different connection pool modes: transaction, session, and statement.

The default and recommended mode is transaction pooling, which allows each client connection to take its turn using a backend connection for the duration of a single transaction. After the transaction is committed, the backend connection is returned to the pool for the next waiting client connection. This is a good choice for typical transactions and is the most commonly used mode.

Some PostgreSQL features do not work correctly with transaction-based pooling. See the PgBouncer features page for details.

Session pooling holds client connections open until the client disconnects from the pooler, after which the server connection is added back to the pooler's free connection list. Client connections are accepted at the TCP level, but queries only proceed when another client disconnects and frees up its backend connection. This can be useful to create a wait queue for incoming connections but is of limited use for most scenarios because recycling the backend connections is a slow process.

Statement pooling is similar to transaction pooling, except that instead of allowing a full transaction to run, it cycles the server-side connections after each database statement. Transactions containing multiple SQL statements are not allowed in this mode.

Migration Tab

See the Managed Database Migration guide for more information about this section.

Settings Tab

Use the Settings tab to manage the cluster options. Choose from the following menus.

  • Upgrade Window: Set the preferred time for system upgrades.
  • Time Zone: Set the default timezone for your cluster.
  • Advanced Configuration: Allows you to set advanced configuration options for your cluster.
  • Available Extensions: Vultr Managed Databases for PostgreSQL comes packaged with several useful extensions you can toggle on or off. You'll find a list of extensions and instructions on activating them on this menu.
  • Change Plan: Scale up your database cluster.
  • Datacenter Location: Migrate your entire cluster to a new datacenter location.
  • Delete Managed Database:: Destroy the cluster and all backups.

Frequently Asked Questions

Is the database backed up?

Yes, all managed databases are backed up for disaster recovery purposes. In addition, all plans other than Hobbyist offer user-initiated recovery, forking, and point-in-time backups. You can use these backups to restore a cluster, which overwrites the current cluster's data. You can also fork a cluster from the backup, creating a new cluster with the backup data and not modifying the existing cluster. You'll find those options in the Actions section of your cluster's Overview tab.

PostgreSQL offers point-in-time recovery history; the duration available depends on your node plan.

  • Premium: 30 days
  • Business: 14 days
  • Startup: 2 days
  • Hobbyist: None

How do I find my Node Plan?

You can deploy managed databases in several node plans, which are a shorthand way of identifying the available size and number of nodes. We offer Hobbyist, Startup, Business, and Premium node plans.

After you deploy a managed database, look in the General Information section of your cluster's information page. The Node Plan appears below the Monthly Price. The plan name format is Vultr-Dbaas-[plan type]-[other internal information]. Here are two example node plans, with the important information underlined in red.

Premium node plan:

Premium Plan

Business node plan:

Business Plan

The node plan determines what backup and recovery options are available.

Can I deploy managed databases with the Vultr API or vultr-cli?

Support for Vultr API and vultr-cli is coming soon. Today, you can deploy managed databases from the Vultr customer portal.

How do I scale my database cluster?

  • Use the Change Plan menu on the Setting tab to scale your cluster up.
  • To scale a cluster down, migrate or fork it to a new, smaller cluster.

How do I create an admin (superuser) or root-level account?

You cannot create superuser accounts. To create a standard user account, use the Vultr customer portal.

Can I use multiple primary (write) nodes?

A cluster can only have one primary node. It can have multiple replica (read-only) nodes.

Do I need to use primary keys for my tables?

Yes, you must use primary keys for all database tables, which is enforced through the database configuration.

What versions of PostgreSQL are available?

We support PostgreSQL versions 11 through 14.

How do I enable extensions for my PostgreSQL database?

  • To check which extensions are available, connect to your database with psql and run:

      SELECT * FROM pg_available_extensions;

  • To enable an extension:

      CREATE EXTENSION extension_name;

  • To remove an extension:

      DROP EXTENSION extension_name;

More Resources