Read and edit database dump file in Ruby

Question

I want to import database dump to new database that does not have all columns that old database had.

So, to ignore/remove those columns, my idea is to edit dump.sql.

How can I read and edit sql file in ruby?

Dump created:

pg_dump --data-only old_database > dump_data_12_3_2019.sql;

Using:

PostgreSQL 9.5
Ruby 2.5.1

Inside sql file:

COPY public.images (id, file, created_at, updated_at, rotation) FROM stdin;
255 31265d7843.JPG  2018-11-15 09:05:43.37898   2018-11-15 09:05:43.37898   0
256 78e834e4e7.JPG  2018-11-15 09:05:43.571389  2018-11-15 09:05:43.571389  0
257 7b6662659b.JPG  2018-11-15 09:05:44.275989  2018-11-15 09:05:44.275989  0
215 6fe307f4b5.jpg  2018-10-15 11:08:59.42583   2018-10-15 11:12:25.284024  0

Header is starting with COPY, column names are inside ( ) and data is below with space for separator.

How can I remove column rotation and his data?

You can dump just the tables you need. See https://stackoverflow.com/questions/7359827/creating-a-database-dump-for-specific-tables-and-entries-postgres — lacostenycoder, Mar 12 '19 at 14:23
I dont want to dump specific tables. I want to ignore or remove columns inside some tables. — kuhinja, Mar 12 '19 at 14:55
Probably simplest to add those "old" columns to your new database so you can most easily import the dump; then remove those columns after. — Cody Caughlan, Mar 12 '19 at 15:48

score 1 · Accepted Answer · answered Mar 12 '19 at 15:55

You have to 'update' the original sql file, by removing the columns (not available in new DB) and data of those columns.

Just read line by line, modify it according your logic and write to other file. Consider that some lines are the columns info and other are the data.


path = '/tmp/source'
temp_file = Tempfile.new('output')
begin
  File.open(path, 'r') do |file|
    file.each_line do |line|
      # change the line according your logic
      temp_file.puts  new_line
    end
  end
  temp_file.close
ensure
  temp_file.close
  temp_file.unlink
end

After that, the target file can be restored with pg commands.

psql -U <username> -d <dbname> -1 -f <filename>.sql

or

pg_restore -U <username> -d <dbname> -1 <filename>.dump

This is what I was looking for. I will try this out and type my own script for dump. Thanks — kuhinja, Mar 12 '19 at 19:05

score -1 · Answer 2 · answered Mar 12 '19 at 15:07

You need to open the file (1), separate commands (2), then do some operation to edit the SQL code (3) and finally run it (4).

contents = File.read('path/to/file.sql') # (1) If the file is huge, do not read it whole, but line by line
commands = contents.split(';') # (2) probably you'll need something more clever (hint: regex) because of the ; character in the data
commands.each do |command|
  stripped_command = strip_some_columns(command) # (3)
  ActiveRecord::Base.connection.execute(stripped_command) # (4)
end

I understand what you want to say, but sql file does not work like this. I updated my question. Thanks — kuhinja, Mar 12 '19 at 15:39

Read and edit database dump file in Ruby

2 Answers2