Greg Sabino Mullane: 5 Ways to Get Table Creation Information in Postgres

A question I hear from time to time with Crunchy Data clients and the Postgres community is: When was my Postgres database table created? Postgres does not store the creation date of tables, or any other database object. But fear not, there are a plethora of direct and indirect ways to find out when your table creation happened. Let's go through some ways to do this, ranging from easy to somewhat hard. All these solutions apply to indexes and other database objects, but tables are by far the most common request. 1. Logging The easiest solution is to look in your Postgres logs and see exactly when the CREATE TABLE command ran. Unfortunately, this does require that the log_statement parameter be set before you create the table! Changing log_statement to ddl is a great idea already, and has many benefits other than seeing object creation times. Once you have it set, Postgres will log nearly everything you do to a table that is not a SELECT, INSERT, UPDATE, or DELETE. So yes, that covers CREATE TABLE as well as ALTER TABLE and DROP TABLE. Of course, this also requires that your log_line_prefix includes a timestamp, but that is already enabled by default. Let's see what it looks like in action: ## Change our logging, then ask Postgres to reload the configuration files: $ psql -c 'alter system set log_statement = ddl' -c 'select pg_reload_conf()' ## Create some test tables with the pgbench program: $ pgbench -i ## From the current logfile, grab all the lines, split them into rows, ## use a regular expression to find all the CREATE TABLE statements, ## and pull back the latest four $ psql -Atc 'select * from regexp_split_to_table( pg_read_file( pg_current_logfile() ), chr(10) ) as x where x ~* $$create table$$' | tail -4 | cut -c1-90 2017-03-03 16:17:10 EDT [13] LOG: statement: create table pgbench_history(tid int 2017-03-03 16:17:10 EDT [13] LOG: statement: create table pgbench_tellers(tid int 2017-03-03 16:17:10 EDT [13] LOG: statement: create table pgbench_accounts(aid int 2017-03-0[...]