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?
CodePudding user response:
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)