Creating a PostgreSQL restore batch file for Windows and cannot get the parameters correct

Question

I am in the process of creating a backup script for my server running PostgreSQL on Windows 2022.

I am rather new to Postgres and I've searched this until I'm blue in the face but my switches seem to contradict each other when I'm running the script.

Here's my script so far:

@echo off
cls

set PGHOST=localhost
set PGUSER=someuser
set PGPASSWORD=somepassword
set PGPORT=5432
set PGDB=gitlab

echo This script will restore a PostgreSQL database dump file.
set /p "dmpfile=Enter dump file name to restore:      "
set /p "pgdb=Enter the new database name (gitlab): "
if "%pgdb%" equ "" set "pgdb=gitlab"

echo.
echo Importing %dmpfile% into database %pgdb%
echo.

echo off

echo Creating database %pgdb%
echo.
createdb -h %pghost% -p %pgport% -U %pguser% -T template0 %pgdb%

echo.
echo Importing %dmpfile%
echo.
pg_restore --create --dbname %pgdb% %dmpfile%

If I do not have the createdb line, I'm getting this message:

pg_restore: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: database "gitlab" does not exist

If I do create the database with createdb, then when the restore runs, I'm getting this message:

pg_restore: error: could not execute query: ERROR: database "gitlab" already exists Command was: CREATE DATABASE gitlab WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'English_United States.1252';

I don't get it. If I create it, it says it already exists, if I don't it complains that it doesn't exist.

What am I doing wrong?

Additional hints: Why is no string output with 'echo %var%' after using 'set var = text' on command line? This answer explains why it is advisable to use set "variable=value" to define an environment variable. How to stop Windows command interpreter from quitting batch file execution on an incorrect user input? This answer explains how to prevent an unexpected exit of the batch file processing on user makes a mistake on entering a string on a prompt. — Mofi
– Mofi, Commented Aug 23, 2023 at 18:18
Symbol equivalent to NEQ, LSS, GTR, etc. in Windows batch files This answer explains why the string comparison operator == should be used for a string comparison and not the EQU integer comparison operator. DosTips forum topic: ECHO. FAILS to give text or blank line - Instead use ECHO/ This forum topic describes why echo/ or echo( should be used instead of echo. which results in accessing the file system to find a file with name echo. — Mofi
– Mofi, Commented Aug 23, 2023 at 18:20
The issue chapters in this answer should be read as well. %dmpfile% in the command lines should be enclosed in ", except the value assigned to the environment variable dmpfile is already enclosed in " which in this case depends on the user input on prompt. — Mofi
– Mofi, Commented Aug 23, 2023 at 18:24

jjanes · Accepted Answer · 2023-08-23 19:44:33Z

1

When you use --create for pg_restore, the db specified on the command line is the "utility" database which gets connected to in order to execute the CREATE DATABASE command. This would generally be 'postgres' or 'template1'. In this case the name of the database which is to be created and restored into is coming from within the dump file, it is not specified on the command line.

So either precreate the database, but don't use --create, and do specify the name of that precreated database to restore into. Or don't precreate the database and do use --create, but then specify the name of a different already-existing utility database.

If you want the name of restored database do differ from the name it was originally dumped with, then you must use the first option (or just go rename it afterwards)

answered Aug 23, 2023 at 19:44

jjanes

45k5 gold badges39 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Creating a PostgreSQL restore batch file for Windows and cannot get the parameters correct

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related