PgSQL - How to import database dump only when database completely empty?

Question

The use-case actually to automate this with ansible. I want to import database dump only when database is completely empty (no tables inside). Of course there's always a way to execute sql statement, but this is last resort, I believe there should be more elegant solution for this.

pg_restore manual doesn't provide this option as far as I see.

Here's how I'm planning to do this with ansible:

  - name: db_restore | Receive latest DB backup
    shell: s3cmd --skip-existing get `s3cmd ls s3://{{ aws_bucket }}/ | grep sentry | tail -1 | awk '{print $4}'` sql.latest.tgz
    args:
        chdir: /root/
        creates: sql.latest.tgz

  - name: db_restore | Check if file exists
    stat: path=/root/sql.latest.tgz
    register: sql_latest

  - name: db_restore | Restore latest DB backup if backup file found
    shell: PGPASSWORD={{ dbpassword }} tar -xzOf /root/sentry*.tgz db.sql | psql -U{{ dbuser }} -h{{ pgsql_server }} --set ON_ERROR_STOP=on {{ dbname }}
    when: sql_latest.stat.exists
    ignore_errors: True

Ideally this should check if DB empty. No ansible module exist for this purpose. Google is also in silence.. Current solution actually also works, this will give error when import will fail, and I can just ignore error, but it's a bit painful to see a false alarm.

Answer 1

There's not really any such thing as "empty" as such; it generally has the built-in types, the default PL/PgSQL language, etc, even if you create from template0. If you create from a different template there could be a whole lot more in there.

PostgreSQL doesn't keep a record of the first non-template write to a DB, so you can't say "changed since created" either.

That's why there's no --if-empty option to pg_restore. It doesn't really make sense.

By far and away the best option is to execute psql to query the information_schema and determine if there are any tables in the public schema. Or, even better, query for the presence of specific tables and types you know will be created by the dump.

e.g.

psql -qAt mydbname -c "select 1 from information_schema.tables where table_schema = 'public' and table_name = 'testtable';"

You can then test for zero/nonzero rows returned on stdout. Or wrap it in SELECT EXISTS(...) to get a boolean from psql. Or use a DO block that ERRORs if the table exists if you need a zero/nonzero exit status from psql.

Answer 2

To regard the database as empty, we must know there nothing has been added from the point of creation. As postgres does not track this (as already mentioned by @Craig Ringer) I recommend a different approach with regards to ansible.

So, just use a handler mechanism like:

- name: Create zabbbix postgres DB
  postgresql_db: name="{{zabbix_db_name}}"
  notify:
    - Init zabbix database