# KeiruaProd

## Importing a partial table backup using a temporary PostgreSQL table

During an update we broke a row on a database recently, and some people had unnecessary notifications when they logged in the application. Turns out we needed to fetch the updated_at value of a specific postgres table from a previous backup to fix this problem.

Here is how we did it:

First, export the row in question from a previous database dump.

\$ psql
\c database_name

COPY (
SELECT
id,
updated_at
FROM dossiers
where
…
) TO '/tmp/dossiers-updates.csv' With CSV DELIMITER ',';


Then, you can upload this on the database server:

scp /tmp/dossiers-updates.csv database.server:/tmp/dossiers-updates.csv


and reimport the data:

CREATE TEMP TABLE tmp_updates (id int, updated_at timestamp); -- but see below
UPDATE dossiers
WHERE
AND …
;


…and you are done. The tmp_updates table will be removed at the end of the execution, and you’ll be able to go back to your normal life.

# More power

The previous solution can be enough, but should we have a more serious problem, it turns out that parsing the initial CSV file is not very difficult, and we can perform some operations in ruby on the data, for instance like this:

# convert-to-update.sql
require 'csv'

CSV.foreach(csv_update_file) do |row|
puts "update dossiers set updated_at = '#{row[1]}' where id = #{row[0]} AND revision_id IS NOT NULL AND updated_at <= '2020-07-22 09:20:00';\n";
end


Sure, that’s not the most awesome ruby script ever, but sometimes a 10 lines script is good enough.

Then, we can generate a large SQL file that we can upload

ruby convert-to-update.rb > /tmp/updates.sql


and we can import it as a file:

time psql -d database_name -h localhost  -p 5432 -f /tmp/updates.sql


See a typo ? You can suggest a modification on Github.