Postgres column is missing

Question

Having the weirdest error with a new postgres db made by a rails app.

I created a new table (images) with a reference field (game_id).

    create_table :images do |t|
      t.string :title
      t.references :game
      t.string :file
      t.integer :image_type, default: 0

      t.timestamps
    end

I inserted over 6000 images in my table, with working reference to the games table. After working for a few months, the table now lost that game_id column. It's completely gone. Did not run any migration, development database works fine, it still has that column. I checked logs since i started the app (it has all the migrations), there is no trace of a drop column call anywhere. Also nothing in the postgres history. Logs show proper inserts

INSERT INTO "images" ("game_id", "file", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"ESC[0m  [["game_id", 4], ["file", "r6hhvtaozowkcf06bswz.jpg"], ["created_at", "2019-03-05 14:40:33.612203"], ["updated_at", "2019-03-05 14:40:33.612203"]]

Now i only have the errors

column images.game_id does not exist

I'm upset that i lost all that data, does anyone know of a reason why the column got dropped? And would it be possible to get the data back or should i just start from scratch? I also didn't upgrade the server, didn't even touch the

Laurenz Albe · Accepted Answer · 2019-09-09 09:04:51Z

Columns don't just vanish by themselves, somebody or something must have dropped it.

The advice is probably too late, but if you set log_statement to 'ddl' in postgresql.conf, you'll get all ALTER TABLE statements logged, which will help you find who or what caused the problem.

If you are willing to mess with the catalog tables you can resurrect the column unless the table has already been rewritten by a VACUUM (FULL) or similar.

I recommend that you don't do that on your production database, but on a copy generated with pg_basebackup. Then you can pg_dump the repaired table and replace the mutilated table in your production database.

Let's assume your table is called tab and you dropped an integer column b.

First, let's verify that this really happened:

SELECT * FROM pg_attribute
WHERE attrelid = 'tab'::regclass
  AND attnum > 0
ORDER BY attnum;

 attrelid |           attname            | atttypid | attstattarget | attlen | attnum | attndims | attcacheoff | atttypmod | attbyval | attstorage | attalign | attnotnull | atthasdef | atthasmissing | attidentity | attisdropped | attislocal | attinhcount | attcollation | attacl | attoptions | attfdwoptions | attmissingval 
----------+------------------------------+----------+---------------+--------+--------+----------+-------------+-----------+----------+------------+----------+------------+-----------+---------------+-------------+--------------+------------+-------------+--------------+--------+------------+---------------+---------------
    17269 | a                            |       23 |            -1 |      4 |      1 |        0 |          -1 |        -1 | t        | p          | i        | f          | f         | f             |             | f            | t          |           0 |            0 |        |            |               | 
    17269 | ........pg.dropped.2........ |        0 |             0 |      4 |      2 |        0 |          -1 |        -1 | t        | p          | i        | f          | f         | f             |             | t            | t          |           0 |            0 |        |            |               | 
    17269 | c                            |       23 |            -1 |      4 |      3 |        0 |          -1 |        -1 | t        | p          | i        | f          | f         | f             |             | f            | t          |           0 |            0 |        |            |               | 
(3 rows)

Yes, column number 2 was dropped.

Let's restore it:

UPDATE pg_attribute
SET attname = 'b',
    atttypid = 'integer'::regtype,
    attstattarget = -1,
    attisdropped = FALSE
WHERE attrelid = 'tab'::regclass
AND attnum = 2;

Now you should see the column again. All rows added after the column was dropped with have a NULL value for the column.

You'll have to add column defaults and what else has been dropped along the way, but I guess you can get these things from your backup.

Thank you so much, this got my column back, perfect solution for my case. My postgres logs didn't go far enough unfortunately (cleaned them up before i realised there was an issue with the db)

Collectives™ on Stack Overflow

Postgres column is missing

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related