Databases


Stateless services are simple and scalable, but most complex services eventually end up needing persistent state. Fortunately, you can store state in a fully managed PostgreSQL database on Render.

PostgreSQL is a powerful, open-source, object-relational database management system used globally by some of the world’s best technology companies. Render’s managed PostgreSQL offering makes it easy to use PostgreSQL in a secure, reliable, and completely hands-off way. Your database comes with encryption at rest, automated backups, and expandable SSD storage.

Getting Started

We have some examples backed by PostgreSQL to get you started:

Creating a Database

You can create a PostgreSQL database on Render in under a minute. Like web services, you can give your database a memorable name (which you can change at any time).

The database name and user name cannot be changed after creation. As noted below, we generate random values for them if you omit them.

New Database form. The Database and User fields are randomly generated unless user-specified.

Connecting to Your Database

How you connect to your database depends on your code: some frameworks expect a single connection string or URL in an environment variable, while others need multiple connection parameters in a configuration file. See Getting Started for examples.

At a minimum, your app will need to know your database’s hostname, port, username, password, and database name (e.g. mydb in the official tutorial).

Render uses the default PostgreSQL port of `5432` to connect. Usually, the port can be left unspecified.

The hostname will differ depending on where you’re connecting from. For services deployed on Render, you should always use the internal connection parameters shown below. This minimizes network latency and maximizes app performance.

The maximum number of simultaneous connections you can have to your database is based on the memory available with your plan:

MemoryMax Connections
<= 6GB97 connections
6GB to 10GB197 connections
>= 10GB397 connections

Databases created prior to August 11th, 2021 can support a maximum of 97 connections, regardless of memory. A configuration update to migrate to the connection limits noted above can be scheduled by emailing support@render.com.

Connecting From Apps on Render

Using internal connection values is the recommended way for your apps to connect to your Render database. The hostname, username, database, and password are displayed on the database page:

Top half of DB page showing hostname, username, database, password, and internal connection string

An internal connection string that looks like postgres://USER:PASSWORD@HOST:PORT/DATABASE is also available if needed. Many database frameworks allow (or require) a connection string instead of individual connection parameters.

Connecting From Outside Render

You might want to run ad-hoc queries or migrations against your database from machines outside Render. In these cases you can connect to your database using the external connection string on the database page.

Bottom half of DB page showing the (hidden) external connection string and PSQL command

Most database clients understand the external connection string, which (like the internal connection string) looks like postgres://USER:NAME@HOST:PORT/DATABASE. We also provide the PSQL command to connect to your database which can be copied and run in the terminal as is.

Connecting from outside of Render will result in decreased performance as compared to connecting from within Render, so you should always prefer to use the internal connection values where possible.

Access Control

New databases are accessible to any IP address with the right credentials. You can remove the default rule and add your own IP addresses or even disable all external access by removing all the rules in the data access section of your database page.

Database Access Control Section

You can specify blocks of IP addresses concisely using CIDR notation.

The rules apply to connections from outside Render’s network. Services within the same team or account can always use the internal connection string to access their databases.

Backups

No database is completely safe from data loss. This is why we take complete database backups every day and retain all backups for at least 7 days.

We take two kinds of snapshots: a SQL-based backup and a filesystem backup. SQL backups are enough for most purposes and can be restored using psql. You can find and download all backups on your database page in the dashboard.

Manual backups for databases on the Standard plan and above can be triggered at any time from the dashboard. On the backups tab, simply click “Trigger Backup” to initiate a database backup. Please note that backups can take a while to complete, especially for larger databases. You can only trigger a manual backup if another backup isn’t already in progress.

Database Backup Section

To recover your database from a backup, download the required .sql.gz file from the Backups tab.

# first unzip the backup file
$ gzip -d $backup_file

# then restore this backup to your database using its external connection string available in the dashboard.
$ psql $external_connection_string -f $backup_file

We’re also happy to help with restores and disaster recovery. Just contact us on Render Community or email us at support@render.com.

Disk Space

We offer database plans ranging from 1 GB to 256 GB of storage. We can always work with you to provide a custom plan; just email us at support@render.com.

Database Versions & Upgrades

Render currently supports PostgreSQL 11, 12, and 13. You can select the major version of your database at creation time, after which it cannot be changed.

We will periodically upgrade the minor version of your databases to apply the latest security fixes. For any maintenance periods that require downtime, we email you ahead of time and allow you to schedule when the maintenance should happen. We also allow you to run the maintenance yourself.

Migrating to a new PostgreSQL major version

At a high level, here are the steps to migrate your database to a newer version:

  1. Create a new database with the desired version.
  2. Disable or suspend any applications that write to your existing database. This prevents drift between the data in your database and that in your backup.
  3. Take a backup of your existing database.
  4. Restore that backup to your new database.
  5. Point your applications at the new database and re-enable them.

Depending on how your applications are set up, this operation may require some downtime.

You can take a backup of your database schema and the data in your tables using pg_dump. This command can be used to dump your database to a file (make sure to swap out the appropriate database variables, as well as the hostname for Frankfurt region databases):

PGPASSWORD={PASSWORD} pg_dump -h oregon-postgres.render.com -U {DATABASE_USER} {DATABASE_NAME} \
   -n public --no-owner > database_dump.sql

You can then restore this data to your new database:

PGPASSWORD={PASSWORD} psql -h oregon-postgres.render.com -U {DATABASE_USER} {DATABASE_NAME} < database_dump.sql

During the restore operation, you might see errors executing one or more statements against your new database. Some of these errors could be innocuous, such as errors granting permissions to a user that does not exist on your new database, while others might require manual intervention.

If certain statements fail to execute due to a version incompatibility, you may need to manually modify your database dump to resolve these issues. You can review the PostgreSQL 12 and PostgreSQL 13 changelogs in order to catch any such incompatibilities beforehand.

It’s a good idea to do a ‘dry run’ of your migration so that you can catch any potential issues before taking your production applications down. In most cases, you will want to stop writing data to your original database just before taking your backup so that there is no inconsistency with the data you restore to your new database.

Read Replicas

A read replica is a database instance that only allows read-only connections. As you write to your primary database, Render automatically replicates your data to your read replica. Read replicas reduce load on your primary database and make one-off queries safer. They are also a great option for data analysis tools that do not need to write to your primary database, or for running computationally expensive data analysis without impacting the performance of you primary instance.

Read replicas are available to all databases using the Standard plan and above. At this time we allow you to provision a single read replica per database, which will match the plan of your primary database.

Creating a read replica

You can add a read replica to an existing database in one click from the Render dashboard. Simply navigate to your database and click “Add Read Replica”.

Database Settings Page

Once you add a read replica, we’ll begin copying over any existing data from your primary database. Read replica creation should take no more than a few minutes. Please email us if it takes longer.

After your read replica moves into an “Available” state, you can connect to it just as you would your primary database, using either the internal or external connection string.

Read Replica Performance

Data written to your primary instance will be available on your read replica after a momentary delay, making read replicas ideal for applications that don’t require instant access to new data. The specific lag between data being written to the primary and being available on the replica will depend on your database usage and can be monitored in your database metrics.

Read Replicas vs. High Availability

Read replicas are distinct from highly-available databases, with different benefits. Read replicas allow you to decrease load on your primary instance, and are safer to use for ad hoc or expensive queries. In contrast, a highly available database setup helps reduce downtime in the event of database failure.

With a high availability setup, data is still copied to a replica; if the primary instance fails, the replica is typically ‘promoted’ to become the new primary. Render does not currently support promoting a read replica to a primary, and we do not automatically fail over to your read replica on primary instance failure. We plan to introduce highly-available database options soon.

Deleting Databases

If you decide to delete a database, we do not retain backups or snapshots of your data. Make sure to download a backup before deletion.

PostgreSQL Extensions

PostgreSQL 13

PostgreSQL 13 extensions can be added to your database using CREATE EXTENSION. The plpgsql extension is enabled by default, and all other available extensions can be enabled as needed. The following extensions are available on all PostgreSQL 13 databases.

PostgreSQL 11 and 12

PostgreSQL 11 and 12 extensions are enabled by default and cannot be customized. The following extensions are enabled on all PostgreSQL 11 and 12 databases by default.

Some of the extensions below (like postgis) create additional schemas (like topology) and tables (like spatial_ref_sys).

If you don’t need an extension and would like to drop it, feel free to email us and we’d be happy to delete it for you. We’re working on enabling users to add and drop extensions on their own.

[1] Because of its resource requirements, postgis is not available on the starter plan for PostgreSQL version 12.