Database Administrators Stack Exchange
Database Administrators Stack Exchange

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
15
votes
4 answers

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 promoting the replicated DB as the main DB. I know I…
Byron Singh
13
votes
5 answers

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 some tests on them without having to work on…
jottr
  • 243
  • 2
  • 6
5
votes
1 answer

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 important to me how the existing times are converted, but…
at.
  • 167
  • 1
  • 1
  • 3
5
votes
3 answers

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 data before it leaves the database server. One…
François Beausoleil
  • 1,463
  • 3
  • 15
  • 24
3
votes
1 answer

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 around 2 seconds. Application is hosted on heroku…
Milovan Zogovic
  • 1,463
  • 3
  • 15
  • 20
3
votes
1 answer

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 "create user app_user with password 'pass';" sudo -u…
Niels Kristian
  • 971
  • 3
  • 15
  • 23
3
votes
1 answer

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 mydatabase -u username -p password…
Tad Scritchfield
3
votes
0 answers

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 at roughly 250,000 rows per day. Ideally the…
contact411
  • 31
  • 3
3
votes
1 answer

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 an insert/update to the postgresql database. In…
davidrac
  • 133
  • 4
2
votes
0 answers

Pass a URI as a pgAdmin parameter

I can connect to Heroku with psql passing a URI: $ psql postgres://user:password@ec2-54-243-xxx-xxx.compute-1.amazonaws.com:5432/d7xkxididkdi I want to do the same with pgAdmin: $ pgadmin3 -qc…
Clodoaldo
  • 1,145
  • 2
  • 8
  • 22
2
votes
1 answer

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 a Certificate of Destruction in the event they…
hiattp
  • 123
  • 3
2
votes
0 answers

"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 using 9.1.5 and 9.1.7 under OS X 10.8. Using the…
jordanpg
  • 355
  • 5
  • 14
2
votes
0 answers

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 users from logging into the primary host. I…
AmitA
  • 121
  • 1
2
votes
1 answer

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 work too. I cannot use a psql command.
No Name
  • 25
  • 1
  • 6
2
votes
0 answers

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. Typically, I need to 'pull' and updated version…
ironjatt
1
2 3