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

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.

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

Since it is hard to tell, if a database is "empty", as explained by others, it is much easier to check, if the database exists, then create and restore in one step. I'm doing it like this:

- name: Check my_database database already exists  become: yes  become_user: postgres  shell: psql -l | grep my_database  ignore_errors: true  register: my_database_db_existence- debug: var=my_database_db_existence- name: Copy backup of the my-database database  shell: your-s3-command here  when: my_database_db_existence | failed- name: Restore my_database database on first run  become_user: postgres  shell: createdb -O my_user my_database && psql -d my_database -f /path/to/my_dump.sql  when: my_database_db_existence | failed

P.S. Also written a detailed blog post explaining each ansible task in the implementation.