0

After changes to some Terraform code, I can no longer access the data I've added into an Aurora (PostgreSQL) database. The data gets added into the database as expected without errors in the logs but I can't find the data after connecting to the database with AWS RDS Query Editor.

I have added thousands of rows with Python code that uses the SQLAlchemy/PostgreSQL engine object to insert a batch of rows from a mappings dictionary, like so:

if (count % batch_size) == 0:
    self.engine.execute(Building.__table__.insert(), mappings)
    self.session.commit()

The logs from this data ingest show no errors, the commits all appear to have completed successfully. So the data was inserted someplace, I just can't work out where that is, as it's not showing up in the AWS Console RDS Query Editor. I run the SQL below to find the table, with zero rows returned:

SELECT * FROM information_schema.tables WHERE table_name = 'buildings'

This has worked as expected before (i.e. I could see the data in the Aurora database via the Query Editor) so I'm trying to work out which of the recently modified Terraform settings have caused the issue.

Where else can I look to find where the data was inserted, assuming that it was actually inserted somewhere? If I can work that out it may help reveal the culprit.

James Adams
  • 8,448
  • 21
  • 89
  • 148

2 Answers2

1

I suspect misleading capitalization. Like "Buildings". Search again with:

SELECT * FROM information_schema.tables WHERE table_name ~* 'building';

Or:

SELECT * FROM pg_catalog.pg_tables WHERE tablename ~* 'building';

Or maybe your target wasn't a table? You can "write" to simple views. Check with:

SELECT * FROM pg_catalog.pg_class WHERE  relname ~* 'building';

None of this is specific to RDS. It's the same in plain Postgres.

If the last query returns nothing, you are in the wrong database. (You are aware that there can be multiple databases in one DB cluster?) Or you have a serious problem.

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for this helpful suggestion. Neither of these returned any rows. Maybe there's some other SQL that selects for non-system/user-created tables regardless of the table name? So that it filters based on some sort of table type? – James Adams May 28 '20 at 00:05
  • 1
    @JamesAdams; If the table exists (including temporary tables of your own session), you would see it. I added another query to check for all table-like objects. – Erwin Brandstetter May 28 '20 at 00:12
  • Thank you @Erwin Brandstetter. Still no rows using the additional select. I will next add logging to report the host, port, db-name, etc. in the code that performs the inserts, maybe something will reveal itself there... – James Adams May 28 '20 at 00:23
  • Once I logged more information regarding the connection I discovered that the database name being used was garbled and so I have been querying the Aurora instance using the wrong database name. – James Adams May 28 '20 at 02:56
0

Once I logged more information regarding the connection I discovered that the database name being used was incorrect, so I have been querying the Aurora instance using the wrong database name. Once I worked this out and used the correct database name the select statements in AWS RDS Query Editor worked as expected.

James Adams
  • 8,448
  • 21
  • 89
  • 148