Questions tagged [heroku]

For questions related to databases on the Heroku cloud platform, which supports Ruby, Node.js, Python, Go, PHP, and JVM-based applications.

Filter by
Sorted by
Tagged with
1 vote
1 answer
96 views

Postgresql super slow on server - super fast locally?

I am hosting a Postgresql database on Heroku with a standard-0 plan. My database has a table called transactions which contains ~18 million rows: SELECT COUNT(*) FROM transactions; count ---------...
zarathustra's user avatar
0 votes
0 answers
33 views

How is Pricing calculated in Heroku PostGres Addon

Heroku has now upgraded its plans. So I have a doubt on the Heroku postgres Mini plan. I have a site which uses the heroku postgres database. The site is just for my practice and in my free time i ...
Joy's user avatar
  • 1
1 vote
1 answer
244 views

Reproduce Heroku's Postgres WAL metric with pg_ls_waldir()

Is it possible to reproduce Heroku’s WAL usage metric by using the postgres query SELECT sum(size) FROM pg_ls_waldir(); that returns the size of the WAL directory? I asked Heroku support but they ...
Todd's user avatar
  • 195
0 votes
1 answer
71 views

Problem seeding Heroku database

I'm trying to seed my Heroku production database and got the following error message: ERROR: insert or update on table "Posts" violate foreign key constraint "Posts_userId_fkey The ...
skyeNoLimit's user avatar
1 vote
2 answers
418 views

Approaching row limit for hobby-dev database on Heroku app

I just got an email from Heroku about my database nearing its row limit, but I'm confused because my database has almost nothing in it. heroku pg:info returns the following: === DATABASE_URL Plan: ...
calyxofheld's user avatar
1 vote
1 answer
58 views

Heroku PostgresSQL: How to replay statement logs to another server

We're using Heroku Postgres v12 for production web app and plan to upgrade to v13. I want to be able to replay statements from previous days log to warm cache on upgraded instance (Heroku will switch ...
scorpp's user avatar
  • 163
-1 votes
1 answer
43 views

Seeking advice on a DB project: hosting services [closed]

I am a new web developer who just finished a full stack boot camp. This question is to get advice and suggestions in building a free web-based database site for archaeology research. I would ...
deweyD's user avatar
  • 3
0 votes
1 answer
208 views

Detecting running pg_dump conflicting with schema changes

I use Heroku PostgreSQL and their PGBackups for my production environment. I have found, for reasons that I'm not quite clear on, that at least some schema changes do not run correctly during the time ...
Ryan H's user avatar
  • 101
2 votes
0 answers
28 views

Postgres on Heroku: Is it possible to prevent users from logging in to the primary host?

I have a Postgres 12.3 server running in a primary-follower configuration on Heroku. I added a few credentials to the primary instance, which were replicated to the follower. I would like to prevent ...
AmitA's user avatar
  • 121
0 votes
1 answer
369 views

MySQL id not saving in order (Laravel in production) [closed]

I'm really confused about this.. So, I created a Laravel app and hosted it on Heroku. I'm using ClearDB extension to be able to use MySQL. Problem is: when I save a new User on my DB, it is not being ...
GabrielLearner's user avatar
0 votes
1 answer
4k views

How to view contraints of a cloumn in postgresSql?

I applied a constraint but forgot its name. Now I want to drop it using ALTER TABLE [TABLENAME] DROP CONSTRAINT [CONSTRAINTNAME] . If there is a way to drop all constraint from a column that should ...
No Name's user avatar
0 votes
1 answer
144 views

heroku postgres sql changes not reflected in db [closed]

I wrote this program import psycopg2 try: #connect to db cursor = connection.cursor() except Exception as e: raise e else: print("connection Success") cmd = "" while(True): ...
No Name's user avatar
1 vote
1 answer
202 views

Vacuum increases bloat size as reported by Heroku's bloat SQL query

I am using using the Heroku command as described here to detect bloat in tables. One of our tables reported a bloat size of around 7GB, but running vacuum on it, then running the same bloat command, ...
m_w_k's user avatar
  • 33
1 vote
1 answer
2k views

pg_restore: [archiver] did not find magic string in file header: please check the source URL and ensure it is publicly accessible

I have been trying to push a dump file from my local Postgrel DB (which I uploaded into my Google Drive and is accessible to public) into my Heroku remote DB with the following URL: heroku pg:backups:...
coredumped0x's user avatar
0 votes
0 answers
932 views

Can I see the remote IP address for failed PostgreSQL logins?

We're running PostgreSQL on Heroku, with all the application logs going to Logentries, and I'm seeing around 4,000 failed login attempts per hour for a specific set of credentials: 2019-07-30T09:08:...
Dylan Beattie's user avatar
2 votes
0 answers
150 views

Getting unfamiliar error when using terminal command to update database

I'm a bit in over my head when it comes to using and managing large databases. I run a small startup that uses a PostGreSQL and pgAdmin to administer and query the database, which is housed on Herkou. ...
user avatar
1 vote
1 answer
1k views

Copying a DB or tables from a local Postgres database to Heroku [closed]

I'm almost a novice with databases. I created a PostgreSQL database with the python programming language. I am pleased to note that the database was created as I wished. I could easily verify it from ...
Memmo's user avatar
  • 113
1 vote
1 answer
147 views

Expensive Query on 6 GB table

I have tables Foo and Buzz like so: Foos buzz_id date Foo has an index on buzz_id, the foreign key. It also has a index on the date. Buzzes name group Buzz has an index on name, an index on group, ...
markain's user avatar
  • 113
2 votes
2 answers
4k views

Set search path for Postgres in a Rails app

I have a project in heroku. I have the URI that enables me to connect with the database in my external rails app. How do I change or set the search path in ruby code?
Dan Dunclace's user avatar
2 votes
1 answer
556 views

Custom unaccent rules in heroku

We have an application running in heroku and we want to add a search feature on a field that contains greek characters and we want to make it accent agnostic. So the idea is to use postgresql's ...
George Karanikas's user avatar
5 votes
2 answers
6k views

How to anonymize pg_dump output before it leaves server?

For development purposes, we dump the production database to local. It's fine because the DB is small enough. The company's growing and we want to reduce risks. To that end, we'd like to anonymize the ...
François Beausoleil's user avatar
3 votes
1 answer
1k views

Mongoose: Change 2dsphere index version

I'm trying to upload my database to the heroku mongodbLab addon. My MongoDB version is 3.2.4 and my servers is 3.0.9. Mongoose is 4.4.6. However, when I run the command $ mongorestore -h myhostname -d ...
user avatar
3 votes
0 answers
370 views

Best method for daily incremental copy of Postgres database

I'm trying to figure out the best plan of attack for making a nightly copy of our Heroku PostgreSQL production database to our local development environment. The database is rather large and grows ...
contact411's user avatar
1 vote
1 answer
143 views

Copy postgis layer from S3 to Heroku

I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add ...
Anne's user avatar
  • 133
2 votes
1 answer
2k views

PostgreSQL: restore a record from a backup

We have a Rails app backed by a PostgreSQL DB and hosted on Heroku. We recently had a bug that deleted some (very few before we caught it) records. Excuse my ignorance but is there a straightforward ...
Meltemi's user avatar
  • 777
3 votes
1 answer
2k views

Postgresql performance issues when issuing many small inserts and updates one at a time

I have a production web server with postgresql database. My server gets data from another server every hour on the hour. The other server wakes up and sends many requests to my server, each result in ...
davidrac's user avatar
  • 133
1 vote
0 answers
824 views

Can I export a PostgreSQL database from Heroku and Import as a PostGIS database in Digital Ocean?

Disclaimer: This may be a dumb question. This is honestly way over my head... one of the pitfalls of using Heroku for too long. I have a PostgreSQL database on Heroku. Now I'm migrating to Digital ...
BigRon's user avatar
  • 111
2 votes
0 answers
1k views

Pass a URI as a pgAdmin parameter

I can connect to Heroku with psql passing a URI: $ psql postgres://user:[email protected]:5432/d7xkxididkdi I want to do the same with pgAdmin: $ pgadmin3 -qc ...
Clodoaldo's user avatar
  • 1,135
5 votes
1 answer
27k views

How to convert time without time zone to timestamp without time zone in PostgreSQL?

I received this error when trying to alter a column of type time to type timestamp: PG::CannotCoerce: ERROR: cannot cast type time without time zone to timestamp without time zone It's not ...
at.'s user avatar
  • 167
1 vote
0 answers
648 views

On Heroku in production, how can I copy database A to database B on command?

From my local terminal, I can use the following commands to do what I need to do: heroku pgbackups:capture --expire heroku pgbackups:restore charcoal --confirm max-kiosk However, I want to do this ...
user2168130's user avatar
0 votes
1 answer
2k views

How can I pg_dump my database while excluding extensions?

I am trying to migrate my PostgreSQL 9.3 database to heroku. My database uses the UUID-ossp contrib extension I am not able to load my db onto Heroku because ERROR: permission denied for language ...
David Chan's user avatar
15 votes
4 answers
8k views

Migrate heroku database to Amazon RDS with minimum downtime

I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
user avatar
3 votes
1 answer
4k views

Finding by ID in postgresql very slow on big table

I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is ...
Milovan Zogovic's user avatar
1 vote
1 answer
2k views

Is it possible for heroku postgres follower database to follow non-heroku postgres master?

I am considering to move our site's database from our dedicated db server to heroku postgres. as we have a few gigs of data in the db and don't want to take it down for a long migration downtime it ...
user avatar
1 vote
1 answer
322 views

Write performance of Postgresql 9.1 with read-only slave

I have a Postgresql 9.1 database that is being hosted by Heroku. It currently has a read-only "follower". I need to truncate several large tables (over 100GB) of data and reloads them. Is the read-...
David S's user avatar
  • 282
2 votes
1 answer
464 views

What is the equivalent of a "Certificate of Destruction" when using a cloud-hosted database?

We have a Rails application hosted by Heroku and using an Heroku Postgres database. Some of the information we store is considered to be sensitive by our users, and we've been asked if we can provide ...
hiattp's user avatar
  • 123
2 votes
0 answers
3k views

"custom archiver out of memory" error when restoring large DB using pg_restore

I'm trying to a restore a local copy of a 30GB DB (with only one table) dumped using the Heroku wrappers (described here) using pg_restore. The Heroku DB is using 9.1.7 and I have had the same result ...
jordanpg's user avatar
  • 355
13 votes
5 answers
4k views

How can I export a subset of tabledata from a prodution database into my local testing database?

We have a relatively big production postgres based db: ~20GB. The PostgreSQL database is hosted on heroku. I would like to copy a small subset of the table data to my local database so I can run ...
jottr's user avatar
  • 243
0 votes
1 answer
545 views

Heroku PostgresApp - Fails to start with edited postgresql.conf

I am trying to tune my local pg running from the Heroku Postgress App. I am seeing strange errors in the OSX console whenever I change some of the internal settings: PM Postgres[78709]: Could not ...
Toby Hede's user avatar
  • 101
3 votes
1 answer
5k views

Ensure correct username when using pg_restore

I have just installed postgres 9.1.6 on a local Ubuntu server. Now I'm trying to restore a database dump from a database on Heroku. The local database is setup like this: sudo -u postgres psql -c "...
Niels Kristian's user avatar