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.
40 questions
1
vote
1
answer
277
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
---------...
- 113
0
votes
0
answers
51
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 ...
- 1
1
vote
1
answer
279
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 ...
- 205
0
votes
1
answer
93
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 ...
1
vote
2
answers
427
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: ...
- 111
1
vote
1
answer
63
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 ...
- 173
-1
votes
1
answer
44
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 ...
- 3
0
votes
1
answer
250
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 ...
- 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 ...
- 121
0
votes
1
answer
400
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 ...
1
vote
1
answer
5k
views
View PostgreSQL column constraints
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 ...
- 15
0
votes
1
answer
148
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):
...
- 15
1
vote
1
answer
222
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, ...
- 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:...
- 121
0
votes
0
answers
1k
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:...
- 119
2
votes
0
answers
151
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.
...
ironjatt
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 ...
- 113
1
vote
1
answer
149
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, ...
- 113
2
votes
2
answers
5k
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?
- 23
2
votes
1
answer
576
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 ...
- 123
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 ...
- 1,453
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 ...
Tad Scritchfield
3
votes
0
answers
395
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 ...
- 31
1
vote
1
answer
144
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 ...
- 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 ...
- 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 ...
- 133
1
vote
0
answers
826
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 ...
- 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 ...
- 1,135
5
votes
1
answer
28k
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 ...
- 167
1
vote
0
answers
651
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 ...
- 111
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 ...
- 101
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 ...
Byron Singh
3
votes
1
answer
5k
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 ...
- 1,443
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 ...
shvemi
1
vote
1
answer
329
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-...
- 284
2
votes
1
answer
532
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 ...
- 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 ...
- 355
13
votes
5
answers
5k
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 ...
- 243
0
votes
1
answer
547
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 ...
- 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 "...
- 961