Как подключить postgresql к heroku
Хостинг PostgreSQL с Heroku
PostgreSQL — это зрелый, стабильный, многофункциональный сервер баз данных. В отличие от своих конкурентов с открытым исходным кодом MySQL и Firebird, PostgreSQL имеет более крутой курс обучения, когда вы только начинаете, но вы можете избежать большинства проблем при установке, настройке и обслуживании, если используете службу баз данных, размещенную в облаке.
Heroku Postgres — это надежная и мощная база данных как услуга, основанная на PostgreSQL, предоставляемая Heroku. Размещение ваших данных с Heroku позволяет вам больше сосредоточиться на написании SQL-кода вашего приложения, а не на том, как стать администратором баз данных.
В этой статье я покажу вам, как начать работу с Heroku Postgres. Я поделюсь некоторыми преимуществами использования Heroku, покажу, как зарегистрироваться, и покажу, как легко настроить размещенный экземпляр базы данных PostgreSQL и подключиться к нему из PHP с помощью PDO.
Почему Героку?
Перед лицом серьезного выбора неплохо взвесить все за и против, прежде чем принимать окончательное решение. В основе практически любого веб-приложения в настоящее время лежат его данные, поэтому выбор правильной стратегии базы данных, вероятно, важнее, чем думает большинство людей. Heroku Postgres может не подойти вам, но как вы узнали?
Heroku Postgres — это многоуровневое предложение, самое низкое из которых стоит 200 долларов в месяц. Если вы разработчик, который хочет просто поработать и узнать что-то новое, это может стать для вас преградой. (Было бы неплохо, если бы Heroku могла предложить какую-то ограниченную бесплатную пробную версию для разработчиков в будущем, чтобы больше людей могли научиться работать со своими предложениями.) С другой стороны, 200 долларов — это всего лишь капля в море для успешного веб-приложение. Это определенно дешевле, чем использовать собственное оборудование и расходы на персонал для поддержания базы данных и соблюдения любых соответствующих норм.
Создать экземпляр базы данных невероятно легко, и вы можете обновить или понизить уровень в соответствии с вашими потребностями. На самом деле, простота создания базы данных с помощью Heroku Postgres облегчает одну из часто встречающихся жалоб на PostgreSQL — ее трудно установить. Heroku претендует на звание крупнейшего поставщика услуг баз данных PostgreSQL. Учитывая эту шкалу, имеет смысл позволить экспертам управлять вашей базой данных.
Heroku обещает 99,99% безотказной работы, и сделал некоторые впечатляющие вещи для достижения этой цели. Транзакции постоянно архивируются в географически распределенном хранилище данных, и каждые 30 секунд выполняются автоматические проверки работоспособности, чтобы обеспечить доступность вашей базы данных. Вы вводите одну команду, а Heroku буквально заботится обо всем остальном.
Heroku допускает практически неограниченное количество данных, поэтому большие наборы данных и хранилища данных не являются проблемой. Их планы оцениваются в соответствии с оперативным набором данных, той частью ваших данных, к которой обращаются чаще всего и которая остается в оперативной памяти по соображениям производительности. Самый низкий уровень поддерживает 1,7 ГБ горячих данных, а самый высокий — 68 ГБ.
Настройка экземпляра базы данных
Зарегистрироваться на сервис Heroku Postgres очень просто. Просто зайдите на postgres.heroku.com в вашем браузере и нажмите кнопку Зарегистрироваться сейчас в середине страницы. Если у вас уже есть учетная запись Heroku, вы можете просто указать адрес электронной почты и пароль, которые вы использовали для нее, и вы войдете в систему. В противном случае вы можете создать новую учетную запись сейчас.
Конечно, вам нужно будет предоставить данные вашей кредитной карты, чтобы оплатить услугу Heroku Postgres. Если вы только что создали новую учетную запись, вы попадете на страницу «Проверка учетной записи», где вы сможете предоставить необходимую информацию.
После того как вы вошли в систему и подтвердили, и если вы еще не просматриваете ее, перейдите на страницу «Ваши базы данных», доступную из записи «Ваши БД» в верхнем меню. Эта страница является информационной панелью, с которой вы можете отслеживать и управлять всеми вашими экземплярами базы данных.
Нажмите +, на который указывает стрелка, чтобы добавить базу данных; вам будет представлен список планов, из которых вы можете выбрать. Выберите нужный план, а затем кнопку «Добавить базу данных», чтобы подтвердить выбор.
How to use PGAdmin to connect to a Heroku database
Throughout my app development journeys I have had various instances where I have had to incorporate a database of some kind. I first started out using MongoDB and MLab as a way to store my data. However, I am use to using SQL Server Management Studio while at work and since MongoDB is NoSQL I found it difficult at times to be able to do what I wanted with the apps as some of my functionality required relationships. However, I have found an alternate solution to my issues, and that is PostGre SQL which is a free and open source relational database management system which uses pgadmin as a tool to manage your databases.
When writing APIs that act as middle men between my apps front end and the backend I found it cumbersome initially when setting up the way in which i would interact with my data using PG Admin so I thought I would write an article/tutorial to help others set this up as when i was looking for answers/guides on this topic I couldn’t find much! So here goes.
Note: I have already downloaded postgre sql and installed the necessary packages. You can find the download link for the software and documentation here: https://www.postgresql.org
After you have downloaded the necessary software you will need to create a heroku account if you haven’t already. (One thing with heroku is that it only lets you have up to 5 projects on your account if you don’t have a debit card or credit file associated with your account, otherwise I believe you can have up to 200 projects/databases on your heroku account)
You can sign up or login to heroku here:
Heroku | Sign up
Create apps, connect databases and add-on services, and collaborate on your apps, for free. A platform for apps, with…
Once you have created your account and logged in locate to your accounts dashboard (You can find this by clicking on square that is made of 9 dots to the left of your account profile picture and then clicking “dashboard”). You should then click the “new” button then click the button that shows labelled “create new app”. You will now need to enter a unique name for your app, I am just calling mine “testapp400” for now. Then click “create app”. This should then generate an app and showcase to you various tabs labelled “overview”, “resources”, “deploy”, “metrics”, “activity”, “access” and “settings”. Click on the tab “resources”.
In the add-ons search bar enter “PostgreSQL, click the top option that appears. A popup like the below should appear.
Choose what plan you want for the resource, I just choose the default one of hobby then click the button labelled “provision”.
This then attaches post-gre sql as at database to your app/project.
Still on the “resources” tab you should now see “Heroku Postgres” as an add on and the text “Attached as database”. Click on the add on icon, this will open another tab that will describe various elements regarding your database. If you click on the tab “settings” you will see a heading labelled “database credentials”, if you click the button to the right of the heading pieces of information such as host, username and password will show these are the credentials you will need to use when wanting to use the database via pgadmin.
If you have installed pgadmin correctly then a tab at the top of your window should show an icon that looks like an elephant. Click this and then click “new pgadmin 4 window”.
A new tab in your browser should load with a pgadmin 4 link
Right click on the “servers” on the left and then click “create new server”.
This should then bring up a window like the below
You can set the name of the server to be whatever you like. Once you have set the name click on the tab “connection”.
All of the information including Host name/address, database, username and password are the unique credentials that were generated for your database app via heroku that can be found on the page you visited earlier when clicking on the “Heroku Postgres” icon in the add-ons section as you can see in the image below.
When you have entered the information, find the database name of your app it will most likely start with a d4, click on the “advanced” tab and then paste the database name into the textbox labelled DB restriction. This will filter out all of the databases that get loaded so only your database shows on this server as Heroku does something strange and loads all of the databases that have been made with it but nobody can access yours so no need to worry about unauthorized access. Click the save button and you should now have access to your heroku database using PGAdmin.
As you can see from the above I have a server setup and if you click on that server it should open various headers such as databases, group roles and table spaces. You can now create tables within this database and do whatever you like by clicking on the “schemas” heading.
There you have it, a Heroku app/database and the ability to edit it using PG Admin. I hope you found this article helpful and informative, in the next tutorial I will show you how can communicate with your database via a NodeJS API.
Categories
Last updated December 08, 2021
Heroku Postgres databases are designed to be used with a Heroku app. All Heroku Postgres databases have a corresponding Heroku application. You can find the application name on the database page at data.heroku.com. Your database is attached to the Heroku app and is accessible via an app config var containing the database URL, even if you host no code in the application itself. This variable is managed by Heroku, and is the primary way we tell you about your database’s network location and credentials.
However, except for private and shield tier databases, Heroku Postgres databases are accessible from anywhere and can be used from any application using standard Postgres clients. For private databases, outside access can be enabled using Mutual TLS.
To make effective use of Heroku Postgres databases outside of a Heroku application, keep in mind the following:
Don’t copy and paste credentials to a separate environment or app code
The database URL is managed by Heroku and will change under some circumstances such as:
Always fetch the database URL config var from the corresponding Heroku app when your application starts. For example, you may follow 12Factor application configuration principles by using the Heroku CLI and invoke your process like so:
This way, you ensure your process or application always has correct database credentials.
Attach the database as an add-on to other Heroku apps
If you are connecting to a database from other Heroku apps, you can now attach a database add-on directly to multiple applications. This ensures that any changes to the database’s URL will automatically propagate to your other apps.
Enable SSL
Applications must support and enable SSL to connect to a Heroku Postgres database. Most clients connect over SSL by default, but sometimes it’s necessary to add the sslmode=require query parameter to your database URL before connecting.
Be sure to append the sslmode=require parameter to your database’s URL from code, rather than by editing the value of your DATABASE_URL config var directly. Various automated events (such as a failover) can change the value of the config var, which overwrites any edits you make.
Use Heroku Postgres backups
You can use Heroku Postgres Backups on the associated Heroku app in order to get automated backups on your database. Heroku Postgres Backups takes backups of the database pointed at by DATABASE_URL in the Heroku app, so make sure you promote your database:
Categories
Last updated October 21, 2021
Table of Contents
On the surface, PG Backups provides a way to capture regular backups of your Heroku Postgres database. However, because of its general-purpose architecture and use of standard PostgreSQL utilities, it is also a useful tool capable of exporting to or importing from external PostgreSQL databases.
An alternative to using the dump and restore method of import/export if you have a Postgres instance on your local machine is to use the pg:push and pg:pull CLI commands to automate the process.
Export
In general, PGBackups are intended for moderately loaded databases up to 20 GB. Contention for the I/O, memory and CPU needed for backing up a larger database becomes prohibitive at a moderate load and the longer run time increases the chance of an error that will end your backup capture prematurely. For databases that are larger than 20 GB, see Capturing Logical Backups on Larger Databases.
Download backup
To export the data from your Heroku Postgres database, create a new backup and download it.
Restore to local database
This will usually generate some warnings, due to differences between your Heroku database and a local database, but they are generally safe to ignore.
Import
PG Backups can be used as a convenient tool to import database dumps from other sources into your Heroku Postgres database.
If you are importing data as part of the initialization of a new application you will need to first create and configure the app on Heroku before performing the import.
Create dump file
Dump your local database in compressed format using the open source pg_dump tool:
Import to Heroku Postgres
In order for PG Backups to access and import your dump file you will need to upload it somewhere with an HTTP-accessible URL. We recommend using Amazon S3 with a signed url.
Note that the pg:backups restore command drops any tables and other database objects before recreating them.
Generate a signed URL using the aws console:
Use the raw file URL in the pg:backups restore command:
DATABASE_URL represents the HEROKU_POSTGRESQL_COLOR_URL of the database you wish to restore to. You must specify a database configuration variable to restore the database.
If you’re using a Unix-like operating system be sure to use single quotes around the temporary S3 URL, because it might contain ampersands and other characters that will confuse your shell. If you’re running Windows, you must use double-quotes.
When you have completed the import process, delete the dump file from its storage location if it’s no longer needed.
Categories
Last updated December 20, 2021
Table of Contents
Heroku Postgres is a managed SQL database service provided directly by Heroku. You can access a Heroku Postgres database from any language with a PostgreSQL driver, including all languages officially supported by Heroku.
In addition to a variety of management commands available via the Heroku CLI, Heroku Postgres provides a web dashboard, the ability to share queries with dataclips, and several other helpful features.
Understanding Heroku Postgres Plans
Heroku Postgres offers a variety of plans, spread across different tiers of service: hobby, standard, premium, and enterprise. For more information on what each plan provides, see Choosing the Right Heroku Postgres Plan.
Pricing information for Heroku Postgres plans is available on the Heroku Postgres add-on page.
If your app’s requirements eventually outgrow the resources provided by the initial plan you select, you can easily upgrade your database.
Provisioning Heroku Postgres
Before you provision Heroku Postgres, confirm that it isn’t already provisioned for your app. Heroku automatically provisions Postgres for apps that include certain libraries, such as the pg Ruby gem.
Use the heroku addons command to determine whether your app already has Heroku Postgres provisioned:
If heroku-postgresql doesn’t appear in your app’s list of add-ons, you can provision it with the following CLI command:
For example, to provision a hobby-dev plan database:
Depending on the plan you choose, your database can take up to 5 minutes to become available. You can track its status with the heroku pg:wait command, which blocks until your database is ready to use.
As part of the provisioning process, a DATABASE_URL config var is added to your app’s configuration. DATABASE_URL contains the URL your app uses to access the database. If your app already has a Heroku Postgres database and you’ve provisioned another one, this config var’s name instead has the format HEROKU_POSTGRESQL_ _URL (for example, HEROKU_POSTGRESQL_YELLOW_URL ).
You can confirm the names and values of your app’s config vars with the heroku config command.
The value of your app’s DATABASE_URL config var can change at any time. Do not rely on this value either inside or outside your Heroku app.
At this point, an empty PostgreSQL database is provisioned. To populate it with data from an existing data source, see the import instructions or follow the language-specific instructions in this article to connect from your application.
Local Setup
Heroku recommends running Postgres locally to ensure parity between environments. There are several pre-packaged installers for installing PostgreSQL in your local environment.
After Postgres is installed and you can connect, you must export the DATABASE_URL environment variable for your app to connect to it when running locally:
Postgres will to the local database matching your user account name (which is set up as part of installation).
Set up Postgres on Mac
Postgres.app requires Mac OS 10.7 or above.
If you’re using version 10, the output will look similar to:
This command should work correctly:
Also verify that the app is set to automatically start at login.
Set up Postgres on Windows
Install Postgres on Windows by using the Windows installer.
Set up Postgres on Linux
Install Postgres via your package manager. The actual package manager command you use depends on your distribution. The following works on Ubuntu, Debian, and other Debian-derived distributions:
If you don’t have a package manager on your distribution or the Postgres package isn’t available, install Postgres on Linux using one of the Generic installers.
The psql client is typically installed in /usr/bin :
The following command should work correctly:
Designating a Primary Database
The DATABASE_URL config var designates the URL of an app’s primary Heroku Postgres database. For apps with a single database, its URL is automatically assigned to this config var.
Sharing Heroku Postgres between Applications
You can share a single Heroku Postgres database between multiple apps with the heroku addons:attach command:
A shared database isn’t necessarily the primary database for any given app that it’s shared with. You promote a shared database with the same command that you use for any other database.
You can stop sharing your Heroku Postgres instance with another app with the heroku addons:detach command:
Version Support
The PostgreSQL project releases new major versions on a yearly basis. Each major version is supported by Heroku Postgres shortly after its release.
Heroku Postgres supports at least 3 major versions at a given time. Currently supported versions are:
Users are required to upgrade roughly one time every three years. However, you can upgrade your database at any point to gain the benefits of the latest version.
Migration of Deprecated Databases
The PostgreSQL project stops supporting a major version five years after its initial release. Heroku Postgres deprecates these versions to ensure no databases run on an unsupported major version of PostgreSQL.
Heroku highly recommends that you perform the version upgrade or update before support ends so that you can test compatibility, plan for unforeseen issues, and migrate your database on your own schedule.
Legacy Infrastructure
Heroku also occasionally deprecates old versions of its infrastructure for the following reasons:
To see if your database is running on legacy infrastructure, use pg:info :
Performance Analytics
Performance analytics is the visibility suite for Heroku Postgres. It enables you to monitor the performance of your database and diagnose potential problems. It consists of several components:
Expensive Queries
The leading cause of poor database performance is unoptimized queries. The list of your most expensive queries, available through data.heroku.com, helps to identify and understand the queries that take the most time in your database. Full documentation is available here.
Logging
If your application/framework emits logs on database access, you can retrieve them through Heroku’s log-stream:
To minimize impact on database performance, logs are delivered on a best-effort basis.
pg:diagnose
pg:diagnose performs a number of useful health and diagnostic checks that help analyze and optimize database performance. It produces a report that you can share with teammates or Heroku Support.
Carefully consider the impact to your database and application before making any changes.
pg:diagnose isn’t supported on shield plans.
Check: Connection Count
Each Postgres connection requires memory, and database plans have a limit on the number of connections they can accept. If you’re using too many connections, consider using a connection pooler such as PgBouncer or migrating to a larger plan with more RAM.
Checks: Long Running Queries, Long Transactions, Idle in Transaction
Long-running queries and transactions can cause problems with bloat that prevent auto vacuuming and cause followers to lag behind. The reporting threshold for these queries and transactions is 1 minute (60 seconds).
Check: Indexes
The Indexes check includes three classes of indexes.
Never Used Indexes haven’t been used (since the last manual database statistics refresh). These indexes are typically safe to drop, unless they are in use on a follower.
Low Scans, High Writes indexes are used, but infrequently relative to their write volume. Indexes are updated on every write, so are especially costly on a high write table. Consider the cost of slower writes against the performance improvements that these indexes provide.
Seldom used Large Indexes aren’t often used, but take up significant space both on disk and in cache (RAM). These indexes can still be important to your application. For example, if they’re used by periodic jobs or infrequent traffic patterns.
Index usage is only tracked on the database receiving the query. If you use followers for reads, this check won’t account for usage made against the follower and is likely inaccurate.
Check: Bloat
Because Postgres uses MVCC, old versions of updated or deleted rows are made invisible rather than modified in place. Under normal operation an auto vacuum process goes through and asynchronously cleans these up. Sometimes, however, it can’t work fast enough or prevent some tables from becoming bloated. High bloat can slow down queries, waste space, and even increase load as the database spends more time looking through dead rows.
The bloat estimation calculation is not always accurate for tables that use columns that don’t have column statistics, such as json columns.
Check: Hit Rate
Checks the overall index hit rate, the overall cache hit rate, and the individual index hit rate per table. Databases with lower cache hit rates perform significantly worse because they have to hit disk instead of reading from memory. Consider migrating to a larger plan for low cache hit rates, and adding appropriate indexes for low index hit rates.
The overall cache hit rate is calculated as a ratio of table data blocks fetched from the Postgres buffer cache against the sum of cached blocks and uncached blocks read from disk. On larger plans, the cache hit ratio can be lower but performance remains constant, as the remainder of the data is cached in memory by the OS rather than Postgres.
The overall index hit rate is calculated as a ratio of index blocks fetched from the Postgres buffer cache against the sum of cached indexed blocks and uncached index blocks read from disk. On larger plans, the index hit ratio can be lower, but performance remains constant because the remainder of the index data is cached in memory by the OS rather than Postgres.
The individual index hit rate per table is calculated as a ratio of index scans against a table versus the sum of sequential scans and index scans against the table.
Check: Blocking Queries
Check: Sequences
Aside from composite indexes, you do not need to prefer integer columns over bigint columns on Heroku Postgres for alignment on 64-bit systems.
This check is skipped if there are more than 100 integer (int4) columns.
Check: Table Transaction ID Wraparound, Database Transaction ID Wraparound
These checks determine how close individual tables are, or a database is, to transaction ID wraparound. Transaction ID wraparound is a rare scenario in which due to autovacuum operations being unable to keep up on frequently updated tables, these tables are in danger of the transaction ID for that table, or database, wrapping around and resulting in data loss. To prevent transaction ID wraparound, Postgres prevents new writes cluster wide until the issue is resolved, impacting availability. These checks return the table and database names if over 50% of the transaction ID space has been used.
Check: Schema Count
This check counts the number of schema in the database. It reports a yellow warning if there are more than 19 schema, and a red warning if there are more than 50 schema. A large number of schema can impact Postgres performance and the ability to take successful logical backups. We recommend you maintain no more than 50 schema.
Check: Load
There are many reasons load can be high on a database: bloat, CPU intensive queries, index building, and too much activity on the database. Review your access patterns, and consider migrating to a larger plan with a more powerful processor.
Check: CPU Burst
Heroku Postgres plans that have Burstable vCPU Performance use a “bucket” of CPU resources when utilization is beyond the baseline of
20%. This check returns:
Check: Disk Burst
Heroku Postgres plans that have Burstable Disk Performance use a “bucket” of disk IOPS when utilization is beyond the baseline, depending on plan. This check returns:
Resetting Statistics
You can reset the internal Postgres statistics to make it easier to see the effects of changes.
Using the CLI
Heroku Postgres is integrated directly into the Heroku CLI and offers many helpful commands that simplify common database tasks.
pg:info
To see all PostgreSQL databases provisioned by your application and the identifying characteristics of each (such as database size, status, number of tables, and PG version), use the heroku pg:info command:
To continuously monitor the status of your database, pass pg:info through the unix watch command:
pg:psql
psql is the native PostgreSQL interactive terminal and is used to execute queries and issue commands to the connected database.
The database located at DATABASE_URL is used by default. If you have more than one database, specify the database you want to connect to as the first argument to the command. You can use just the color as a shorthand:
pg:push and pg:pull
For a more in-depth guide on working with backups, read the import and export guide.
pg:pull
pg:pull can be used to pull remote data from a Heroku Postgres database to a database on your local machine. The command looks like this:
If providing a Postgres user or password for your local DB is necessary, use the appropriate environment variables like so:
pg:push
pg:push pushes data from a local database into a remote Heroku Postgres database. The command looks like this:
Troubleshooting
pg:ps, pg:kill, pg:killall
These commands give you view and control over currently running queries.
The pg:ps command queries the pg_stat_activity view in Postgres to give a concise view into currently running queries.
pg:killall is similar to pg:kill except it cancels or terminates every query on your database.
pg:promote
Promote a database to be the primary for your app with the heroku pg:promote command.
pg:promote updates the value of the DATABASE_URL config var with the newly promoted database’s connection string. It also creates an alternate attachment for the old primary database, assigned with a new HEROKU_POSTGRESQL_ _URL config var. The promotion process triggers a release and restarts the app.
Running pg:promote for a follower database doesn’t automatically make it unfollow its leader. Run pg:unfollow on followers to stop replication before promoting it.
Promotions When Using Connection Pooling
pg:credentials
Heroku Postgres provides convenient access to the credentials and location of your database. This makes it easier to use a GUI to access your instance.
The database name argument must be provided with pg:credentials:url command. Use DATABASE for your primary database.
It’s a good security practice to rotate the credentials for important services on a regular basis. On Heroku Postgres, you can use heroku pg:credentials:rotate to rotate credentials.
When you issue this command, new credentials are created for your database and the related config vars on your Heroku application are updated. However, on Standard, Premium, and Enterprise tier databases, old credentials aren’t removed immediately. All of the open connections remain open until the currently running tasks complete, then those credentials are updated. This ensures background jobs or other workers running on your production environment aren’t abruptly terminated, potentially leaving the system in an inconsistent state.
pg:reset
Connecting to Postgres
Heroku Postgres & SSL
Most clients will connect over SSL by default, but on occasion it’s necessary to set the sslmode=require parameter on a Postgres connection. Add this parameter in code rather than editing the config var directly. We recommend you enforce SLL, especially if you’re using Java or Node.js clients.
Connecting in Java
There are a variety of ways to create a connection to a Heroku Postgres database, depending on the Java framework in use. In most cases, the environment variable JDBC_DATABASE_URL can be used directly as described in the article Connecting to Relational Databases on Heroku with Java. For example:
When it isn’t possible to use the JDBC URL (usually because custom buildpack is being used), you must use the DATABASE_URL environment URL to determine connection information. Some examples are provided below.
By default, Heroku attempts to enable SSL for the PostgreSQL JDBC driver by setting the property sslmode=require globally. If you’re building the JDBC URL yourself (such as by parsing the DATABASE_URL ), we recommend explicitly adding this parameter.
It’s also important that you use a version of the Postgres JDBC driver version 9.2 or greater. For example, in Maven add the following to your pom.xml :
Examples of all outlined connection methods here are available on GitHub at: https://github.com/heroku/devcenter-java-database
Create a JDBC connection to Heroku Postgres by parsing the DATABASE_URL environment variable.
Spring/XML
Spring/Java
Alternatively you can use Java for configuration of the BasicDataSource in Spring:
The DATABASE_URL for the Heroku Postgres add-on follows this naming convention:
However the Postgres JDBC driver uses the following convention:
Remote Connections
You can connect to your Heroku Postgres database remotely for maintenance and debugging purposes. However, doing so requires that you use an SSL connection. Your JDBC connection URL must include the following URL parameter:
If you leave off sslmode=require and attempt to connect to a production-tier databases, you will receive a connection error..
You must add this parameter in code rather than editing the config var directly. Automated events such as failover can change the config var, overwiring your edits.
Click here for more information see the Dev Center article on Connecting to Relational Databases on Heroku with Java.
Connecting in Ruby
Run bundle install to download and resolve all dependencies.
Connecting in Rails
To use PostgreSQL locally with a Rails app your database.yml must contain the following configuration:
Connecting in JRuby
Run bundle install to download and resolve all dependencies.
If using Rails, follow the instructions for Connecting with Rails.
Connecting in Python
To use PostgreSQL as your database in Python applications you must use the psycopg2 package.
And use this package to connect to DATABASE_URL in your code.
Connecting with Django
Be sure to add psycopg2-binary and dj-database-url to your requirements.txt file as well.
To parse the values of the DATABASE_URL environment variable and convert them to something Django can understand, add the following to the bottom of settings.py :
Connecting in Go
Go apps can connect to Heroku-Postgres by providing the pq Postgres database driver to their query interface of choice (such as the standard database/sql). Your app will use the query interface, rather than using the driver directly.
Standard Usage (database/sql)
SSL is required to connect to Heroku-Postgres. pq automatically sets sslmode=require, but if you use another library, you must configure ssl explicitly.
Beyond the Standard Lib
For lower-level access to Postgres, you can use pgx.
For time-saving extensions to database/sql, you can use sqlx.
Connecting in PHP
General Considerations
If a framework or library cannot natively handle database URLs, use the parse_url() function to parse the DATABASE_URL environment variables you need to connection. You use parse_url()1 to separate out arguments for user, pass, host, port, database name, and other values.
The leading slash must be trimmed from the path component since it contains the database name.
The resulting associative array contains the information from the URL, as documented, with the database name available through the “ path ” key.
Connecting with the pgsql Extension
The pgsql extension passes connection strings directly to the underlying libpq library, which supports URL-style connection strings, so the DATABASE_URL environment variable can be used directly:
Connecting with PDO
A DSN must be constructed to connect using PDO:
Connecting with Laravel
The config/database.php file returns an array of database connection info to the framework; it can simply be amended to call parse_url() on the DATABASE_URL environment variable first, and return the extracted data:
Connecting with Symfony 3
The DATABASE_URL environment variable can be referenced in config.yml ; Symfony’s DoctrineBundle automatically parses the URL’s contents.
Connecting with Symfony 4
Symfony 4 automatically picks up the DATABASE_URL environment variable without further configuration.
Connecting in Node.js
Install the pg NPM module as a dependency:
Then, connect to process.env.DATABASE_URL :
Connection Permissions
Heroku runs the following SQL to create a user and database for you.
Multiple Schemas
Heroku Postgres supports multiple schemas and doesn’t place any limits on the number of schemas you can create.
The most common use case for using multiple schemas in a database is building a software-as-a-service application wherein each customer has their own schema. While this technique seems compelling, we strongly recommend against it as it has caused numerous cases of operational problems. For instance, even a moderate number of schemas (> 50) can severely impact the performance of Heroku’s database snapshots tool, PG Backups.
External Connections (Ingress)
In addition to being available to the Heroku runtime, Heroku Postgres databases can be accessed directly by clients running on your local computer or elsewhere.
You can retrieve the PG connection string in one of two ways. heroku pg:credentials (discussed above):
Also, the connection string is exposed as a config var for your app:
Migrating between Plans
Data Residency
When a database gets provisioned, the data associated with that database is stored within the region in which it’s created. However, services ancillary to Heroku Postgres and the systems managing the database fleet might not be located within the same region as the provisioned databases:
Blocking Logs
At add-on creation time, a flag can be passed to prevent logging of queries that get run against the database. If this option is turned on, it can’t be turned off after the database has been provisioned. If you must turn it off after it has been turned on, a migration to a new database will be required.
Blocking the queries in the logs reduces Heroku’s ability to help debug applications and tune application performance.
Removing the Add-on
You must remove the add-on to destroy your Heroku Postgres database.
Databases can’t be reconstituted after being destroyed. Take a snapshot of the data beforehand using PG Backups or by exporting the data
Support
All Heroku Postgres support and runtime issues should be submitted via one of the Heroku Support channels.