problem with create database if not exist via postgres backup and restore tool

Question

I wrote a bat script for executing the Postgres backup and restore tool.

I have a little issue with the restore flow: Its works fine as long as my database exists. But if it doesn't it will fail. My restore command:

"pg_restore.exe" -d postgres://postgres:[email protected]:9195/mydb -w -c -v -F c --if-exists "DatabaseBackup_mydb.tar" 2>> "DatabaseRestore_mydb.log"

So I need to modify that command somehow that will handle also a use case in which the database "mydb" doesn't exist, and create it in such a case.

just adding the -C flag won't work in that case.

Any suggestion?

Answer 1

Should work by using postgres://postgres:[email protected]:9195/postgres and adding -C. Obviously test on throw away instance. This will connect to postgres database DROP DATABASE IF EXISTS mydb; , then CREATE DATABASE mydb, connect to mydb and then restore the database objects.

To demonstrate:

\l test_db
                       List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges 
------+-------+----------+---------+-------+-------------------
(0 rows)

pg_restore -d postgres -c -C -U postgres test_db.out 
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4734; 1262 1170111 DATABASE test_db postgres
pg_restore: error: could not execute query: ERROR:  database "test_db" does not exist
Command was: DROP DATABASE test_db;
pg_restore: warning: errors ignored on restore: 1

 \l test_db
                               List of databases
  Name   |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
---------+----------+----------+-------------+-------------+-------------------
 test_db | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)

pg_restore -d postgres -c -C -U postgres test_db.out

\l test_db
                               List of databases
  Name   |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges 
---------+----------+----------+-------------+-------------+-------------------
 test_db | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(1 row)