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?

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. — Adrian Klaver
– Adrian Klaver, Commented Oct 18, 2021 at 21:57

Adrian Klaver · Accepted Answer · 2021-10-19 22:44:20Z

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)

edited Oct 19, 2021 at 22:44

answered Oct 18, 2021 at 22:46

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurenz Albe Over a year ago

You should add your comment above to the answer. As it is, it lacks explanations.

Dekel tsairi Over a year ago

Indeed its works. Thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related