How to check creation date of postgres schema?

Question

For debugging purposes I would like to know when are some of my postgres schemas created at- is it possible?

Searched from PostgreSQL and Apartment docs but didn't find any helpful clues.

Environment and tools I'm using:

+---------------+----------+
| Tools         | Version  |
+--------------------------+
| PostgreSQL    | 9.4.1    |
| Ruby          | 2.2.1p85 |
| Ruby on Rails | 4.1.9    |
| Apartment gem | 0.26.1   |
+---------------+----------+

As you can see I use Apartment for creating schemas in my multi-tenant rails application.

score 1 · Accepted Answer · answered Apr 15 '15 at 09:54

1

PostgreSQL system catalogs don't store the dates on which database objects were created. You can configure PostgreSQL to log more or less every SQL statement (log_statement setting), but you'd have had to do that beforehand.

answered Apr 15 '15 at 09:54

Mike Sherrill 'Cat Recall'

91,602
17
122
185

score 1 · Answer 2 · answered Apr 15 '15 at 10:27

1

You could create an event trigger and store the date of this event in a table. And example can be found here

answered Apr 15 '15 at 10:27

Frank Heikens

117,544
24
142
135

score 0 · Answer 3 · answered Apr 16 '15 at 19:24

I'll post a "Rails way" solution that will not work for previously made schemas but new-ones.

Create model for tenants rails g model Tenant name:string

Add after_create callback for Tenant model

class Tenant < ActiveRecord::Base
  after_create :tenant_moves_in

  private

  def tenant_moves_in
    Apartment::Tenant.create self.name
  end

For ex. create somekind of script for making tenants or use ActiveRecord CRUD ability with Tenant model to operate with tenants in your app directly like Tenant.create(name: 'new_tenant').

Thatway you can have created_at and updated_at dates in your db tenants table in relation to Apartment Tenants, which represent postgres schemas.

Of course then you should avoid making tenants with Aparmtent tool like Apartment::Tenant.create 'new_tenant' because that will not create a Tenant-model object and therefore no created_at time will be preserved.

jvenezia · Answer 4 · 2015-04-15T10:04:41.753

-1

Disclamer : This answers a wrong interpretation of the question. I keep it in case it is useful for someone...

When you run you Ruby on Rails migrations with rake db:migrate, rails maintain a file witch shows you a snapshot of the database structure. This file is in db/schema.rb.

Here is an example of a table with a created_at added by rails :

  create_table "comments", force: true do |t|
    t.integer  "user_id"
    t.integer  "article_id"
    t.text     "content"
    t.datetime "created_at",                 null: false
    t.datetime "updated_at",                 null: false
    t.string   "origin",     default: "web"
  end

More details here: http://edgeguides.rubyonrails.org/active_record_migrations.html

edited Apr 15 '15 at 10:04

answered Apr 15 '15 at 09:32

jvenezia

890
7
10

1

If I understand you properly then that's not quite the case. `db/schema.rb` holds, yes, the structure of database schema, but it does not contain information about *multiple different* schemas I've made. As much as I know, it only shows the date of last made new migration. `created_at` field in table is for holding information about when *an object of that table* is created not the table itself. Also migrations run automatically after creating new schema (which I do like this: `Apartment::Tenant.create 'new_schema_name'`) – Andres Apr 15 '15 at 09:49
You are right, I have misunderstood your problem. Sorry I have no solution for that. – jvenezia Apr 15 '15 at 09:55

How to check creation date of postgres schema?

4 Answers4