1

In pg 9.3 database for rails 3.2 app, table engine_configs keeps losing primary key (id) and its indexes. Here is the table:

table engine_configs

For the table shown above, we just added the primary key again and it is showing integer in column ID instead of serial. Last week, we had manually re-created key and indexes on the table and verified that those key and indexes did exist. There were only a few pg_dump and pg_restore on the table before we found that primary key and indexes are missing again. There was no db crash or something similar. Is there any explanation of why this happens and how to prevent it from happening again? This problem literally blows up our rails app.

UPDATE:

Here is the rails db create:

class CreateAuthentifyEngineConfigs < ActiveRecord::Migration
  def change
    create_table :authentify_engine_configs do |t|
      #t.integer     :client_id
      t.string      :engine_name
      t.string      :engine_version
      t.string      :argument_name
      t.text        :argument_value
      t.integer     :last_updated_by_id
      t.timestamps
      t.string      :brief_note
      t.boolean :global, :default => false

    end

    add_index :authentify_engine_configs, :engine_name
    add_index :authentify_engine_configs, :argument_name
    add_index :authentify_engine_configs, [:engine_name, :argument_name], :name => :authentify_engine_configs_names
  end
end

This migration file has been used many times with sqlite3 and never had any problem.

UPDATE1: After pg_dump -Fc --table=authentify_engine_configs mydb > mydb_ec.backup, then restore with:

pg_restore --clean --dbname=mydevdb --table=authentify_engine_configs --verbose c:\d\code\rails_proj\cis\db\mydb_ec.backup

The indexes were lost with the restored local copy. However when pg_dump the whole db, then restored copy is fine.

user938363
  • 9,990
  • 38
  • 137
  • 303
  • 2
    In more than five years of postgres usage, I have never experienced such behaviour. Must be your rails derailing. – wildplasser Jun 22 '14 at 21:04
  • In any case: you could check the postgres logfile (if you still have it, and 'ddl' is logged) – wildplasser Jun 22 '14 at 21:12
  • This is indeed odd. This is first migration to postgres. Before it was used with sqlite3 and never had problem. – user938363 Jun 22 '14 at 21:58
  • Hi. Is it possible that users and doing database updates that are corrupting the database as opposed to the pg_dump and pg_restore ? and apologies if questio overly stupid. Pierre – user1854802 Jun 23 '14 at 01:42
  • The app is still be developed and tested by developers. The config table needs to be constantly updated and that's why there are quite a few pg_restore and pg_dump. When pg_restore, --clean is used. Not sure if this --clean may introduce risk of schema damages. pg_restore seems not having ability to do data update only (empty the table and replacing the data). The --data-only for pg_restore is only adding the record to the end of the table. Thanks for the questions. – user938363 Jun 23 '14 at 04:30
  • Enable `log_statement = 'ddl'` and see what application is dropping them... – Craig Ringer Jun 23 '14 at 10:34
  • _it is showing integer in column ID instead of serial_: that's normal, **serial is not a datatype**, it's a shortcut for `int not null default nextval('generated_sequence_name')` – Daniel Vérité Jun 23 '14 at 10:42
  • Right that serial is not a data type. We have to re-define and set the sequence after losing the primary key. – user938363 Jun 23 '14 at 19:49
  • I have run into a similar issue, that only happened in postgres 9.3.5 and not in 9.3.4. Do you found any solutions for your problem yet? – Martin Frank Nov 14 '14 at 09:47

1 Answers1

1

I had the same problem recently and used pg:reset to solve it. I found the solution from Getting "Unknown primary key for table" while the ID is there . I was using heroku pgbackups:restore and it was dropping the primary keys until I dropped the database using pg:reset.

Community
  • 1
  • 1
Derek
  • 1,735
  • 17
  • 14
  • I'm not on Heroku, but dropping the database (`dropdb xxx`) and reimporting the backup worked – Vala Jun 21 '16 at 09:55