PostgreSQL

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 instance 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 for paid instances, and expandable SSD storage.

Getting Started

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

• Create a Django app
• Create a Rails app
• Create a Phoenix app

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).

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.

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:

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

To use the internal connection, the service and database need to be in the same account/team and region.

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.

Most database clients understand the external connection string, which (like the internal database URL) looks like postgres://USER:PASSWORD@EXTERNAL_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.

Connection Service Limits

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

Instances 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.

Access Control

New instances 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.

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

Multiple Databases In A Single PostgreSQL Instance

The simplest configuration used by most applications is to have a single database per PostgreSQL instance. However, Render also supports creating multiple databases in a single instance. You can create a new database inside your instance by connecting to it with psql and and using the CREATE DATABASE <name> command. You can connect to the newly created database the same way, postgres://USER:NAME@HOST:PORT/name. Make sure to use version psql >= 14.2 when connecting from outside Render.

Note: This feature is available by default on PostgreSQL instances created after October 30th 2022. If you’d like to create multiple databases in an instance created before October 30, 2022, please contact support with your database details so we can enable the feature on your instance.

Backups

Render offers two types of backups for your databases

1. Standard Backups offer a full backup of your database that you can download. These are snapshotted daily and can be downloaded to your local computer or stored in other locations outside of Render.
2. Point In Time Recovery offers continuous archiving of your database. This allows you to restore your database to a specific point in time over the last few days.

Standard 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 good for most purposes and can be restored using psql. Render’s SQL-based backups include SQL commands to drop databases and recreate them to prevent data duplication when restoring from a backup. Our SQL-based backup process uses pg_dump for instances with a single database, and pg_dumpall for instances with multiple databases. Backups using pg_dumpall have the --no-privileges flag enabled, and do not include GRANT or REVOKE commands. We exclude these commands to minimize errors when performing a restore operation. If you require custom access privileges in your databases, you can configure them after the restore operation is complete. Since pg_dumpall uses pg_dump internally, some diagnostic messages will mention pg_dump.

Filesystem-based backups include all the databases in your PostgreSQL instance.

Manual backups for databases on the Standard instance type 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.

Automated and manual backups can be found and downloaded from your backups tab in the dashboard.

If you would like more control over the frequency and storage of your backups, see our guide Backup PostgreSQL to Amazon S3. This guide will walk you through creating a Cron Job that will upload SQL backups from pg_dump to S3.

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.

Point-in-Time Recovery

With Point-in-Time Recovery (PITR), your database is continually archived. This can help you recover your database from unexpected data loss, for example, due to an unintentional table drop or deletion of data. With PITR, you can restore into a new database at the point before the data loss occurred, up to 7 days ago.

When you trigger a restore via PITR, your data are restored into a new database. This allows you to examine and validate the data before updating all of your services to use the new database. If you don’t find the data you were looking for, you can attempt a restore at a different time.

Your new database will inherit the instance type and IP Address allow list of the source database but will have a new default database name containing your data. The connection strings to connect to your database will be different and can be found in your new database settings page. You will need to manually delete your previous database after you have verified the new database is working as expected.

PITR is available for teams on Pro database instance types and above. If your database has been upgraded, you will only be able to restore to a time corresponding to when your database was on the Pro instance type or above.

Perform a Restore

To begin a restore of your database, go to the Backups section of your database settings and click the Restore Database button.

1. [Optional] Change the name of your new database, or use the suggested default.
2. Determine the date and time to which you want to restore your database. The time can be no later than 10 minutes prior to the current time.
3. Click Restore to initiate the restore, and you will be taken to the settings page of your new database.
4. Your database status will advance from Recovery In Progress to Creating, and then Available when it is ready to accept connections.
5. Validate that the data in your new database is what you expect.
6. Update all of the connection strings on your services to point to your new database.
7. Delete or Suspend your old database.

Disk Space

We offer PostgreSQL instance types ranging from 1 GB to 256 GB of storage per instance. The database storage limit applies to all storage the PostgreSQL database needs, including caches, indices, etc. Backup storage does not count toward your plan’s storage limit.

We can always work with you to provide a custom plan; just email us at support@render.com.

Metrics

Dashboard

You can view database metrics any time in the Render Dashboard. We currently support the following metrics:

Datadog

The Datadog integration will provide you with more metrics related to the Postgres instances host and disk. You will also be able to use the Datadog UI to create dashboards and alerts on your database.

See the Datadog integration docs for more details.

Instance Type Updates

You can update your instance type to a larger instance with more RAM, CPU, and Storage.

Need even more RAM, CPU, or Storage? Contact support for a custom instance type.

Downgrades

It’s not currently possible to downgrade Postgres instance types. Make sure to pick the instance type that works for you. If you do want to lower the database instance type you’ll need to create a new instance and restore a backup to it.

Database Versions & Upgrades

Render currently supports PostgreSQL 11, 12, 13, 14, and 15. 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

If you have multiple databases in your PostgreSQL instance, repeat the steps above for each database you wish to backup and restore. Alternatively, you can use pg_dumpall to automatically backup all databases in your instance.

Refer to Backups for more details regarding backup and restore.

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, PostgreSQL 13, PostgreSQL 14, and PostgreSQL 15 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 access. As you write to your primary instance, Render automatically replicates your data to your read replica, including all databases in your primary instance. Read replicas reduce load on your primary instance 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 instance, or for running computationally expensive data analysis without impacting the performance of you primary instance.

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

Creating a read replica

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

Once you add a read replica, we’ll begin copying over any existing data from your primary instance. 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 instance, 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 instance load and can be monitored in your metrics.

Read Replicas vs. High Availability

A PostgreSQL configuration with read replicas is distinct from a configuration for high-availability, providing 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 setup helps reduce downtime in the event of instance 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 configuration options soon.

Deleting Databases

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

PostgreSQL Extensions

PostgreSQL 13, 14, and 15

PostgreSQL 13, 14, and 15 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. Unless otherwise noted, the following extensions are available on all PostgreSQL 13, 14, and 15 databases.

• adminpack
• amcheck
• autoinc
• bloom
• btree_gin
• btree_gist
• citext
• cube
• dblink
• dict_int
• dict_xsyn
• earthdistance
• file_fdw
• fuzzystrmatch
• hstore
• hstore_plperl
• hstore_plperlu
• insert_username
• intagg
• intarray
• isn
• jsonb_plperl
• jsonb_plperlu
• lo
• ltree
• moddatetime
• old_snapshot ^[1]
• pageinspect
• pg_buffercache
• pg_freespacemap
• pg_ivm ^[2]
• pg_prewarm
• pg_similarity
• pg_surgery ^[3]
• pg_stat_statements
• pg_trgm
• pg_visibility
• pgaudit
• pgcrypto
• pgrowlocks
• pgstattuple
• plpgsql
• postgis
• postgis_raster
• postgis_tiger_geocoder
• postgis_topology
• postgres_fdw
• refint
• seg
• sslinfo
• tablefunc
• tcn
• timescaledb ^[4]
• tsm_system_rows
• tsm_system_time
• unaccent
• uuid-ossp
• xml2

[1] old_snapshot is only available for PostgreSQL 14, and 15
[2] pg_ivm is available for PostgreSQL databases created after March 8, 2023
[3] pg_surgery is only available for PostgreSQL 14, and 15
[4] timescaledb is not available for PostgreSQL 15 databases created before January 12, 2023, as well as PostgreSQL 14 databases created before February 19, 2022

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.

• bloom
• btree_gin
• btree_gist
• citext
• cube
• dblink
• dict_int
• dict_xsyn
• earthdistance
• fuzzystrmatch
• hstore
• intagg
• intarray
• isn
• lo
• ltree
• pg_buffercache
• pg_prewarm
• pg_stat_statements
• pg_trgm
• pgcrypto
• pgrowlocks
• pgstattuple
• postgis ^[4]
• postgis_tiger_geocoder
• postgis_topology
• tablefunc
• unaccent
• uuid-ossp

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.

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

Connection Pooling

Postgres Databases have limits on the number of concurrent connections that are available to use on the database. For high traffic services, you can setup connection pooling using PgBouncer. Your application will connect to your PgBouncer instance, and PgBouncer will automatically pool your application connections to your database.

Deploy to Render

There are two ways to deploy PgBouncer on Render, either by declaring PgBouncer within your repository using a render.yaml file or by manually setting up PgBouncer using the dashboard. In this tutorial, we will walk through both options.

Use render.yaml for Deploys

1. Create a file named render.yaml in the root of your directory. The file will define your PgBouncer and the Database used by your application. Don’t forget to commit and push it to your remote repository.

   databases:
     - name: mysite
       databaseName: mysite
       user: mysite
   
    services:
    - type: pserv
      name: pgbouncer
      env: docker
      plan: standard
      repo: https://github.com/render-oss/docker-pgbouncer
      envVars:
      - key: DATABASE_URL
        fromDatabase:
          name: mysite
          property: connectionString
      - key: POOL_MODE
        value: transaction
      - key: SERVER_RESET_QUERY
        value: DISCARD ALL
      - key: MAX_CLIENT_CONN
        value: 500
      - key: DEFAULT_POOL_SIZE
        value: 50

2. On the Render Dashboard, go to the Blueprint page and click New Blueprint Instance button. Select your application repository (give Render permission to access it if you haven’t already) and click Approve on the next screen.

That’s it! Your database and PgBouncer instance will be setup. You can navigate to the pgbouncer service to find the URL that your applications should connect to.

Manual Deployment

1. Create a new PostgreSQL database on Render. Note your database internal database URL; you will need it later.

2. Create a new Private Service, pointing it to the PgBouncer Docker Image from Render: https://github.com/render-oss/docker-pgbouncer

3. Select Docker for the environment

4. Add the following environment variables under Advanced:

   Key	Value
   DATABASE_URL	The internal database URL for the database you created above
   POOL_MODE	transaction
   SERVER_RESET_QUERY	DISCARD ALL
   MAX_CLIENT_CONN	500
   DEFAULT_POOL_SIZE	50

That’s it! Save your private service to deploy your PgBouncer instance on Render.