Hot answers tagged

7

Since time does not have a date component you need to provide a date. You can just add date and time values in the ALTER TABLE statement: ALTER TABLE students ALTER COLUMN time_since_missing_schedule_notification TYPE timestamp USING ('2000-1-1'::date + time_since_missing_schedule_notification)


3

I contacted Heroku after trying to find a solution for the exact same issue. Here is the response: Unfortunately, there is no way to upload that unaccent.rules file to the Postgres server that hosts your database. You can enable the unaccent extension, but not supply a custom rules files. If this is a requirement for your app to run, you will ...


3

On way would be to restore only the relevant table to a new empty dummy database. Then find rows with standard SQL tools. Not sure if you are restricted by Heroku in any way. On the shell, as postgres user (or any user with sufficient privileges) first create an empty staging DB: createdb -T template0 dummy If the DB isn't too big and you have plenty of ...


3

In order to avoid overloading my server, the requests are queued and handled one at a time. That's the problem right there. You are not avoiding but causing overloading this way. Single row INSERT / UPDATE is dramatically more expensive than doing the same en bloc. Each statement has to be planned and executed separately. Depending on missing details ...


3

Pretty sure you are going to have to perform a good old-fashioned dump-and-restore here. Amazon doesn't seem to offer any other import options than loading in a dump: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html and even if they did, AFAICT the only export options offered by Heroku are pg_dumps (i.e. not ...


2

Honestly I have never seen a single value btree lookup take this long. I don't think your problem is simply in your query. I think it is elsewhere. First you say you have a lot of writes. This probably means you are constantly pushing a lot of stuff out of the buffers and doing a lot of random disk I/O. I would not be surprised if a lot of the ...


2

You're never committing your transaction. Add connection.commit() before you call cursor.close(). Docs here.


2

There are four tough problems to solve when trying to anonymize data. I'm going to summarize an old blog post of mine about it: 1: Anonymized data may grow in size. If you have peoples' names, for example, you don't really want all of the anonymous ones to have exactly the same length as the original data. If you change the data as it goes out, that means ...


1

Unless somebody has overridden the application_name parameter for pg_dump, you could do this: SELECT count(*) FROM pg_stat_activity WHERE application_name = 'pg_dump'; If that gives you 0, no pg_dump is running.


1

I think the auto_increment on your tables is set to 10 instead of 1.


1

This blog post by Michael Krenz outlines one way of handling data for developers. Basically, the strategy is to periodically (perhaps through a cron job) dump and restore to another database instance and running a function containing a series of SQL statements to scramble data, remove, or delete sensitive data. Then developers would dump directly from the ...


1

So I have tried to use Dropbox and other services, but I had the same error until the tenth attempt. Here is what I did: I logged in to my with my Heroku cli from my terminal on the master branch so I don't have to redefine the app. (specify the path to your damp. I have mine on the same directory) e.g: [murph@ComputerX languageandcode]$ the I run [murph@...


1

First, take a dump of the db under Windows as outlined here. This will be a human readable text file - worth a look to get a feel for what's going on "under the hood", i.e. in the background! Then, ftp that file (as text) over to the Heroku box (Linux I presume) and do a restore from the dump as outlined here. psql dbname < infile You may have to ...


1

You can define it in your database.yml using the schema_search_path key with a string value production: adapter: postgresql ... schema_search_path: 'schema1,public' You can find out a bit more here: https://til.hashrocket.com/posts/5aa2892b43-set-schema-search-path


1

It looks like you've installed the ossp-uuid extension from the sql scripts directly, rather than with create extension. You should CREATE EXTENSION "uuid-ossp" FROM unpackaged; on your local DB, before creating the dump. That'll convert it into an extension, which is dumped as CREATE EXTENSION ... instead of individual CREATE FUNCTION ... commands.


1

I recently did this on a multi terabyte Heroku Postgres database moving it to AWS Aurora Postgres with basically zero downtime (only requiring dyno config variable changes and restart). The process was to generate a log shipped replica on an EC2 instance, promote it and switch the app to it, then use the EC2 Postgres instance as a source for logical ...


1

It looks like AWS DMS (Database Migration Service) supports migrating data from external SQL instances. I was able to set up a migration task that replicates data and changes from Heroku to RDS. Whether it actually works for me is yet to be seen ;) --- EDIT --- Looks like services like DMS don't work with Heroku, because they require more privileged roles ...


1

You can connect your current app to the RDS database and have it insert in both Heroku and RDS, I don't mean to set up replication, just to insert in both servers. Once this is set up you will have new records stored in both databases. It should be easy enough to see which records are missing (for example: in table1 all records with id 100 or lower are ...


1

Truncate should be a logical operation that should not be too bad disk I/O-wise. I would not expect a lot of problems there.


1

Another option is something that I've just recently come across (granted, I'm yet to use it, but plan to soon): rdbms-subsetter. It is a little bit simpler and lighter than Jailer, with a few nice features/perks: CLI so easy to wire up to existing tooling Open source Will follow foreign keys to fetch a coherent subset of data If you don't have well-...


1

Jailer can helps in this situation. I am working on the same one as yours (trying to get ~100 records of each table) and here are steps that I did: - Find the root entity (the one that associated with almost tables) and get subset of the records of a root row (e.g root is people, then I will look up for all the related records to people.id=1...) - After ...


Only top voted, non community-wiki answers of a minimum length are eligible