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:1234@127.0.0.1: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?
Should work by using postgres://postgres:1234@127.0.0.1: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)